How get latest dates plus 3 other data for records from a table?

[Punice]

Giving up after a zillion tries. I have a table (tblLOADS) containing: BROKER, PUDATE, MATERIAL & DRIVER. I am able to create this query:

SELECT tblLOADS.L_ID, tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate, tblLOADS.Material, tblLOADS.Driver
FROM tblLOADS
GROUP BY tblLOADS.L_ID, tblLOADS.BROKER, tblLOADS.Material, tblLOADS.Driver
HAVING (((Max(tblLOADS.Pudate))>0));

Problem: It's datasheet view displays all of the records for BROKERS, PUDATE, MATERIAL & DRIVER, instead of ONLY the records for the last PUDATE of each of the BROKERS, with their corresponding MATERIAL & DRIVER fields.

 

[vbaInet]

A subquery is what you need. Follow these examples:

http://allenbrowne.com/subquery-01.html

 

[Punice]

I tried converted some of Mr. Browne's sql to apply to my applications, but what I tried did not work. Still showed all records for the BROKER, Pudate, Material & Driver.

Still looking for the correct solution to my problem.

 

[vbaInet]

Let's see the SQL of what you tried.

 

[Brianwarnock]

I prefer simple stack queries where query1 gets the max pudate grouped by Broker and then in query2 this is joined on these fields back to the table to get the rest of the data.
Don't know where L_ID comes in , it is in the Select but not mentioned in the text

Brian

 

[vbaInet]

I concur Bri! But that would depend on the kind of data Punice has. Perhaps Punice can show us some sample data so that we can advise which method best suits.

 

[Punice]

The "L_ID" is the 'Load identification number' that is automatically created when a new load is entered on the 'Loads' form, where the control is located that runs the following sql.

Here is my sql:

SELECT tblLOADS.L_ID, tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate, tblLOADS.Material, tblLOADS.Driver
FROM tblLOADS
GROUP BY tblLOADS.L_ID, tblLOADS.BROKER, tblLOADS.Material, tblLOADS.Driver
HAVING (((Max(tblLOADS.Pudate))>0));

 

[vbaInet]

Can we see some sample records, upload as an xls document. Non confidential data of course.

 

[Punice]

Let's say that I have a table (tblLOADS) with BROKER, Pudate, MATERIAL & DRIVER information and I want that information from the last entry for each BROKER.

Based on a forum response from 'bpalsy' (I think),
I need to create two queries: one that gets the most recent entry for each BROKER:

q_Latest_Pudates
SELECT tblLOADS.L_ID, Max(tblLOADS.Pudate) AS MaxOfPudate
FROM tblLOADS
GROUP BY tblLOADS.L_ID;

Then, in a second query, I need to join the tblLOADS and q_Latest_Pudates to get the MATERIAL & DRIVER information associated with the records:

q_Latest_Load
SELECT tblLOADS.*
FROM tblLOADS
INNER JOIN q_Latest_Pudates
ON (tblLOADS.L_ID = q_Latest_Pudates
AND (tblLOADS.Pudate = q_Latest.Pudate.MaxPudate);

When I try to save that query, I am advised that I have an error in the 'FROM' statement. Did I make a typing or transcription error?

 

[Punice]

Found this Re: Query on Latest Dates from pbaldy and modified it to fit my application: Here is the query sqls that provided my what I wanted.

SELECT tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate
FROM tblLOADS
GROUP BY tblLOADS.L_ID;

SELECT tblLOADS.BROKER, tblLOADS.MATERIAL, tblLOADS.DRIVER
FROM tblLOADS
INNER JOIN q_Max_Pudates
ON (tblLOADS.BROKER = q_Max_Pudates.BROKER)
AND (tblLOADS.Pudate = q_Max_Pudates.MaxOfPudate);

Thanks to all who helped me. I DO appreciate your taking the time & effort.

 

[Brianwarnock]

that is exactly what I said to do, except surely this

GROUP BY tblLOADS.L_ID;

in the first query

should be

GROUP BY tblLOADS.BROKER;

Brian

 

[Brianwarnock]

Let's say that I have a table (tblLOADS) with BROKER, Pudate, MATERIAL & DRIVER information and I want that information from the last entry for each BROKER.

Based on a forum response from 'bpalsy' (I think),
I need to create two queries: one that gets the most recent entry for each BROKER:

q_Latest_Pudates
SELECT tblLOADS.L_ID, Max(tblLOADS.Pudate) AS MaxOfPudate
FROM tblLOADS
GROUP BY tblLOADS.L_ID;

Then, in a second query, I need to join the tblLOADS and q_Latest_Pudates to get the MATERIAL & DRIVER information associated with the records:

q_Latest_Load
SELECT tblLOADS.*
FROM tblLOADS
INNER JOIN q_Latest_Pudates
ON (tblLOADS.L_ID = q_Latest_Pudates.L_ID
AND (tblLOADS.Pudate = q_Latest.Pudate.MaxPudate);

When I try to save that query, I am advised that I have an error in the 'FROM' statement. Did I make a typing or transcription error?

The error here was that you left out the bit I have added in red.

Brian

 

[Punice]

Brian, In my final attempt to get the code to produce what I wanted, I changed 'ON (tblLOADS.L_ID = q_Latest_Pudates.L_ID' to 'ON (tblLOADS.BROKER = q_Max_Pudates.BROKER)'.

Thanks for spotting my omission. Punice