DLookup and calculate

[jabersold]

I have a packing table that I can have as many as 8 different materials used in packing a box. I have a table with the item listed and the charge associated with it. I have a column labeled as total material cost. How do I have the 8 different columns go look up the item and the cost associated with it and sum that line and put the total cost in the cell labeled "total material cost" for that package.

Thank you advance.

Jef

 

[ranman256]

You don't.
your table is designed wrong,you would have 3 columns,
PackID, Item,Cost


then sum the vertical Cost of all items in the box.
similar to Shopping cart.

 

[jabersold]

You don't.
your table is designed wrong,you would have 3 columns,
PackID, Item,Cost


then sum the vertical Cost of all items in the box.
similar to Shopping cart.

Thank you for getting back. The information is scanned into a database on the web via barcode reader (for the different components) for each package. If we pack 300 packages a day I can pull them down in a excell file that looks like this. PkID, Item1, Item2, etc... through Item8. I want to then append it to a access table where it is more manageable. Then be able to pull the cost of materials per pack for reporting.

Thanks

 

[Joe4]

Ranman's advice is spot on. Your proposed database design is not normalized, and will cause you many headaches. It makes seemingly "simple" tasks much harder than they should be.

For example, let's say that you want to search all packages that shipped for a certain item. In your current proposed design, you would need to search 8 different fields! Under the proposal Ranman suggested, you would only need to search 1.

Also, the SUM functionality in Access works on a single column across multiple records (not multiple fields in a single record). So in your proposed methodology, you would actually need to create a calculated field that would be something like: Cost1 + Cost2 + Cost3 ... + Cost8
In Ranman's suggested structure, you would simply Group the records on the Package ID and sum the Cost field.

Lastly, your proposed method is not very flexible. Let's say at some point down the road, you need to add more fields (i.e a 9th and a 10th). Under your proposed structure, you would first need to edit the structure of your table to add two more fields. Then you would need to update every query, form, and report (and possibly VBA code) for these additions. Under Ranman's suggestion, there would be no need to do that. You are simply adding more records, and do not need to alter any object structures at all.

A well-designed database should not need that many alterations every time something as simple as an additional item can be included in a package.

The key to a well-designed database is setting up your data tables in a normalized fashion. Here is a good article on that topic: https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics
Note the example of the "unnormalized table" in that resembles the structure you had proposed.

 

[jabersold]

Thank you everyone,

I am in agreement that is why I'm asking for guidance on correcting the materials I have been given to work with. I can work on the structure but still need assistance on the cost lookup from the item table.

Thank you everyone

 

[Joe4]

but still need assistance on the cost lookup from the item table.

So your Item table should have at least the following two fields:
- ItemID
- ItemCost

And then your main data table should also have an ItemID field in it, for the selected Item for that record.
Then it should just simply be a matter of linking the two tables in a query on the ItemID field, and returning all the fields you want from your main table, and the ItemCost field from your Item table.

 

[xenou]

Note that in terms of the current structure the total cost is:

update table1 set [Total Material Cost] = col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8

This is not the whole story if any of these fields can be NULL since that would reduce the entire sum to NULL. In that case you'd have to coalesce the NULL values to zeros:

update table1 set [Total Material Cost] = nz(col1, 0) + nz(col2, 0) + nz(col3, 0) + nz(col4, 0) + nz(col5, 0) + nz(col6, 0) + nz(col7, 0) + nz(col8, 0)

Since this is awkward sql to work with, you can see the advantage of better database structure. Also if a ninth field were to be added you would have to fix the query to include the new field, but with a standard relational database structure your new sub-cost datapoint would be included automatically.