Entering Data in a Query (1 Viewer)

[NSAMSA]

Hi All:

I have what should be a simple issue. I'm making a database for a department that has 20 tanks to put product in. I have a table called tblBatchRun in which data is filled in for each tank including status, date etc.

On my opening form page, I want to have pictures of each tank, and within the picture a form showing the current status, (based on the last run).

In order to do this, I figured the best solution was to have a query that looks up the maxID for each tank and shows that data in the adjoining form. The best way to get this data is to do a Totals query in which I group by the tank numbers and maxID to give me the records I want. Unfortunately, you cannot do data entry in a Totals query. How do I get this information in a way that allows me to make data entry enabled forms?

Thank you for any help you can give.

Regards

 

[sneuberg]

I think if you save your aggregate query and then join it to the table you want to edit you might be able to do this. You would need to join it on all of the field of the aggregate query.

If you can upload your database or a stripped down copy I'll see if I get something working for you.

 

[NSAMSA]

View attachment SampleDb.accdb

I've attached a sample Db with just the two tables concerned. What I want to be able to do is create a form for each tank to attach to the opening Form so that the user can change the status or change the data for the current run of each. As you can see when opening the query, I cannot update with a Totals query.

Per your suggestion, I made a query where I joined the ID for the query and the ID for the table and used the table status. This also failed to allow me to update.

Thank you for any help you can provide. It's much appreciated.

 

[sneuberg]

The database you uploaded has one table and one query. Shouldn't there be two tables? One for the tanks and one batch runs?

 

[NSAMSA]

Currently, I only have the Tanks as a Lookup Wizard, (Enter your own), column. However, it would probably be best if I did make them a separate table to track the different sizes. I will go ahead and do that... Especially if it helps with this issue.

 

[NSAMSA]

I've made the tanks into a separate table per your request. View attachment SampleDb.accdb

 

[sneuberg]

I added a relationship between the tblTanks and tblBatchRun table in the attached database. Please look in relationships and see if you think it is right. Then I let Access create a form base on the tblTanks. If you open frmTanks you see it put tblTanks in the main form and tblBatchRun in a subform. This is normally how you set up a form for a one to many relationship. I think you can see that the picture of the tank would go in the main form. Now the question is what other aggregate information do you want in the main form. If you want it to show the current status just let me know what determines that. Is it a date in the tblBatchRun?

 

[NSAMSA]

The issue with this is that it shows all records for the tank. The purpose of the query is to find the last record for the respective tank.

Essentially it hopefully will work like this:

I make a new batch in tank 2, let's say with an ID of 8
There will be a block representing tank two on the front page with a form in it, and one of the pieces of data will be "status". When the tank is complete the employee will change that status to open to signify that the tank is available.

The next day, a new batch is made in tank 2 with an ID of 15.
The form on the opening tank will show information for ID 15, as that is the most recent batch for the tank, showing the status of that ID in the form and that ID alone. This way, we can know the current status of the tank. The other options like TA etc. can be updated for the current batch from the opening page as well.

That is why my original query looks only for the max(ID) since the number only goes up and whichever ID has the highest number for a tank is that tank's most current, and/or previous run.

 

[sneuberg]

You could base the subform on a query of the tblBatchRun that was sorted in descending order on the Batch ID. The the most current status would be on top. If you don't want to see the other records you could just resized the subform so that only one record fits.

I was trying to demonstrate this but the missing tables that are in the lookups are driving me nuts. In your spare time you might want to read
The Evils of Lookup Fields in Tables

 

[sneuberg]

Here's an editable query that produces the records with highest ID for each tank. This is editable because rather than uses a join with an aggregate query it uses a subquery with a WHERE IN clause. The subquery will make this slow so my suggestion in my previous post is my recommended approach.

SELECT tblBatchRun.*
FROM tblBatchRun
WHERE (((tblBatchRun.ID) In (SELECT Max(tblBatchRun.ID) AS MaxOfID
FROM tblBatchRun
GROUP BY tblBatchRun.TankNo)))
ORDER BY tblBatchRun.TankNo;

 

[NSAMSA]

I ended up solving the issue by making a delete and append query to update a table with the IDs I want. That way, I can link the original table to the appended table and make editable forms.

Thank you for your help. It was much appreciated.