Most Recent Dates When Using Group By and Max Functino 1

[Lightbulb]

Someone help! In creating an inventory system I have been trying to query four fields from multiple tables grouping the records by the serial number (same one occurs multiple times in assignment table) and attempting to get the most recent record for each serial group by using the max function with the date when I do totals. However, I also want the first and last name persons in the table and am forced to choose a total value such as max, min, var... While I do get back the correct serial number and assignment date, I don't always get back the correct first OR last name that corresponds to the serial number. How can I create a query using GROUP BY for serial number, MAX for the date while simultaneously retrieving additional fields for the same record such as first and last name and have both name fields be accurate? Below is a simplified summary example of my query.

SELECT [ASSIGNMENT_SERIAL_NO], [MAX(ASSIGNMENT_DATE_ASSIGNED)], ASSIGNMENT_FIRST_NAME, ASSIGNMENT_LAST_NAME, FROM ASSIGNMENT GROUP BY ASSIGNMENT_SERIAL_NO

First and last name are from different tables than in the query above. However, in this example, pretend they are all from the same table and disregard that I left out some of the syntax such as AS, possible special characters and so forth since I tried to simplify it for this example. I've been working on this one for a couple of days. I've also searched this site and didn't find a solution directly related to my problem. Any help would be greatly appreciated. Thanks.

 

[jebry]

Hi!

If you use aggregate functions every field has to be used in some aggregate. I would suggest that you use Group By for the last name and the first name as well as the assignment serial number.

hth
Jeff Bridgham

 

[Lightbulb]

Thanks jebry. However, I tried your solution and ended up getting multiple records for the same serial number instead of the most recent date assigned for each serial number that occurs more than once. Any more suggestions would be appreciated, else thanks anyway.

 

[tlbroadbent]

Try this query.

SELECT
Assignment_Serial_No,
Assignment_Date_Assigned,
Assignment_First_Name,
Assignment_Last_Name
FROM Assignment AS a
WHERE Assignment_Date_Assigned =
(SELECT MAX(Assignment_Date_Assigned)
FROM Assignment
WHERE Assignment_Serial_No=a.Assignment_Serial_No) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Lightbulb]

Thanks Terry,

However, when I run the query you specified, I get the following records back. Notice that duplicate serial numbers are printed and the date assigned was somehow erroneously duplicated; not completely reflective of actual data in the table. Interestingly, although the dates are duplicated with each corresponding serial number, I have verified that they are actually the most recent dates for the serial numbers below. Now, if I could just get them to print once for each serial number and with the remaining fields being correct also. Any suggestions. If not, thanks again.

Serial Date Assign Fst Nm. Lst. Nm.

5433 8/15/2001 Jo N
5433 8/15/2001 Dan R
5433 8/15/2001 Jay Fosh
5433 8/15/2001 Al Lanster
7564 8/19/2001 Jo N
7564 8/19/2001 Dan R
7564 8/19/2001 Jay Fosh
7564 8/19/2001 Al Lanster
8989 2/1/2001 Jo N
8989 2/1/2001 Dan R
8989 2/1/2001 Jay Fosh
8989 2/1/2001 Al Lanster
8888 8/25/2001 Jo N
8888 8/25/2001 Dan R
8888 8/25/2001 Jay Fosh
8888 8/25/2001 Al Lanster

 

[Lightbulb]

Terry,

My error. Your solution worked. Earlier (above) I mentioned that the first and last name fields were not in the assignment table as I indicated above. Therefore when I ran your query, I used the other table containing the first and last names from another table and the serial and date assigned from the assignment table. When you gave me your solution with just the assignment table, that's the way I gave it to you which wasn't your fault. So, at first, when I ran your query, it didn't work based on the two tables I used. However, when I used click and drag to establish a relationship between the two tables on a related field, all of a sudden I was no longer getting duplicated records and the data was 100% accurate! This has been a difficult problem for me. Thanks a million!