most recent dates of duplicate records

[Paula]

I have a table of training records. The table holds Last Name, First Name,
Course # Course Title, Completion Date, and Expiration Date for each record.
As the training expires, new training records are added with new completion
dates and new expiration dates.

I have created a report that will pull the records with the expiration dates
that fall "between now() and Now()+30" (within 30 days). But, it pulls all
the duplicate records, not just the most recent.

I tried using the Max function, but I couldn't get it to work.

Can someone give me a clue how to setup the query to pull the records with
the latest expiration dates that fall within the next 30 days.

 

[KARL DEWEY]

Post your SQL statement.

 

[Paula]

Below is the SQL statement that I currenly have. But when I add the criteria
of Between now() and now()+ 30 I don't get the most recent dates for the 30
time period, I get them all.

Thanks for your help


SELECT [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date], Max([Master
Training Table].[Exp Date]) AS [MaxOfExp Date], tblIndividuals.Active
FROM tblCourses INNER JOIN (tblIndividuals INNER JOIN [Master Training
Table] ON tblIndividuals.[Badge #] = [Master Training Table].[Badge #]) ON
tblCourses.[Course#] = [Master Training Table].[Course#]
GROUP BY [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date],
tblIndividuals.Active
HAVING (((tblIndividuals.Active)=Yes))
ORDER BY tblIndividuals.[Last Name];

 

[KARL DEWEY]

You need to post what is not working so it can be analyzed.

It is difficult to figure out what is wrong if I only see what is working.

Below is the SQL statement that I currenly have. But when I add the criteria
of Between now() and now()+ 30 I don't get the most recent dates for the 30
time period, I get them all.

Thanks for your help


SELECT [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date], Max([Master
Training Table].[Exp Date]) AS [MaxOfExp Date], tblIndividuals.Active
FROM tblCourses INNER JOIN (tblIndividuals INNER JOIN [Master Training
Table] ON tblIndividuals.[Badge #] = [Master Training Table].[Badge #]) ON
tblCourses.[Course#] = [Master Training Table].[Course#]
GROUP BY [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date],
tblIndividuals.Active
HAVING (((tblIndividuals.Active)=Yes))
ORDER BY tblIndividuals.[Last Name];

Post your SQL statement.

 

[Paula]

Below is the SQL statement after I add Between now() and Now()+30 in the
criteria for the query. And I may be crazy, but I think it is working now.
Let me check to be sure. Thanks for you help

SELECT [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date], Max([Master
Training Table].[Exp Date]) AS [MaxOfExp Date], tblIndividuals.Active
FROM tblCourses INNER JOIN (tblIndividuals INNER JOIN [Master Training
Table] ON tblIndividuals.[Badge #] = [Master Training Table].[Badge #]) ON
tblCourses.[Course#] = [Master Training Table].[Course#]
GROUP BY [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date],
tblIndividuals.Active
HAVING (((Max([Master Training Table].[Exp Date])) Between Now() And
Now()+30) AND ((tblIndividuals.Active)=Yes))
ORDER BY tblIndividuals.[Last Name];

You need to post what is not working so it can be analyzed.

It is difficult to figure out what is wrong if I only see what is working.

Below is the SQL statement that I currenly have. But when I add the criteria
of Between now() and now()+ 30 I don't get the most recent dates for the 30
time period, I get them all.

Thanks for your help


SELECT [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date], Max([Master
Training Table].[Exp Date]) AS [MaxOfExp Date], tblIndividuals.Active
FROM tblCourses INNER JOIN (tblIndividuals INNER JOIN [Master Training
Table] ON tblIndividuals.[Badge #] = [Master Training Table].[Badge #]) ON
tblCourses.[Course#] = [Master Training Table].[Course#]
GROUP BY [Master Training Table].[Badge #], tblIndividuals.[Last Name],
tblIndividuals.[First Name], [Master Training Table].[Course#],
tblCourses.[Course Title], [Master Training Table].[Comp Date],
tblIndividuals.Active
HAVING (((tblIndividuals.Active)=Yes))
ORDER BY tblIndividuals.[Last Name];

Post your SQL statement.

:

I have a table of training records. The table holds Last Name, First Name,
Course # Course Title, Completion Date, and Expiration Date for each record.
As the training expires, new training records are added with new completion
dates and new expiration dates.

I have created a report that will pull the records with the expiration dates
that fall "between now() and Now()+30" (within 30 days). But, it pulls all
the duplicate records, not just the most recent.

I tried using the Max function, but I couldn't get it to work.

Can someone give me a clue how to setup the query to pull the records with
the latest expiration dates that fall within the next 30 days.