Multiplying quantity per unit by number of units sold

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a data base for a construction company. We have main
products (A,B,& C) which are made up by a group of assemblies (1,2,& 3) which
are made up of parts (X,Y,& Z). Is there a way for me to make an Order Table
so that if we sell 3 units of product A, Access will multiply it by the
quantity per unit for the assemblies and parts that make up Product A for a
grand total. Thanks in advance for any help.
 
In short Yes.
Create a System table with two fields, SystemID and
AssemblyID. ENter each system and each associated
assembly.
Create an Assembly table with two fields, AssemblyID and
PartID. Enter each Assembly and each associated partID
with partQty required for this assembly.
Create a Parts table, and enter each partID, nomenclature,
UnitPrice and what ever other data elements.
Now create a TOTALS query including the System, Assembly
and Parts database, linking the System table to the
Assembly table by the AssemblyID, and linking the Assembly
table to the parts table by the PartID.
Now in the fields list enter the SystemID and in the next
field enter :
TotalPrice:PartQty*UnitPrice
For the SystemID the Total: should be Group By, and for
the TotalPrice the Total: should be set to Sum.
That should be all there is to it.
If you wish I can email you a sample mdb
Hope this helps.
Fons
 
Back
Top