Having Problems Connecting All My Data

[xcrx]

I am working on a database that calculates inventory. It has to be able to calculate the inventory of single parts based on sub-assemblies and assemblies. For example, We received (10) partA and (20) PartB. We then Build (2) Sub-AssemblyA which uses (2) partA and (1) PartB per Assembly. The inventory would read (6) partA (18) PartB (2) Sub-AssemblyA. Then (1) AssemblyA is made which uses (2) Sub-AssemblyA. The Inventory would read (6) partA (18) PartB (0) Sub-AssemblyA (1) AssemblyA. All of that is fairly straight forward.

I use 3 main tables tblParts, TblSubAssemblies, TblAssemblies. These tables store the names and other important information. I then use 3 more tables to link things together, tblPartsinSubassemblies, TblSubassembliesinAssemblies and TblSubAssembliesinSubAssemblies.

I use a query to figure what parts go into a subassembly that is part of another subassembly. The problem I am having is getting those parts to be included in with the parts that are part of the main subassembly but not in any other subassemblies.

I am not sure what other data might be helpful. I am sorry if this is confusing.

Thanks
Ryan

 

[PNGBill]

Maybe post a jpg of your table relationships.

 

[xcrx]

I attached jpgs showing my tables and how the data relates and my current queries.

I hope this clears some of the confusion up.

The problem I am having is that after I get part totals from qrysubinsub and qrypartinsub, I can't get them to combine together to show me all the parts that are actually in the master subassembly.

Thanks
Ryan

 

[xcrx]

I fixed my problem using simple IIF statements. I thought I would post it so if anyone else ever has the same problem they know how to fix it.

Also if this is a bad fix please let me know.

Ryan