Getting most record record using Grouping query

Lateral

Registered User.
Local time
Today, 08:49
Joined
Aug 28, 2013
Messages
388
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
 

Attachments

Last edited:
In the Audit table, you're using the field type text for OldValue and NewValue that is wrong, you should use the field type number or the field type currency, otherwise it may give you unexpected problems.
Your main problem here is that the query qryLastWSPUSD doesn't give you what you expected to get, (but you get what you tell the query to return it is giving you the max of OldValue regardless of date and other criteria), so you need 2 queries or a query with a sub query, I've used 2 queries. The first find the max date, then the next query find the price.
Database attached.
 

Attachments

Hi JHB


Thanks for the quick reply. I spent more time on looking at my code and I think I came up with the same idea.


OldValue and NewValue are text fields because they can also contain text or numbers. This is because the Audit table is used to keep track of any changes to any field in any record in the main tables.


That's why I convert from text to Currency in the query.


I think I have it working as far as displaying the data on forms is concerned. The issue I have now is that when I try to filter the form via the Filter property in the Data tab with: [PartsSupplierWSP]>[Old Cost], I get a data Mismatched error. This is so that I can just see parts records who have ad an increase in their cost price....


Do you have any idea why this is happening?


Thanks again for your help.


Cheers
Greg
 
..
OldValue and NewValue are text fields because they can also contain text or numbers...
That is a bad idea.
..
That's why I convert from text to Currency in the query.
Sorry but that doesn't work, by me the amount is quiet different from what you say it was!
..The issue I have now is that when I try to filter the form via the Filter property in the Data tab with: [PartsSupplierWSP]>[Old Cost], I get a data Mismatched error. This is so that I can just see parts records who have ad an increase in their cost price....
Sorry no - I don't know what you're talking about, remember you know your database, I do not! :)
 
I assume that the Audit table has a date/time field. If you bring that into the query you can easily determine the most recent.
 

Users who are viewing this thread

Back
Top Bottom