Selecting from a database where the joins have history



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.

ItemtypeAID INTEGER Primary Key
ItemTypeAName TEXT

ItemtypeCID INTEGER Primary Key
ItemTypeCName TEXT

ItemtypeAID INTEGER Primary Key
ItemtypeCID INTEGER Primary Key
ChangeDate INTEGER Primary Key

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:
Max(ChangeDate) AS MaxChangeDate
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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question