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