Creating Query with selective field (1 Viewer)

wesmcf89

New member
Local time
Today, 18:39
Joined
Dec 7, 2017
Messages
3
Hi All,

First time poster, relatively new access user.

Currently at my job we receive a handwritten daily inventory sheet with about 60 different tank levels on it. We have another sheet that we use to handwrite the material movements from one tank to another. I'm attempting to digitize this with an access database.

I have two form and two tables, one for the inventory sheet and one for the transfers. The inventory sheet has every tank as a field.

I would like to be able to create a query that will link material transfers (which will be posted in same field), to the specific tank the transfer came to/from.

The problem i seem to be running into is that since all of my tanks have their own field in one table, I do not know how or if its possible to make a query to select a particular field (ie tank). I've watched youtube videos and understand how to add criteria to a field, but can you have a user input the desired field for a query?

I would ultimately like to query the particular field from the inventory table and then the items relating to that field from the other table.

Thank you for your help!
 

plog

Banishment Pending
Local time
Today, 17:39
Joined
May 11, 2011
Messages
11,612
The inventory sheet has every tank as a field.

That's not how databases work. Values are stored in fields, not in field names. So you need a new field in your table to determine what tank you are working with.

Read up on data normalization (https://en.wikipedia.org/wiki/Database_normalization). You set up your tables properly and what you want to do becomes trivial.
 

Solo712

Registered User.
Local time
Today, 18:39
Joined
Oct 19, 2012
Messages
828
Hi All,

First time poster, relatively new access user.

Currently at my job we receive a handwritten daily inventory sheet with about 60 different tank levels on it. We have another sheet that we use to handwrite the material movements from one tank to another. I'm attempting to digitize this with an access database.

I have two form and two tables, one for the inventory sheet and one for the transfers. The inventory sheet has every tank as a field.

I would like to be able to create a query that will link material transfers (which will be posted in same field), to the specific tank the transfer came to/from.

The problem i seem to be running into is that since all of my tanks have their own field in one table, I do not know how or if its possible to make a query to select a particular field (ie tank). I've watched youtube videos and understand how to add criteria to a field, but can you have a user input the desired field for a query?

I would ultimately like to query the particular field from the inventory table and then the items relating to that field from the other table.

Thank you for your help!

Hi, to begin with, you would be much better off to redesign your inventory table to have each tank as a separate row in the table, and not as a column (field).

Best,
Jiri
 

wesmcf89

New member
Local time
Today, 18:39
Joined
Dec 7, 2017
Messages
3
Updated: I believe i understand what you guys mean now...If the operator enters every tank level manually, then maybe i can pull the info from that day into a report for the finished product and query based on the date and tank number?

My next question will be, can i have a combo box that has approx. 60 selections (tanks) to pick from? or do i need to rely on him to enter the tank number?

Wesley

Double update: Figured it out (for now)...thank you for the help!!

Wesley
 

Attachments

  • top of inventory table.PNG
    top of inventory table.PNG
    14.9 KB · Views: 50
  • access table.PNG
    access table.PNG
    7.8 KB · Views: 51
Last edited:

Solo712

Registered User.
Local time
Today, 18:39
Joined
Oct 19, 2012
Messages
828
Thank you all for the information so far!

In the manual form, the values for each tank are read every day. An example of my table setup

FIELDS - ID, Date, Reporter, T101, T102, etc. etc.

So for today the values would be

1, 12/8/17, JF, 127000, 31500, 52500

and on and on for each subsequent day.

Jiri, you mentioned how i should have my tanks set up in rows and not columns. What i'm envisioning is that this would mean each value would have its own row (with date, tank number, value in tank). Because the tanks never change, is there a way to enter all the values and then have them populate the table in separate rows?

I've attached a picture of my table and part of my input form if that would help make it clearer.

Thank you!

Yes, I would recommend creating a table with the tanks as rows, e.g.:

ID,
Tank Name/Desc
Opening Balance
Date (of Opening Balance)

Your second table then would register all the transactions against the tanks, i.e. the date, transaction description amount added or consumed. You could get the current balance (or one to a given date) for each tank by simply summing up the transactions since the opening day and adding (subtracting) them from the opening balance. What I am saying is that you would not be storing "new values" in the tank every day, but transactions affecting the stored levels. So, yes, you can have a list of transactions that you add to the second table every day. The querying and reporting in such a setup would be very simple, as would be adding new tanks or taking tanks out of service.

Best,
Jiri

PS.: see you have made your decision...good luck!
 

wesmcf89

New member
Local time
Today, 18:39
Joined
Dec 7, 2017
Messages
3
The final portion of this would be outputting the data to a inventory sheet we're used to seeing. The next step would be creating a query based on a input date, to generate the values where i want to place them in a report?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:39
Joined
Feb 19, 2002
Messages
42,976
Welcome,
You need a table to hold the tanks. One row for each tank. This table also needs some special "tanks". You might call one "Shrinkage" and another "Purchased" because you will need to do inventory adjustments and the "loss" needs to go to Shrinkage and new material needs to come from Purchased or whatever you want to call them.

Then you need a product table so you know what is in the tanks.

And finally, you need a transaction table to record movement from one "tank" to another.

If you keep a running balance on the tank records, the from tank in the transaction would subtract from that tank and add to the other tank. You can rebuild the balance from scratch by using a query that sums all the from amounts for a tank plus all the to amounts. to get the net. In theory, this will exactly equal what you think is currently in each tank.
 

Users who are viewing this thread

Top Bottom