Selecting most recent records for a query

[angi35]

Hi - working in Access 2000... my goal is to combine fields from two tables in a query:

Table 1 has ItemNumber and ItemDescription. There's only one record per ItemNumber.
Table 2 has ItemAlias. It also has ItemNumber and LatestUsedDate.

Table 2 may not have any record for an ItemNumber in Table 1; it may have one record; or it may have several records for a given ItemNumber.

Sometimes the LatestUsedDate is filled in; sometimes it's not. Where there are more than one record with the same item number, the LatestUsedDate is always filled in for at least one of the records.

I need a query that will give me the ItemNumber and ItemDescription from Table 1 and the ItemAlias from Table 2. If there are more than one record for a given ItemNumber in Table 2, I need the ItemAlias for the record with the most recent LatestUsedDate.

So I create a query with just ItemNumber and MaxOfLatestUsedDate from Table 2.

The problem is getting the ItemAlias in there, because I can't "group by" the alias - it's different for each record when there are multiple records with one item number, so I'm back at square one with multiple records for one ItemNumber.

I tried creating a new query using the first query (ItemNumber and MaxOfLatestUsedDate), linked to Table 2 on ItemNumber and pulling the two fields from the query plus the ItemAlias field from the table; didn't work.

Any ideas?

Thanks,
Angi

[DonRayner]

Try pasting this sql code into the sql view of a new query. You will have to change the table1 and 2 to whatever your actual table names are, but it should work for you.

SELECT Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias, Max(Table2.LatestUsedDate) AS MaxOfLatestUsedDate
FROM Table2 RIGHT JOIN Table1 ON Table2.ItemNumber = Table1.ItemNumber
GROUP BY Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias;
 

[angi35]

Try pasting this sql code into the sql view of a new query. You will have to change the table1 and 2 to whatever your actual table names are, but it should work for you.

SELECT Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias, Max(Table2.LatestUsedDate) AS MaxOfLatestUsedDate
FROM Table2 RIGHT JOIN Table1 ON Table2.ItemNumber = Table1.ItemNumber
GROUP BY Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias;
 

Thanks for the idea.. I'm afraid all that did was freeze up Access so I had to Ctrl+Alt+Del to close it. I've double-checked that I correctly named all my tables and fields in the code.

I don't know if this will help, but here's an example of the data in Table2:

ItemNumber, ItemAlias, LatestUsedDate
123-04, Bear,
123-04, Zebra, 8-12-05
123-04, Tiger, 6-12-07
123-05, Leopard,
123-06, Bear,
123-07, Leopard, 9-13-08

(I think I need a trip to the zoo...)

So I've got multiple records for one item number with different aliases and some dates but not all; and multiple part numbers for the same alias (which doesn't matter to me, but I don't know if it's confusing the code). What I need is no duplicate part numbers; in the case above, the query should return Tiger for item number 123-04 because that's the most recent record for that item, and the aliases for 123-05 thru 123-07 (which have no duplicates).

And of course, the query is also returning the ItemNumber and ItemDescription from Table1.

Any more suggestions?
Angi

[DonRayner]

Ok, try this.

Add a new field to your Table2 to called ID and make it a sequencialy numberd autonumber.

Create a query called Query1 and put this sql in it

SELECT Table1.ItemNumber, Max(Table2.LatestUsedDate) AS MaxOfLatestUsedDate, Max(Table2.ID) AS MaxOfID
FROM Table1 INNER JOIN Table2 ON Table1.ItemNumber = Table2.ItemNumber
GROUP BY Table1.ItemNumber;
 

Then create another query (call it whatever you want) and put this sql in it

SELECT Table1.ItemNumber AS TheNumber, Table1.ItemDescription, Table2.ItemAlias, Table2.LatestUsedDate
FROM Query1 INNER JOIN (Table2 INNER JOIN Table1 ON Table2.ItemNumber = Table1.ItemNumber) ON Query1.MaxOfID = Table2.ID
ORDER BY Table1.ItemNumber, Table2.LatestUsedDate;
 

I've tried it and it works.

[angi35]

Drat... I'm working with linked tables from an external (non-Access) database to which I can't add fields.

[DonRayner]

Drat... I'm working with linked tables from an external (non-Access) database to which I can't add fields.

You could always create a local table with the autonumbered field and then use an append query to update it with the records from the linked table

[angi35]

But I'm creating a report other users will be running. If the data changes in the original table, won't users have to manually recreate the local table each time they want to run the latest report? Or at least run an append query to add any new records to the existing local table (and then would the db automatically number the appended records, or would that have to be done manually as well?)?

[DonRayner]

But I'm creating a report other users will be running. If the data changes in the original table, won't users have to manually recreate the local table each time they want to run the latest report? Or at least run an append query to add any new records to the existing local table (and then would the db automatically number the appended records, or would that have to be done manually as well?)?

Create the append query and put the code to run it in the main forms on open event. It will then run the append query when the user starts using the database. Just make sure that the query is sorted on the date field. The autonumber in the local table will number the records as they are appended.

If the database is small enough to be appending the entire thing each time the database is run you could then run a delete query to remove all the records in the forms on close event. Otherwise your append query is going to have to check the records in the local table against the linked table and only append anything that is new

[FishVal]

Hello, gentlemen.

@angi

Could it be so that Table2 has multiple records with the same ItemNumber and LatestUsedDate. If so, then explain please how it could be resolved in terms of your application business logic?

@DonRayner

IMHO, the situation is not that bad to use additional fields, temporary tables and all the rest weapons of mass destruction. ;)

Regards,
Fish

[angi35]

Create the append query and put the code to run it in the main forms on open event. It will then run the append query when the user starts using the database. Just make sure that the query is sorted on the date field. The autonumber in the local table will number the records as they are appended.

If the database is small enough to be appending the entire thing each time the database is run you could then run a delete query to remove all the records in the forms on close event. Otherwise your append query is going to have to check the records in the local table against the linked table and only append anything that is new

It's all coming together now. Thanks for the help! One more question... what is the code to tell the form to run the append query (and delete query) I created? I haven't figured out how to do that.

Thanks,
Angi

[DonRayner]

It's all coming together now. Thanks for the help! One more question... what is the code to tell the form to run the append query (and delete query) I created? I haven't figured out how to do that.

Thanks,
Angi

DoCmd.SetWarnings False
Docmd.RunSql "QueryName"
DoCmd.SetWarnings True
 

[angi35]

DoCmd.SetWarnings False
Docmd.RunSql "QueryName"
DoCmd.SetWarnings True
 

I'm getting an error message:
Runtime Error 3129
Invalid SQL statement
Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

It's highlighting line 2 in the code. Any ideas?

[angi35]

I'm getting an error message:
Runtime Error 3129
Invalid SQL statement
Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

It's highlighting line 2 in the code. Any ideas?

So... answering my own question (partially), I wrote the queries directly into the VB code. But I'd still love to know if there's a way to use VB code to run an existing query without having to rewrite the whole query.

[DonRayner]

So... answering my own question (partially), I wrote the queries directly into the VB code. But I'd still love to know if there's a way to use VB code to run an existing query without having to rewrite the whole query.

For an external query it's docmd.openquery "QueryName"