Multiply quantities

[nickvans]

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

[lee123]

you could do this in the control source of the chairs:

=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

[nickvans]

you could do this in the control source of the chairs:

=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.

[lee123]

well you can put it in the after update in the chairs like this:

Private Sub chairs_AfterUpdate()

 
    If chairs = 3 Then
        screws.Value = 6
elseif chairs = 4 then
        screws.value=8
 
    End If

End Sub

lee123

[MMcCarthy]

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.

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