Qry to group & Sum all data but only returning the location of where Max Pts was

[Lor]

I have table with multi Text columns (location, coaches,ssn etc), and
multiple Num columns (pts and product counts)
I need to sum all the unit counts & points for each person, but only
bring back the location for each person that had the 'max' sales (pts).

ex. ssn xxxxxx has 3 sales codes and code 1&2 had less sales aquired
then sales code 3. Sales code 1 was location Vt, slsCd 2 was location
CA and slscd 3 was location FL. I need to bring back the sum of all
three codes but only the location FL as it had the most sales pointing
to it.
I have deadline of Monday to get this done.PLEASE help

 

[Michel Walsh]

Hi,


a deadline as in homework?


SELECT salerID, SUM(amount) AS sa, MAX(amount) AS ma
FROM myTable


can be a saved query, q1.


A second query returning what you seek would then be:


SELECT q1.*, myTable.Location
FROM myTable INNER JOIN q1 on myTable.amount = q1.ma

Now, if this is homework, you still have to explain it, otherwise, you have
something working and can ask question about it, past the 'deadline'.

Hoping it may help
Vanderghast, Access MVP