Max Date Function Not Working

[BLD21]

I am having a problem trying to get the MAX date function to work properly. I am starting with the following query:


SELECT DISTINCT [ADP - WIP Audit 05112017 use].[Associate ID], report_Recurring_Comp.[Pay Component], report_Recurring_Comp.[Event Date] INTO 1
FROM [ADP - WIP Audit 05112017 use] INNER JOIN report_Recurring_Comp ON [ADP - WIP Audit 05112017 use].[Associate ID] = report_Recurring_Comp.[Person Id]
WHERE ((([ADP - WIP Audit 05112017 use].[Associate ID])=47291));

results are:

Associate ID	Pay Component	Event Date
47291	PC000071-US Wireless Phone 75	8/1/2016
47291	PC000071-US Wireless Phone 75	1/2/2017
47291	PC000071-US Wireless Phone 75	5/8/2017
47291	S-Base Salary	7/31/2016
47291	S-Base Salary	8/1/2016
47291	S-Base Salary	1/2/2017
47291	S-Base Salary	5/8/2017

I now add max date to event date field:
SELECT DISTINCT [ADP - WIP Audit 05112017 use].[Associate ID], report_Recurring_Comp.[Pay Component], Max(report_Recurring_Comp.[Event Date]) AS [MaxOfEvent Date] INTO 1
FROM [ADP - WIP Audit 05112017 use] INNER JOIN report_Recurring_Comp ON [ADP - WIP Audit 05112017 use].[Associate ID] = report_Recurring_Comp.[Person Id]
GROUP BY [ADP - WIP Audit 05112017 use].[Associate ID], report_Recurring_Comp.[Pay Component]
HAVING ((([ADP - WIP Audit 05112017 use].[Associate ID])=47291));

Results:

Associate ID	Pay Component	MaxOfEvent Date
47291	PC000071-US Wireless Phone 75	5/8/2017
47291	S-Base Salary	5/8/2017

good so far, but when I add the amount field all dates come back:
SELECT DISTINCT [ADP - WIP Audit 05112017 use].[Associate ID], report_Recurring_Comp.[Pay Component], Max(report_Recurring_Comp.[Event Date]) AS [MaxOfEvent Date], report_Recurring_Comp.Amount INTO 1
FROM [ADP - WIP Audit 05112017 use] INNER JOIN report_Recurring_Comp ON [ADP - WIP Audit 05112017 use].[Associate ID] = report_Recurring_Comp.[Person Id]
GROUP BY [ADP - WIP Audit 05112017 use].[Associate ID], report_Recurring_Comp.[Pay Component], report_Recurring_Comp.Amount
HAVING ((([ADP - WIP Audit 05112017 use].[Associate ID])=47291));

results:

Associate ID	Pay Component	MaxOfEvent Date	Amount
47291	PC000071-US Wireless Phone 75	5/8/2017	75
47291	S-Base Salary	8/1/2016	3276.08
47291	S-Base Salary	1/2/2017	3339.54
47291	S-Base Salary	5/8/2017	3426.37
47291	S-Base Salary	5/8/2017	3846.16

any help is greatly appreciated!
Bruce

[ranman256]

MAX (or min) only works with 2 fields. Otherwise you get the MAX involving 3 fields, and it is not max anymore.
you want the max(field) and the ID. Then use the ID to grab the record.

Q1: select ID, MAX(field) from table
Q2: select * from table where Q1.ID = table.ID

[aytee111]

You could try changing the Group By on the amount to First.

[BLD21]

Thanks for responding, I forgot to clarify I need the max date within each Pay Component. There could be multiple pay components and I need to see just the max date for each.

[BLD21]

It looks like Group By on the amount to First will work.

Thanks aytee111 and ranman256.