Wrong previous records retrieved

[Uni03]

Hi - I have a database of 26 machines. They are identified by a unique machine number (101-126). On a form, I select a machine number from a combo box, and enter the current meter reading for that machine. I am using DMax to get the previous meter reading for that particular machine to use to determine the volume of work done. So, when I select a machine number, the previous meter field gets autopopulated on the form. The first machine 101 works, but when I do the next few records, 102, 103, 104, previous meter numbers appear, but from weeks back. This doesn't happen to all the machines. After 104, the next several previous meters for other machine numbers are correct, but then it occurs again. Most of the machines do get back the correct previous meter count, but I don't see a pattern as to why this is happening. Appreciate any help on this.

Thanks.

 

[jzwp22]

Could you please provide a little more detail on your table structure and the syntax of the DMax() function you used?

Out of curiosity, how do you have the form setup? Do you have code executing when you select a machine # from the combo box?

 

[Uni03]

Thanks for responding jzwp22 - The main table, called MeterCounts, has id (autonumber generated by Acces), machine#, serial#, machine type, meterdate, meter count. We have 26 machines. The combobox runs off another table, called Machine, that contains the machine, Serial#, type. This is to auto fill those fields on the form after the machine # is selected from the combo box, called machineinfo. The combo box is setup with 3 columns for these fields. The code for this is in the AfterUpdate event of the combo box (machineinfo) : Me.Machine = Me! [machineinfo].Column (1), and the same for the other 2 fields, column 2 and 3. As stated previously, the correct previous records for most of the machines come back, but the others do not show the last record.

 

[spikepl]

You also need to show the code used to pick-up the values

 

[jzwp22]

First and most importantly, you have a table structure issue. You should not have the same information in both tables. Since a machine can have many meter readings, you have a one-to-many relationship. Your tables should be structured more like this

tblMachines
-pkMachineID primary key, autonumber
-MachineNo
-SerialNo
-MachineType


tblMachineMeterReadings
-pkMachineMeterID primary key, autonumber
-fkMachineID foreign key to tblMachines
-meterdate
-metercount

As some general recommendations, it is best not to have spaces or special characters in your table and field names. In fact, the # sign has special significance in Access.

One-to-many relationships are best handled with a form/subform design with the main form being bound to tblMachines and the subform being bound to tblMachineMeterReadings

Additionally, if you have many machine types and a machine type can apply to many different machines (one-to-many relationship), it would be best to put all the machine types into a table and then reference that back to tblMachines

tblMachineTypes
-pkMachineTypeID primary key, autonumber
-txtMachineType


With the above change, we need to modify tblMachines as follows

tblMachines
-pkMachineID primary key, autonumber
-MachineNo
-SerialNo
-fkMachineTypeID foreign key to tblMachineTypes

 

[Uni03]

This is the first time that I have worked with Microsoft Access, so please bear with me. This application was originally created years ago with Excel, and has been a real maintenance nightmare, so Access looks like a better tool to use. As I've worked with this, I catch myself trying to set it up in Access like it was setup in Excel, and that confuses things. I do understand the naming conventions for the table and field names, I was just abbreviating to keep the post short. There are only 2 machine types, and actually we don't really have to have that, so I will leave it out. I will work with your suggestion. Other people do the data entry for this, and I want to keep the form simple-they will only need to select the machine number from the combo box and enter the meter reading. Thank you again.

 

[jzwp22]

I would agree that Access is the better tool to use, but making sure it is setup properly (especially the table structure) will save you a lot of future headaches. If you want, you can zip and post your database (with any sensitive data removed) and we can take a look at it to see what is going on with your form and offer suggestions on how to get it set up correctly.

 

[Uni03]

Here is the database. The table Machines has the machine info, and the other table MeterCounts is the main table which is where the daily meter count information goes. There is only 1 form
View attachment EptTracker.zip

 

[jzwp22]

There are 2 meter count tables in your database metercounts and metercounts2 as well as two forms Meter Counts and MeterCounts (with no space). Both of the forms are based on the MeterCounts table. What is the difference between the two meter count tables?

 

[jzwp22]

Is there a relationship between the meter reading values and the begin and end cycle values?

 

[jzwp22]

Taking a quick look at your forms, you definitely have a problem. If you select a machine from the combo box while you are viewing the record of another machine,you overwrite the machine number. Now you have data that pertains to the wrong machine in the record, so your data integrity is out the window. What should happen is when you select a machine from the combo box, Access should move to that record in the table. You definitely need a form/subform set up. It can be done to make it look similar to your current form. So now the big question, is the data in this database any good?

 

[Uni03]

Forget MeterCounts2, the table, and MeterCounts, the form - I thought I deleted these. Table is MeterCounts and the form is Meter Counts. The Begin and End cycles are numbers used in the the math in the Excel version to determine the difference between the current and last meter reading = the amount of work done on the machine during that shift. Also, there are reports run off of this information - for example, the total meter count for a machine for an entire month. The meter count for the first of the month subtracted from the meter count for the last day of the month. The meters on the machines "rollover" back to the beginning after they reach 99,999,999. So a rolled over meter number can't be subtracted from the previous meter because it would be a negative value. The end and beginning numbers continually get increased and the Excel program does the math with thee. They normally would not be on the form, I just have them there while doing this so I can check my information. The form only needs to show the machine information and meter count.

 

[Uni03]

This is test data a copy of the real data file. Actually, after I fille dout the form, the information didn't get added to the table unless I refreshed the table.
Then after adding the DMax instruction to get the last meter reading (the End Meter Count from the previous record) to populate, we have the current issue of getting the wrong previous record coming up for some of the machines.

 

[jzwp22]

I've restructured the database, moved the data into the new structure using a series of append queries and recreated the form using a form/subform design. With respect to the append queries, they are just for reference because if you run them again you will add duplicate records to the applicable tables, so I would strongly suggest not running them.

In the header of the main form, I added a combo box where you select the machine and then code executes after you select a specific machine. That code moves to that particular record and you would add you meter data.

I'm still not real clear on the rollover fields and how they relate to the meter count, but from what I see, you do not need both rollover fields since the end rollover value of one day becomes the beginning rollover value of the next, so why store the same data twice? I opted to just have the ending rollover value much like the ending meter data.

 

[Uni03]

Great. I will look this over on Monday at work because I don't have Access on my home computer. The Begin and End Rollover counts were used by Excel to calculate volume and in creating reports. I may be able to do reporting on the meter numbers without those fields but I'm more concerned first with getting the data entry form working so we can use this instead of the old Excel application.
Thank You again.

 

[jzwp22]

You're welcome. Please do not hesitate to post back if you have questions after you have had a chance to review the database.

 

[Uni03]

Hi jzwp22 - Thanks again for working this. I need a little explanation of how this will work. I did go in to try it out but I don't understand what is going on behind the scenes. I clicked the new record arrow at the bottom of the form, and this cleared the form. I entered 101 in the combo box, which prefilled the machine info in the top boxes, but the subform filled in with data from the first record from the table. I entered some data, but do I hit the new record arrow when done, or what? The infor did not go in the table, it was still at the last record. So, basically, I'm unclear on how to use the form.

 

[Uni03]

Hi - jzwp22 - thanks again for working this. I'm a little awkward navigating through the revised form. The main form shows 1 of 26 records (26 machines) and the attached subform shows 1 of 74 records? The separate subform shows 1 of 1948 records, all the records on the main table.I clicked the new arrow key on main form, it cleared the fields, I entered 101 which prefilled the machine fields at top, and the sub form filled with info from first record. I entered info here, clicked new record arrow, but nothing on table.

 

[jzwp22]

The subform shows the records related to the record in the main form; in other words, the subform shows the meter readings for the machine shown in the main form. If you add a record to the subform, it will be tied to the machine shown in the main form; this is where you would add new meter data. If you add a record to the main form, you will be adding a record for a new machine.

I entered 101 which prefilled the machine fields at top, and the sub form filled with info from first record. I entered info here, clicked new record arrow, but nothing on table

If you select 101 from the combo box in the main form header, Access moves you to that record in the main form, you would then click the * record navigation button in the SUBFORM to add a new meter reading for machine 101.

If you click the * record navigation button in the main form, you are presented with a blank record to add a new Machine.

 

[Uni03]

I worked with this yesterday afternoon and it looks like the previous record issue that happened before is occurring, with the Last Meter Reading. When you first open the form, machine 101 info is displayed at the top, and the Last Meter Reading is correct. After filling out meter reading info for that record, I select machine 102, and the info for that populates, but the Last Meter Reading is not the previous record for machine 102, but the value in the 7/8/11 record for machine 102. This happens with other machines like before, with values coming from different dates, and not the previous record.