Selecting from a database where the joins have history

[Stephen]

I have a database with two tables, each with a numerical primary key field
and a name (text) field, joined by a third containing both the primary key
fields from the other two along with an integer containing a unix date to
provide the time of the changes in the relationship between the outer two
plus another numerical field specifying the person who made the change. I
need to construct a query that provides only the latest pairing of the two
outer tables. The only data I want returned are the numerical primary key
fields from the outer tables as specified by the maximum date.

TableA
ItemtypeAID INTEGER Primary Key
ItemTypeAName TEXT

TableC
ItemtypeCID INTEGER Primary Key
ItemTypeCName TEXT

TableB
ItemtypeAID INTEGER Primary Key
ItemtypeCID INTEGER Primary Key
ChangeDate INTEGER Primary Key
PersonnelID INTEGER

I have tried the following but it returns too many records. It returns a
record for each pairing of the outer two and not just the most recent.

SELECT ItemtypeAID, ItemtypeCID, Max(ChangeDate) FROM TableB GROUP BY
ItemtypeAID, ItemtypeCID;

Any suggestions would be most welcome.

 

[Allen Browne]

Firstly, open TableB in design view, and make sure you have a unique index
on the combination of ItemTypeAID + ChangeDate. If you don't have such an
index, the idea of "the latest record" is not properly defined (since there
could be two.)

Now create this query:
SELECT ItemtypeAID,
Max(ChangeDate) AS MaxChangeDate
FROM TableB
GROUP BY ItemtypeAID;
Save the query with a name such as qryItemDate.

Create another query, that uses TableB and qryItemDate as input tables. Join
TableB.ItemtypeAID to qryItemDate.AID. Join TableB.ChangeDate to
qryItemDate.MaxChangeDate. These inner joins limit the table to only the
ones that match the most recent change date. You can output any fields you
want from TableB.

For other suggestions, see Michel Walsh's article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm