469,630 Members | 1,209 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,630 developers. It's quick & easy.

Selecting most recent records for a query

55
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
Oct 14 '08 #1
13 3780
DonRayner
489 Expert 256MB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias, Max(Table2.LatestUsedDate) AS MaxOfLatestUsedDate
  2. FROM Table2 RIGHT JOIN Table1 ON Table2.ItemNumber = Table1.ItemNumber
  3. GROUP BY Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias;
  4.  
Oct 14 '08 #2
angi35
55
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias, Max(Table2.LatestUsedDate) AS MaxOfLatestUsedDate
  2. FROM Table2 RIGHT JOIN Table1 ON Table2.ItemNumber = Table1.ItemNumber
  3. GROUP BY Table1.ItemNumber, Table1.ItemDescription, Table2.ItemAlias;
  4.  
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
Oct 15 '08 #3
DonRayner
489 Expert 256MB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ItemNumber, Max(Table2.LatestUsedDate) AS MaxOfLatestUsedDate, Max(Table2.ID) AS MaxOfID
  2. FROM Table1 INNER JOIN Table2 ON Table1.ItemNumber = Table2.ItemNumber
  3. GROUP BY Table1.ItemNumber;
  4.  
Then create another query (call it whatever you want) and put this sql in it

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ItemNumber AS TheNumber, Table1.ItemDescription, Table2.ItemAlias, Table2.LatestUsedDate
  2. FROM Query1 INNER JOIN (Table2 INNER JOIN Table1 ON Table2.ItemNumber = Table1.ItemNumber) ON Query1.MaxOfID = Table2.ID
  3. ORDER BY Table1.ItemNumber, Table2.LatestUsedDate;
  4.  
I've tried it and it works.
Oct 15 '08 #4
angi35
55
Drat... I'm working with linked tables from an external (non-Access) database to which I can't add fields.
Oct 16 '08 #5
DonRayner
489 Expert 256MB
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
Oct 16 '08 #6
angi35
55
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?)?
Oct 16 '08 #7
DonRayner
489 Expert 256MB
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
Oct 16 '08 #8
FishVal
2,653 Expert 2GB
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
Oct 16 '08 #9
angi35
55
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
Oct 17 '08 #10
DonRayner
489 Expert 256MB
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Docmd.RunSql "QueryName"
  3. DoCmd.SetWarnings True
  4.  
Oct 17 '08 #11
angi35
55
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Docmd.RunSql "QueryName"
  3. DoCmd.SetWarnings True
  4.  
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?
Oct 17 '08 #12
angi35
55
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.
Oct 17 '08 #13
DonRayner
489 Expert 256MB
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"
Oct 17 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Mark Hargreaves | last post: by
reply views Thread by allyn44 | last post: by
3 posts views Thread by manny | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.