I would like to track the revision history of table records. To do so, I have created tables with two-key fields. A typical table is tblPO_Data. The first key field is PO_Number and the second key field is PO_Revision. Data fields follow.

A key element of this table is that each record may have any number of data fields left blank AND later revisions may only show the "changed" field values.

To Illustrate tblPO_Data:

PO_Number PO_Revision Length Width Height Weight Quant UPrice
100 _________0___________10_____15___________________2 ____20
100__________1_________________________30
100__________2_________________________35____100
200 _________0_______________________________________1 ____10

NOW I would like to create a query that lists one record for each PO number, showing the most recently revised data. The desired result would look like:

PO_Number PO_Revision Length Width Height Weight Quant UPrice
100_____________2_______10_____15_____35_____100__ ___2____20
200_____________0_________________________________ ___1____10

Please realize that this is an example. There would be numerous one to one tables for each PO number to reduce the number of blank fields (ie. only tables with changes would be get revised records).

Can someone suggest a way to create a query to accomplish the above requirements, thanks in advance, Jeff...

Try this SQL (substitute the actual table/field names for the highlighted text):

SELECT T1.*
FROM tblPO_Data T1
INNER JOIN
(SELECT T2.PO_Number, MAX(T2.PO_Revision) AS PO_Revision
FROM tblPO_Data T2
GROUP BY T2.PO_Number) T2
ON T1.PO_Number = T2.PO_Number
AND T1.PO_Revision = T2.PO_Revision

This is similar to a problem I have, but I suspect I need a much simpler approach (for me to understand it anyway).

I have two one-many related tables: Transactions and Site Details. Most of the Transactions information can be filled in first, and then the Site Details filled in when they are ready.

How do I get a query based on both tables to show those transactions with missing site details? I have tried IsNull(SiteName) in the SiteName field in the Design View, but I don't get any results at all. The answers I've seen in Google seem to be variations on the theme, but I am probably missing something simple (as usual).

This is similar to a problem I have, but I suspect I need a much simpler approach (for me to understand it anyway).
The two scenarios are nothing alike. We have:
Query to Track Revision History
vs.
Show fields with no data in the query

Please post your question in a brand new thread. It will be much easier for users to find with the site's search functions.