Question New Database Question

[brharrii]

I'm self-taught, and somewhat new to creating Access databases, this will be my third. My needs for this database are going to be somewhat different than were my needs for the previous 2 and I was hoping to get some general advice for how to start before I dive in and make a mess.

My needs appear to be somewhat simple:

Multiple pallets are weighed each day, before and after product has been removed from them to determine Gross, Tare and net weights. I'm not sure how to setup the tables/forms since each day will represent a single date, but multiple pallet weights.

This is what a simplified example of what I imagine a form I would use would look like (Brown lettering represents fields the user would populate):

Date: 7/23/2012
Lot: B248756

Gross Wt Tare Wt Net Wt
1 100 lbs 5 lbs 95 lbs
2 115 lbs 8 lbs 107 lbs
3 102 lbs 5 lbs 97 lbs
4 50 lbs 2 lbs 48 lbs
5 51 lbs 2 lbs 49 lbs

Would it be more efficient to create a new record for each pallet weighed or for each day pallets were weighed? If 50 - 100 pallets are being weighed a day, will I run into problems with my database being over burdened by so many new records each day?

Thanks for any advice / suggestions

Bruce

 

[brharrii]

Apparently all my spaces were stripped when I made my post.... Hopefully that still makes some kind of sense ><

 

[plog]

From the data you have given you will need 2 tables:

Lots will hold hold the Lot Number and the date
Pallets will hold Lot Number, Gross Weight and Tare Weight

Net Weight will not be a field you store, but instead one you calculate in a query.

 

[brharrii]

Thanks for your response.

During a given day there will usually be more than one lot number used, I don't know if that changes anything, I'm sorry I wasn't clear on that before. Additionally the same lot number may be used multiple times.

As far as the two tables go, I can kind of see how that would work, I'm still confused on how I would create a form that would allow someone doing data entry to add multiple gross / tare weights per date.

The idea I have in my head is to idplay an entire day's pallet weights at once. Maybe this is not the most efficient way to do it though. The concern I have is how to create multiple data entry fields on my form for gross / tare weight on the same form. Would I have to create a table that had GrossWeight1, grossweight2, grossweight3, .... etc?

Thanks!

 

[plog]

Since Lots Numbers are not unique, I need modify my previous Lot table to include LotID--an autonumber field that will be the primary key. Also, Lot Number will not be in the Pallets table, but instead a numeric field to hold this new LotID from the Lots table.

Now, for data entry, you would have a form where they enter the Lot Number and the date. Then below, there would be a sub-form based on the Pallets table I described and they would enter as many weights as they wanted into that. This sub-form would link to the main form via LotID field.

Those would be all the tables and fields you would need for this.

 

[brharrii]

Thank you very much! that pushes me in the right direction, I appreciate it!