How do I get the last record for an item without the rest of the history

angellrp · Feb 26, 2003

I have a database with a few thousand items on it. Each of these items has a history associated to it using a different table. How do I get a query to produce the last record in the history for each item, without getting all the places that item has been in the past as well as the current location.

Can anyone help?

Mile-O · Feb 26, 2003

You could use the ID from the table and then use it with the Max function to get the last record.

angellrp · Feb 26, 2003

How do I do this with two fields - they have an ID but two different types of item can have the same id but are different types e.g.

bull 1 = monitor & base unit
bull 2 = base unit only
bull 3 = base unit, monitor and tape drive

???

Mile-O · Feb 26, 2003

I would have thougth you would have had a structure like this:

tblItems
ItemID - primary key (autonumber)
ItemName
OtherItemInfo

tblHistory
HistoryID - primary key (autonumber)
ItemID
OtherHistoryDetails

where ItemID in both tables are related in a one-to-many relationship.

By using this structure you can query on all records in the history table where the Item ID is equal to that of the item in tblItems and then you can also query on the Max of the HistoryID which would be the most recent record in that table

angellrp · Feb 26, 2003

I still can't get this to work - I can get the last record if all other fields of that Item ID are the same but as soon there are any differences then all the records that are different come up not just the last one.

sorry to be a pain

Angel

Mile-O · Feb 26, 2003

Can you post an example database?

angellrp · Feb 26, 2003

ok I've cut down the database to the two tables and the two querys.

Query 1 - is the one that worked and Query 2 is when I added more fields .

I want only the details expanded on the records in Q1 - how do I do this?

Thanks

Jon K · Feb 27, 2003

Rebuilt two queries: qryOne and qryTwo

You can run qryTwo to see if it returns the required results.

angellrp · Feb 27, 2003

Thank You

Angel

How do I get the last record for an item without the rest of the history

angellrp

Registered User.

Mile-O

Back once again...

angellrp

Registered User.

Mile-O

Back once again...

angellrp

Registered User.

Mile-O

Back once again...

angellrp

Registered User.

Attachments

Jon K

Registered User.

Attachments

angellrp

Registered User.

Similar threads

Users who are viewing this thread