Am I dense? FIRST

[sccofer]

I have a rather simple query that has been working for me quite well in the past until this month. I am not sure what changed or if I am doing someting differently but any help would be apprecaited. I put together a simplified example to demonstrate the problem.

Table RETURN
STATE ENTITY RETURN
NC KAA 1
NC KAA 2
CO KAA 3

Table Tax
STATE ENTITY TAX
CO KAA 999
NC KAA 100
NC KAA 200

Following is the query on the above two tables. My thinking is that even though the state and entity for tax amount 100 and 200 match two records in the return table I can limit the result to the first match of return (1 or 2).

SELECT TAX.STATE, TAX.ENTITY, First(RETURN.RETURN) AS FirstOfRETURN, Sum(TAX.TAX) AS SumOfTAX
FROM TAX LEFT JOIN RETURN ON (TAX.ENTITY = RETURN.ENTITY) AND (TAX.STATE = RETURN.sTATE)
GROUP BY TAX.STATE, TAX.ENTITY;

And the result of the above

STATE ENTITY FirstOfRETURN SumOfTAX
CO KAA 3 999
NC KAA 2 600

I would expect that the total for NC be 300 becuase of the FIRST keyword. If I get rid of the sum on tax it returns the following.

STATE ENTITY FirstOfRETURN TAX
CO KAA 3 999
NC KAA 2 100
NC KAA 2 200

which is correct but I want to sum these two amount to show return 2 and total 300. For some reason the sum is throwing the whole thing off and causing a result of 600. Help!!

I am almost 100% positive this worked correctly the prior month and I don't think I have made any changes that would account for this. Any advice

 

[cmmrfrds]

You have a many to many relationship between the 2 tables on the keys you indicate. Get this down to 1 record on the first table. In Access this is usually done by a group by query that is linked to the second table.

SELECT First(RETURN.RETURN) AS FirstOfRETURN,
State,
Entity,
FROM RETURN
GROUP BY STATE, ENTITY;
Save as Qry1

Join Qry1 to the Tax table.