Multiple Items with Qty into a list from a single record.

[SubmittedByMail]

I'm am trying to take a single record that has multiple items with quantities and turn it into a material list. The ultimate purpose would be to take all items and quantities from all the records and turn the items into a material list with prices that could be adjusted from the the query.

[ranman256]

youd need to give us an example. we cant guess.

[orange]

Your objective is to move
FROM- single record that has multiple items with quantities
TO -multiple records that has single item with quantity

As ranman says -- some sample data would help focus the advice.

[ArviLaanemets]

As start point you need a table of items, like:
tblItems: ItemID, [ItemCode], ItemType, ItemName, ..., ItemStatus;
(where ItemID is autonumeric Primary Key, ItemCode is an optional indexed code field you can have when you feel you need it, ItemType will probably classify items as purchased, or produced, etc., ItemStatus will classify items as normal, or obsolete, etc.)

Simplest step from there will be the next table like:
tblProductComponents: ProductComponentID, ProductID, ComponentID, ComponentQty;
(ProductID and ComponentID are foreign keys linked to ItemID in tblItems. This table will list all components needed to produce a single unit of product.)
This allows you to calculate quantities of all components to produce a single unit of product, or same quantities of components to produce any amount of products. And when you have item prices registered either in tblItems (in case those prices never change), or in separate tblItemPrices table where the history of all prices for items along with date the price was due, is registered, then you can also get the cost of all components for single product or any amount of them.

More advanced step will be using a BOM list instead of simple components list. A BOM list is a table, where the all components needed to produce the product are listed in structured way - a row for every component and its quantity used in specific step of production process. Often this means you have to register sub-components produced in process and used in next production steps later, as items too - probably adding a specific ItemType for them (assumed those sub-components are never sold - when they are, you better mark them as produced items).