Trouble Getting the Data I want

[rts]

I am working on a Profit Sharing database. I am trying to create an Append Query to be run at Payout time, but I'm not getting the info I need to show. I have a table TeamMembers with ID as the PK and a SalaryInfo table with TeamMemberID as the FK. It's a one-to-many relationship so that we can keep track of Salary and Profit Sharing % as employees get raises, etc. The SalaryInfo table has fields: ID, TeamMemberID, Salary, AdjustmentDate, and ProfitSharePercent. We need to be able to pull the most recent records from the SalaryInfo table into a query so we can use them in math equations. I created one Query hoping to be able to use it in my Append Query, but when I try to add the necessary fields (Salary and Profit Sharing %), it pulls the first record for each TeamMember instead of looking at the record correlating to the MaxofAdjustmentDate field from the SalaryInfo table. Here is the first query:

SELECT TeamMembers.ID, Max(SalaryInfo.AdjustmentDate) AS MaxOfAdjustmentDate, Round((DateSerial(Year(Date()),12,31)-[StartDate])/7/52,2) AS YearsOfContribution, TeamMembers.Active, TeamMembers.ProfitSharingCategory, TeamMembers.First, TeamMembers.Last
FROM TeamMembers INNER JOIN SalaryInfo ON TeamMembers.[ID] = SalaryInfo.[TeamMemberID]
GROUP BY TeamMembers.ID, Round((DateSerial(Year(Date()),12,31)-[StartDate])/7/52,2), TeamMembers.Active, TeamMembers.ProfitSharingCategory, TeamMembers.First, TeamMembers.Last
HAVING (((TeamMembers.Active)=True) AND ((TeamMembers.ProfitSharingCategory)=1)) OR (((TeamMembers.ProfitSharingCategory)=2));



I was trying to do a second query and hoped that if I used the MaxofAdjustmentDate, it would populate the Salary and ProfitSharePercent fields with the corresponding records from that date. It doesn't work. It pulls the first record for each TeamMember instead. I was told that using "Last" in the Totaling Options was a bad idea because it would pull random records, but I can't use Max in ProfitSharePercent or Salary because it is possible that the Max value is not the most recent record. Someone's ProfitSharePercent may have decreased while their salary increased and vice versa.



Any help on how to properly query this would be greatly appreciated.

Thanks!

[June7]

Try these options:

1. TOP parameter in query 1 to pull that most recent AdjustmentDate for each member from SalaryInfo. Review http://allenbrowne.com/subquery-01.html#TopN
Then join that query to SalaryInfo and apply filter to retrieve records where the AdjustmentDate matches the date retrieved by the TOP query.

2. DMax() expression in query:
SELECT * FROM SalaryInfo WHERE AdjustmentDate= DMax("AdjustmentDate", "SalaryInfo", "TeamMemberID=" & [TeamMemberID])

[rts]

Thank you much! That was the missing information I needed.

[rts]

I've got it working great and now I'm stuck on another problem. I need to do a separate query that pulls each TeamMember's last Salary Adjustment from before Jan 1 of the current year. I've tried adding another criteria to the DMax Statement and I've tried using subqueries from the link above, but I either get error messages or it returns no results. Can you use DMax on a field and also use that field in the criteria of the argument? I basically want to say exactly what you have above and add, WHERE Year([AdjustmentDate]) < Year(Date()) before it calculates the DMax.

[June7]

Where are you using DMax? Post the complete sql statement.

[rts]

SELECT Round(((DateSerial(Year(Date()),12,31)-365)-[StartDate])/7/52,2) AS YearsOfContribution, *
FROM TeamMembers INNER JOIN SalaryInfo ON TeamMembers.TMID = SalaryInfo.TeamMemberID
WHERE (((SalaryInfo.AdjustmentDate)=DMax("AdjustmentDate ","SalaryInfo","TeamMemberID=" & [TeamMemberID])) AND ((TeamMembers.Active)=True));

[rts]

I think I got it!

SELECT Round(((DateSerial(Year(Date()),12,31)-365)-[StartDate])/7/52,2) AS YearsOfContribution, *
FROM TeamMembers INNER JOIN SalaryInfo ON TeamMembers.TMID = SalaryInfo.TeamMemberID
WHERE SalaryInfo.SID IN
(SELECT TOP 1 SID
FROM SalaryInfo AS Dupe
WHERE Dupe.TeamMemberID = SalaryInfo.TeamMemberID
AND Dupe.AdjustmentDate < DateSerial(Year(Date()),1,1)
ORDER BY Dupe.AdjustmentDate DESC)