By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,153 Members | 2,581 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
4 Replies


lee123
100+
P: 556
you could do this in the control source of the chairs:

Expand|Select|Wrap|Line Numbers
  1. =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
  1. =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

lee123
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
  1.  
  2.     If chairs = 3 Then
  3.         screws.Value = 6
  4. elseif chairs = 4 then
  5.         screws.value=8
  6.  
  7.     End If
End Sub

lee123
Sep 13 '07 #4

MMcCarthy
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
  1. SELECT tblAssembly.AssemblyID, tblAssembly.AssemblyName, Sum(tblFastnerPerPart.Qty) As Quantity, tblFastner.FastnerName
  2. FROM ((tblAssembly LEFT JOIN tblPartPerAssembly
  3. ON tblAssembly.AssemblyID = tblPartPerAssembly.AssemblyID)
  4. INNER JOIN tblFastnerPerPart
  5. ON tblPartPerAssembly.PartID = tblFastnerPerPart.PartID
  6.  
Sep 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.