Getting most recent record using Grouping query
Hi guys,
I have been struggling trying to resolve this issue for days and just can't seem to crack it so I'm throwing it out there for some help.I have attached a stripped down version of the database as well.I have a table called "PARTS". It contains records for each part in the inventory. The key field is called [PartID]. I also have another table called "AUDIT" that contains records that are created everytime the cost price field is updated or if the User double clicks the [NEW] field displayed on the form, fBulkPartsUpdate. This is to provie a history of cost price changes. The Audit table also contains a range of other records and data relating to changes to various fields in the PARTS table so the names of some of the fields are a bit generic.
Here is my problem:
I have a form called fBulkPartsUpdate that displays some data from the PARTS table. It's data source is a query called, qryParts_00. Within the qryParts_00 query there is another query called, qryLastWSPUSD.
When you open the fBulkPartsUpdate form, you will see the following columns:
Part Name
OLD
NEW
Part #
PartID
If you "right click" the [WSP (USD)] cell ($8.40) for the first record, Alternator Stand - Cast aluminium, a popup form will be displayed that accesses the AUDIT table, finds the records that match the [PartID], in this case #238, and displays them in the popup form.
You will see displayed in the pop up form that the newest record is dated 26/10/2018 and that the "Old" is $8.40 and the "New" is $8.40 but if you look at the main form, fBulkPartsUpdate, you will see that the "OLD" field contains $9.55 which is wrong. I need the newest timestamped record values for "OLD" to be displayed in the fBulkPartsUpdate form.
If you "right click" a few of the other records you will see that some are correct and others aren't.
If you need to create a new record in the AUDIT table, simply double click any of the [NEW] fields of a record. You will need to press F5 to refresh.
I want to find the most recent record in the AUDIT table for the record and pick out the "OLD" value and insert it into the "OLD" field in the fBulkPartsUpdate form.....
I hope I've made myself clear.
Thanks for your help with this.
Cheers
Greg
Hi guys,
I have been struggling trying to resolve this issue for days and just can't seem to crack it so I'm throwing it out there for some help.I have attached a stripped down version of the database as well.I have a table called "PARTS". It contains records for each part in the inventory. The key field is called [PartID]. I also have another table called "AUDIT" that contains records that are created everytime the cost price field is updated or if the User double clicks the [NEW] field displayed on the form, fBulkPartsUpdate. This is to provie a history of cost price changes. The Audit table also contains a range of other records and data relating to changes to various fields in the PARTS table so the names of some of the fields are a bit generic.
Here is my problem:
I have a form called fBulkPartsUpdate that displays some data from the PARTS table. It's data source is a query called, qryParts_00. Within the qryParts_00 query there is another query called, qryLastWSPUSD.
When you open the fBulkPartsUpdate form, you will see the following columns:
Part Name
OLD
NEW
Part #
PartID
If you "right click" the [WSP (USD)] cell ($8.40) for the first record, Alternator Stand - Cast aluminium, a popup form will be displayed that accesses the AUDIT table, finds the records that match the [PartID], in this case #238, and displays them in the popup form.
You will see displayed in the pop up form that the newest record is dated 26/10/2018 and that the "Old" is $8.40 and the "New" is $8.40 but if you look at the main form, fBulkPartsUpdate, you will see that the "OLD" field contains $9.55 which is wrong. I need the newest timestamped record values for "OLD" to be displayed in the fBulkPartsUpdate form.
If you "right click" a few of the other records you will see that some are correct and others aren't.
If you need to create a new record in the AUDIT table, simply double click any of the [NEW] fields of a record. You will need to press F5 to refresh.
I want to find the most recent record in the AUDIT table for the record and pick out the "OLD" value and insert it into the "OLD" field in the fBulkPartsUpdate form.....
I hope I've made myself clear.
Thanks for your help with this.
Cheers
Greg
Attachments
Last edited: