Easier way than hundreds of fields? (1 Viewer)

[itownson1]

Hello
I am currently developing a traceability database for our butchery dept.When it comes to the labelling of the product is where I hit a wall.
I have fields with all the information that appears on every label such as, Product, use by date, batch Id etc.
I have managed to connect the database to some scales and when the product is put on the scale it enters the weight of that product in the field.
All good up until now.
What I need to happen is for the database to record every weight and add them to give a rolling total weight.
I am thinking subform for weights but how do I get it to sum the total. I have seen that you put [field 1] + [field 2] etc but I could have 200 bags of one product going through the system.

Ian

 

[Mark_]

I would have to say you are thinking of this like a spreadsheet.

You have a "Product".
Each "Weight" would be in a child record to the "Product". Then you total these child records to get your total weight.

Have you looked at what Parent/Child records are?

 

[jdraw]

I helped a poster with meat cut tracing with the attached model(draft).
It may give you some ideas for tables and relationships.

Good luck.

 

[The_Doc_Man]

You need to study the subject called "Database Normalization." Your problem is that you need to build a set of tables that grow by adding ROWS (RECORDS), not by adding COLUMNS (FIELDS). For Access, tables should be tall and narrow, not short and wide.

If you make a parent/child table, then each child can contain the items you need, but then you can use what are called Aggregate queries to generate sums, counts, averages, or whatever other statistic you need.

A subform works perfectly fine in a parent/child context and is probably the type of thing you need. But your question tells me that you are not comfortable with that yet. Or at least you don't seem to be comfortable.

You might also wish to search for topics of "parent/child tables" as another reading topic.

Having [field1] + [field2] + [field3] + ... is, as you have already begun to see, not the answer. That method could lead you to have more fields than Access allows if you consider the other stuff you suggested that you had. You get stopped when you reach 255 fields on a single table - but if you do this using parent child tables to make the tables narrow but tall, you can go to thousands of records if needed.

One last reading or research assignment - "Aggregate queries."

If you read up on database normalization, aggregate queries, and parent/child table concepts, you will begin to see your answer. And your problem IS DEFINITELY answerable using Access. Not even a slight doubt on that part. But you just have to see how to look at the problem using Access methods rather thatn spreadsheet types of data layout.

 

[itownson1]

Ok thank you guys, I will carry on my research on the subject matters you suggested. I may be back!!

 

[itownson1]

I am so sounding dumber and dumber as this goes on.
Is parent/child the same as using two tables and one as a subform?
If I have 300 bags of sausages to go through on a batch, am I still not going to need 300 fields to record the weight of each bag?

 

[theDBguy]

I am so sounding dumber and dumber as this goes on.
Is parent/child the same as using two tables and one as a subform?
If I have 300 bags of sausages to go through on a batch, am I still not going to need 300 fields to record the weight of each bag?

If you think of "fields" as "columns," then you can see why we say don't do it. Rather, you should have 300 "records" or "rows" to show the weights of 300 bags. In other words, go "tall," not "wide." I think the "funniest" analogy I've seen someone used for this are "hamburger" and "hotdog."

 

[itownson1]

Ok, so every bag weighed will be a new record.
Could this cause an issue at 300 bags per day over a year?

 

[theDBguy]

Ok, so every bag weighed will be a new record.
Could this cause an issue at 300 bags per day over a year?

Hi. 300 bags x 365 days = 109,500 rows x 10 years = 1,095,000 rows. Access can handle millions of records. The only limit is the file size can't be over 2GB.

 

[itownson1]

Ok I'm glad you worked that out and not me ha ha.
If I have a subform that has BatchID linked to BatchID in the main table and get the subform to open a new record everytime the data is filled within the main form, I should be somewhere near. (Sounds good in my head)

 

[theDBguy]

Ok I'm glad you worked that out and not me ha ha.
If I have a subform that has BatchID linked to BatchID in the main table and get the subform to open a new record everytime the data is filled within the main form, I should be somewhere near. (Sounds good in my head)

Hi. Not sure I follow where you're going there but just want to mention simply linking the form and subform properly doesn't mean you'll automatically get new records put in for you in the subform. It only means you automatically get the foreign key assigned to any records you add subsequently in the subform. Cheers!

 

[The_Doc_Man]

itowson1, you are going the right direction. There is more to it than just linking the batch ID numbers - but that is DEFINITELY a step along the right path.

It sounds to me like your parent form (main form) will have the info about the batch overall, but the child form (sub-form) will have individual package info. The parent can see the child records using either aggregate queries or a new reading term, "Domain Aggregates" as a way of tracking things that need to be counted or totaled.