Material List from a Table with Multiple Items and Quantities in a Single Record

[SubmittedByMail]

Hello. I am trying to create a query that will take records from a table to create a material list.



This is a simple example of my "assemblies" table:

qty	type	Aqty	A	Bqty	B	Cqty	C	Dqty	D
2	120v20a Quad Receptacle	20	wire	2	receptacle	1	jbox	1	device cover
3	4gang switch	60	wire	4	switch	1	jbox	1	device cover

This is how i imagine the "material list" query:

material qty	material item	price
220	wire	.55
4	receptacle	.85
12	switch	.75
2	jbox	1.25
2	device cover	.90

The "material item" and "price" come from the "supplies" table. I would like to be able to update the "price" in the "material list" query and have it update in the "supplies" table automatically.


I had a couple yrs of experience with Access about 20 yrs ago, so I'm rusty. I'm using Access 2010.

Thanks.

[June7]

Your data is not normalized. To rearrange into the normalized structure you want as output would use a UNION query then use that query as source for an aggregate query. Where does price data come from?

[SubmittedByMail]

Your data is not normalized. To rearrange into the normalized structure you want as output would use a UNION query then use that query as source for an aggregate query. Where does price data come from?

"price" comes from a separate table, "supplies", that i update manually.

[June7]

What do you mean by "update in the 'supplies' table"? If price comes from Supplies table into the query, what would you update? Saving calculated data is usually a bad idea. However, if prices change and you need to maintain history, then prices in effect at time of assembly needs to be saved.

Again, data structure of Assemblies is not normalized. You show only 4 assembly elements - why only 4, what if an assembly needs 5 or 50?

[SubmittedByMail]

What do you mean by "update in the 'supplies' table"? If price comes from Supplies table into the query, what would you update? Saving calculated data is usually a bad idea. However, if prices change and you need to maintain history, then prices in effect at time of assembly needs to be saved.

Again, data structure of Assemblies is not normalized. You show only 4 assembly elements - why only 4, what if an assembly needs 5 or 50?

I designed my input form to have as many as 60 "material" entries per assembly. the form links to a table where the data is stored. I need the material list for 2 reasons. First of course is to submit an order to a supplier. The second reason i need the material list is to verify material prices before i submit an estimate. There are hundreds of different pieces of materials, so it would be efficient to check only the used pieces of materials as i am preparing a quote. If i can save the updated data as i am going through this process, it will keep prices close to accurate for when i start the next quote.

[Micron]

Agree with June7. If your 1st post resembles your table, it is incorrectly designed. IMO, any db like this (especially a BOM, which this appears to be) makes normalization that much more important, not only for summarizing data, but presentation. You'd probably be better off with a materials list for the parent item (assembly, job, whatever but job hereinafter) that applies to that job only (with the ability to append). Your design requires many fields which are not needed for every job so lot's of unnecessary overhead and presentation. Worse, you have to redesign every dependent query/form/report/code that is affected by the addition of another job type or fields that comprise the job sub details. Or you pick some arbitrary number and hope you never exceed it (that's why you have 60?). That just adds complexity for no gain.

You really should explore normalization because db's are not spreadsheets, and that's the mind set you're using. Things will only get worse for you. As mentioned, some db's can get away with this, but I'd strongly suggest to not continue your path for a BOM type of db. Perhaps study and comprehend these or find ones you like better.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[June7]

You want to allow user to modify price and then save that revised value into Supplies? That should be fairly simple code (macro or VBA).

Are you saving prices into each Assembly quote record?

Again, strongly recommend normalizing data.

[SubmittedByMail]

I think my question is how do i normalize this data into a table or query? The primary function of the database is to build and edit assemblies with the option to create templates for those assemblies. This function has been accomplished. I can calculate each assembly cost from the form itself. I am now attempting to keep material cost accurate and feed the data into a material list and quote for the customer. I would also like to build queries and reports to track progress and payment of the project. I would like to keep these processes self contained in the database so each project can have a data base.

[June7]

Already advised to use a UNION query to rearrange fields to normalized structure. However, UNION query has a limit of 50 SELECT lines, so data structure with 60 fields really complicates. Possibly use two UNION queries of 30 SELECT lines each then a third UNION to combine the first two. I've never tried that.

Data doesn't show an assembly ID field but I presume there must be unique identifier field.

UNION would be like:

SELECT ID, qty, type, Aqty AS Quantity, A AS Material FROM Assemblies
UNION SELECT ID, qty, type, Bqty, B FROM Assemblies
UNION SELECT ID, qty, type, Cqty, C FROM Assemblies
UINON SELECT ID, qty, type, Dqty, D FROM Assemblies
etc.;

Which is more like what the table should be in the first place.
If the raw data table were already normalized, UNION would not be necessary.

Then, join the final UNION to Supplies to pull in price.