Adding values in separate columns dependent on text in another column?

[Degs29]

I'm trying to make a database to track inventory or several items. Basically, I have four tables:



1) RawMaterialList - includes a list of all raw materials.
2) PartList - includes a list of all finished product using said raw materials.
3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials.
4) ShipmentRecord - contains details of daily shipments.

Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.

Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:

1) Item - a list of of raw materials from table RawMaterialList
2) Description - also dependent on info entered into table RawMaterialList
3) Quantity

But, I have 12 of these instances. Aka, Item1, Item2...Item12; Description1, Description2...Description12; Quantity1, Quantity2...Quantity12.

My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2. Help?


Example:

1st Entry:

May 13

Item1 = PartA Description1 = PartA's description Quantity1 = 15
Item2 = PartB Description2 = PartB's description Quantity2 = 20

2nd Entry:

May 14

Item1 = PartB Description1 = PartB's description Quantity1 = 30

How do I get it to add up Part B to get 50pcs?

[pbaldy]

Is it too late to correct the design flaw?

Fundamentals of Relational Database Design -- r937.com

Multiple items should be multiple records, not fields. Then your query is a simple one.

[Degs29]

Is it too late to correct the design flaw?

Fundamentals of Relational Database Design -- r937.com

Multiple items should be multiple records, not fields. Then your query is a simple one.

So each line item of a single shipment needs a separate record? Seems redundant. The employee would need to re-enter the date, supplier name, and order number for however many line items there are on the one packing slip. Is there no way to do this by recording multiple line items on a single record?

[Degs29]

My apologies, I totally missed that link. I will be checking into that when I get a chance. Maybe there IS a way I can better handle this. Will get back to you! Thanks!

[pbaldy]

No problem. Your situation is like the tblOrder and tblOrderDetails discussed in the link, which is the normalized design. Your repeating fields become records in the detail table.

[Degs29]

That linked proved useful, but I still have a problem. Upon data entry, I need the user to be able to fill out the form roughly as follows for each packing slip we receive:

Supplier _________
Date __________
Order Number __________
__________________________________________________ __________________________________________________ ______________________

Item 1 _________ Description (This is filled in automatically based on previous field) Quantity ___________
Item 2 _________ Description (This is filled in automatically based on previous field) Quantity ___________
Item 3 _________ Description (This is filled in automatically based on previous field) Quantity ___________
.
.
.

Supplier, date, and order number would apply to all three (in this case) of the line items that follow. Would I need to create a new record for each line item, and re-enter the supplier, date, and order number for each one? Or is there a way to do it so you enter the supplier, date, and order number once, then enter the rest of the info on multiple records? How would I enter the next packing slip?

Thanks for the patience. I imagine my ignorance makes this a little difficult.

[rzw0wr]

Pardon my intrusion.
Yes there is a way.
And we understand being new to Access.

I would make a table for Items.
A table for Orders.
Add a relationship from Orders (PK) to Items (FK).

It does sound like your tables need some normalizing work per Paul's suggestion.
Normalization of tables can not be over stressed in database design.
Dale

[pbaldy]

As described in the link, the main table (tblOrders in the link) would have the supplier/date/order number. The details table would contain the items. It's typically done with a form/subform. This may help with the autofill:

BaldyWeb - Autofill

[Degs29]

Thanks guys. I really appreciate it. I'm out until next Tuesday, but I'll be diving into it then!

[Degs29]

I have now normalized my tables and used a subform in my form, following that up with a query to tally totals of each item. Working flawlessly.

Thanks again for the help!

[pbaldy]

No problemo!