Add a calculated field that is based on more than one row within the same query

[matidiaz]

This is my first post so please forgive me if I'm not very clear.


I've been working with Access, VBA and SQL for a couple of years now. But there are always more challenges to learn and get through.

I'm making a system for calculating game standings. It's played by a group of organizations which all sell the same item. There are players within those organizations that compete to sell the most of a certain product. This database stores all these players and organizations names, their standing by sales amount, etc.

I attached a copy of my db. It's not real data, but it serves to communicate.

Basically I have a query which pulled the following data

Org	Player Name	Gross Sales	Week Ending
D.C.	Kevin Pruett	$5,900.00	20-Mar-14
Portland	Jeni Western	$5,900.00	20-Mar-14
New Jersey	Duncan Adams	$5,500.59	20-Mar-14
New Jersey	Alex Swartz	$5,356.00	20-Mar-14
Montreal Sector	Toni Chrambanis	$5,355.79	20-Mar-14
Dallas	Katherine Quick	$5,300.00	20-Mar-14
Buffalo	Vanessa Corlenne	$5,300.00	20-Mar-14
D.C.	Josh Shapiro	$5,130.69	20-Mar-14
Denver	Brian Nadeau	$5,000.00	20-Mar-14
Portland	Jessica Tubbs	$4,814.00	20-Mar-14
Montreal Sector	Lynn Milanovich	$4,657.27	20-Mar-14
Montreal Sector	Joan Stevens	$4,657.27	20-Mar-14
Portland	Jessica Berry	$4,535.96	20-Mar-14
Quebec Sector	David Baritz	$4,412.52	20-Mar-14
Hawaii	Kelly Baron	$4,299.48	20-Mar-14
D.C.	Damon Clark	$4,096.58	20-Mar-14
Seattle	Anne Cox	$4,057.33	20-Mar-14
LA	Jonni Ricard	$3,757.00	20-Mar-14
Quebec Sector	Yvette Shank	$3,613.71	20-Mar-14
San Francisco	Barbara McDonald	$3,508.31	20-Mar-14
Montreal Sector	Adrian Austin	$3,379.00	20-Mar-14
Buffalo	Emmett James	$3,324.33	20-Mar-14
New Jersey	Willy Wong	$3,256.00	20-Mar-14
San Francisco	Julie Wood	$3,179.54	20-Mar-14
Quebec Sector	Sylvie Melnychuk	$3,168.82	20-Mar-14
Buffalo	Tom Ger	$3,002.00	20-Mar-14
Dallas	Blanchard Clark	$2,972.00	20-Mar-14
Buffalo	Aang Bueno	$2,875.05	20-Mar-14
New Jersey	Katie Crawford	$2,744.10	20-Mar-14
D.C.	Jessica Scozzola	$2,736.61	20-Mar-14
Seattle	Anna Foogi	$2,713.00	20-Mar-14
Hawaii	Wilbur Chun	$2,500.00	20-Mar-14
Buffalo	David Light	$2,469.29	20-Mar-14
Buffalo	Katara Spallino	$2,321.81	20-Mar-14
Buffalo	Tashania Helens	$2,321.81	20-Mar-14

This is a query in which the Gross Sales column is sorted from the most at the top to the least at the bottom.

Now here came the hard part. I'm supposed to add a column at the left which would group the standings into 1st, 2nd, 3rd place and on to the 10th place.

I want to somehow execute a query that looks like this.

Standings	Org	Player Name	Gross Sales	Week Ending
1	Florida	George Clunie	$4000	20-Mar-14
Null	Kansas	Joe Blow	$4000	20-Mar-14
Null	LA	Susie Rile	$4000	20-Mar-14
Null	LA	Roman Stance	$4000	20-Mar-14
2	Montreal	Mat Scheurmann	$3900	20-Mar-14
3	Quebec	Sam Clon	$3500	20-Mar-14
Null	New York	Tom Bailey	$3500	20-Mar-14
4	San Diego	Vanessa Corlene	$2000	20-Mar-14
5	New York	Christian De La Rosa	$100	20-Mar-14

Then I would generate a report based off of this query. I would use the grouping used in Access Reports but they are not is wanted from my authorities.

I am adding a copy of the SQL used for the above query:

SELECT tblOrgs.Org, tblStatisticReports.PlayerID, tblStatisticReports.[Gross Sales], tblStatisticReports.[W/E] AS [Week Ending]
FROM (tblOrgs INNER JOIN tblPlayers ON tblOrgs.OrgID = tblPlayers.OrgID) INNER JOIN tblStatisticReports ON tblPlayers.PlayerID = tblStatisticReports.PlayerID
WHERE (((tblStatisticReports.[W/E])=#3/20/2014#))
ORDER BY tblStatisticReports.[Gross Sales] DESC;

Please let me know if more data is needed. I really would like any help on this.

[ranman256]

Append your results to a 'result' table. This table has an AUTONUMBER field for STANDINGS. (the 1st record gets 1, etc..)
Report off this table.

Now the trick is , autonumbers constantly increment, so it never starts at 1 again, SO....you must keep 2 tables of results and empty one and a working one.
The empty table called tResutls_MT gets copied to the tResults table so anything added starts a 1.

Your macro would be..
1 copy empty table to the working results table via DoCmd.CopyObject
2 append your data to the tResults tbl
3 report on tResults.

[matidiaz]

Thanks for the speedy reply. I definitely used the data here. Though, the table I had posted here had NULL Values. This means on some of the fields I did not want a value.

For example:

1	CAN	$5000
	USA	$5000
	MEX	$5000
2	GER	$4000
3	SWE	$2500
	NOR	$2500
4	ARG	$2000
	And on to the 10th place

An AutoNumber Field increments 1,2,3,4 and on with each row. So I had to add a Standings field to the table. Then use a function I made in VBA to generate the standings.

If anyone wants this code, I can post it up here.

But I couldn't of figured it out without the reply, so thanks again.