Wrong previous records retrieved

[jzwp22]

When I reworked your database, I copied the DMax() function from your old form

=DMax("dpMeterCount","tblMachineMeters","fkMachineID= " & [Forms]![frmMachines]![pkMachineID])

The DMax() function as above finds the maximum value of dpMeterCount irrespective of the date on which it is entered. What you really want to do is find the maximum date for each meter reading pertaining to a particular machine and then whatever the meter reading is on that date.

To do this you will need some queries. First a query to find the max date for each machine in the meter table:

query name: qryMaxDateForEachMachine
SELECT tblMachineMeters.fkMachineID, Max(tblMachineMeters.dteMeter) AS MaxOfdteMeter
FROM tblMachineMeters
GROUP BY tblMachineMeters.fkMachineID;

Then create another query based on the above and join that back to the meter table via both the machineID and date fields

query name:qryMostRecentMeterReading
SELECT qryMaxDateForEachMachine.fkMachineID, qryMaxDateForEachMachine.MaxOfdteMeter, tblMachineMeters.dpMeterCount
FROM tblMachineMeters INNER JOIN qryMaxDateForEachMachine ON (tblMachineMeters.fkMachineID = qryMaxDateForEachMachine.fkMachineID) AND (tblMachineMeters.dteMeter = qryMaxDateForEachMachine.MaxOfdteMeter)
ORDER BY qryMaxDateForEachMachine.fkMachineID;


You will then need to reference the above query in the control on your form. Since the query only give the most recent, you can use the DLookup() function instead of the DMax() function.

Attached is the modified database.

 

[Uni03]

Thanks for clearing that up. The data entry for this is done daily after each shift, which means the previous meter reading when entering the DAY shift info will be from the EVE meter of the previous date, but when entering the EVE shift info the previous shift meter reading for a particular machine will be from the DAY shift on the same day. So I don't think we can use the date for a lookup. My original difficulty when I started this was in using DMax and DLookup to get the previous record using the autonumber id (what you now are calling fkMachineID in tblMachineMeters) and -1, for the machine I was updating. It should get the highest record id number for a particular machine. I have looked through many posts trying to get the syntax for this type of scenario, but I have only seen getting a previous record, not having to deal with a set of records, like updating 26 items at once. All your input on this has been very helpful in my getting a better understanding of using Access, and when this is finally working I can start tackling other projects.

 

[jzwp22]

One option is to include the time along with the date. Another option is to incorporate the shift + date. From a query point of view, just adding the time component would be easier.

 

[Uni03]

I want to try using Dlookup and DMax in the Control Source for the Last Meter Reading field on the form. I set it up as follows:
=DLookUp("[dpMeterCount]","tblMachineMeters","[fkMachineID]= " & [Forms]![frmMachines]![pkMachineID] And [pkMachineMeterID]=#" & DMax("[dpMeterCount]","tblMachineMeters","[fkMachineID]= " &[Forms]![frmMachines]![pkMachineID]))
The field shows a "#NAME?" error. After entering the code I got an "Invalid Date field in syntax" error.
Using DMax I have to get the previous record for the machine by getting the highest id number of the record for the machine, or as you pointed out previously I will get the highest meter number for the machine. The command makes sense when looking at other examples using it. I'm sure it's some basic little thing I am not seeing.

 

[jzwp22]

It would be better to use a query to do basically what you want to do and then use the DLookup() to pull the value rather than trying to nest two domain aggregate functions. Please see the attached for the modification

 

[Uni03]

Great! I tested the form and the correct previous meters are showing up for each machine. I also took some time to read up on queries so this makes sense to me now. It works great for this application. I thought a one line command was easier but I just have to learn how to create queries. I will spend some studying this because I will have eventually have to set up reports. Thanks you again.

 

[jzwp22]

You're welcome. Good luck as you move forward on your project.

 

[Uni03]

I tried for a week to figure this out but am stuck. If you can recall the Last Meter Count value, which was gotten using DLookup and referencing the qryMostRecentMeterUsingPK query. The shift volume is arrived at by subtracting this last meter, called lstShiftMeter from dpMeterCount. This works fine. But I want a running total of this at bottom. I get ERROR displaying in field. Using SUM, I know I can't use the name of the calculated control lstShiftMeter, but have to type out the entire DLookUP line that gets the lstShiftMeter. So, as I understand it, the sum calculation would be =SUM([dpMeterCount]-[DLookUP... . This is not working. Sorry to haunt you with this again, but there is no point in my spinning my wheels over this.
Thanks.

 

[jzwp22]

But I want a running total of this at bottom

At the bottom of what, the main form or the subform (it makes a difference)?

Since the Dlookup() only returns the last value and the dpMeterCount only shows the current value, the form cannot show a running value of the difference just by using these two values since they only correspond to 1 record each.

I think you need another query that finds the difference between the current and previous values and then use the DSum() function on that query.

 

[Uni03]

The total is to be at the bottom of the subform.
I will work with your suggestion.
Thanks.

 

[jzwp22]

You might want to take a look at this site which shows how to properly reference controls (properties of the controls) on forms and subforms

 

[Uni03]

This will be very helpful. Thanks.