Query - Using sum to select one row from another table.

[mgunnett]

Howdy, I have an Access 2007 database on a Win Vista x64 that is going to help me keep track of donations to my Clan. I currently have 3 Tables with the following Columns:

User - UserID is unique and the primary Key. Medal is a Foreignkey.

UserID | Username | Member | Medal

Donations - Username is a Foreign key

ID | Username | d_date | Amount

Medals - ID is the primary key.

ID | MedalName | Amount_needed

We award our users with a medal for donating a certain amount (10, 25, 50, 100, 500, 1000) to the forum. We keep track of the total amount donated and award the next medal in the line as they reach the amount of the next medal.

Currently I have 1 query that generates the total amount donated for each user. I have another query currently that is supposed to display the medal name that the user should currently have. There are two fields in the query, 1 listing the username, and the other that is supposed to list the Name of the Medal that the user should currently have. My code is:

Medal: IIf(([Amount_needed])<=[Total],[MedalName],"")

Unfortantly, whenever you run the query, It displays All the medals that the user is eligible for, i.e. if a user has donated $45, it will list 3 rows in the query, null, $10, and $25. I only want to display highest medal the user is eligible for, not all the ones they are/were eligible for.

I've tried doing group by statements without much luck. Any pointers for what I should try next?

[ChipR]

MedalAmount: IIf(([Amount_needed])<=[Total],[Amount_needed],"")

Then group on Username and show Max(Amount_need ed). Then join on the Amount_needed to get the medal name.

[NeoPa]

First of all the Donations table should have UserID as the Foreign Key instead of UserName. There should also be no Medal field in the User table either. This is a calculated field.

SELECT   tU.UserID,
         tU.UserName,
         Sum(tD.Amount) AS SumAmount,
         (
    SELECT Mid(Max(Format([Amount_Needed],'0000000.00') & [MedalName]),11)
    FROM   [Medals] AS tM
    WHERE  tM.Amount_Needed<SumAmount
         ) AS Medal

FROM     [User] AS tU LEFT JOIN
         [Donations] AS tD
  ON     tU.UserID=tD.UserID

This is Air-code, so you may need to test & play with it, but it should work.