444,153 Members | 2,581 Online
Need help? Post your question and get tips & solutions from a community of 444,153 IT Pros & Developers. It's quick & easy.

# Multiply quantities

 P: 62 Hello. I am using MS Access 2003 on Windows XP. My database is concerned with keeping track of assemblies, parts and fasteners. I have a table which relates assemblies and parts, and a second one which relates parts and fasteners. (One chair assembly for example has as parts: 1 seat, 4 legs. Each leg requires 2 screws, so the assembly requires 8 screws.) I have a form which allows users to copy existing assemblies, then modify the parts list to create a new assembly. Is it possible to create a query or table which totals each type of fastener automatically? For instance, if the user decides to only have 3 legs on his chair, I would like the query to show that the assembly requires 6 screws. As always, you guys are a God-send. Thanks! nickvans Sep 11 '07 #1
4 Replies

 100+ P: 556 you could do this in the control source of the chairs: Expand|Select|Wrap|Line Numbers =IIf([Chairs]=3,6,IIf([chairs]=4,8)) and do this to all the rest, just follow how the code is and add on to it. lee123 Sep 12 '07 #2

 P: 62 you could do this in the control source of the chairs: Expand|Select|Wrap|Line Numbers =IIf([Chairs]=3,6,IIf([chairs]=4,8)) and do this to all the rest, just follow how the code is and add on to it. lee123 Thanks for getting back to me. Unfortunately, this approach won't work very well for me. I have about 200 assemblies, and about as many parts, so doing it this way would be impossible. Also, if a new modification were made, a new criteria would have to be input, which is far from ideal from a data-management point of view. Sep 12 '07 #3

 100+ P: 556 well you can put it in the after update in the chairs like this: Private Sub chairs_AfterUpdate() Expand|Select|Wrap|Line Numbers       If chairs = 3 Then         screws.Value = 6 elseif chairs = 4 then         screws.value=8       End If End Sub lee123 Sep 13 '07 #4

 Expert Mod 10K+ P: 14,534 Assuming a Table structure as follows: tblAssembly AssemblyID (Primary Key) AssemblyName tblPartPerAssembly AssemblyID (Joint Primary Key) PartID (Joint Primary Key) tblPart PartID (Primary Key) PartName tblFastnerPerPart PartID (Joint Primary Key) FastnerID (Joint Primary Key) Qty tblFastner FastnerID (Primary Key) FastnerName You can now run the following query. Expand|Select|Wrap|Line Numbers SELECT tblAssembly.AssemblyID, tblAssembly.AssemblyName, Sum(tblFastnerPerPart.Qty) As Quantity, tblFastner.FastnerName FROM ((tblAssembly LEFT JOIN tblPartPerAssembly ON tblAssembly.AssemblyID = tblPartPerAssembly.AssemblyID) INNER JOIN tblFastnerPerPart ON tblPartPerAssembly.PartID = tblFastnerPerPart.PartID   Sep 13 '07 #5