Calculate values using criteria in multiple tables?

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

Guest

I am still in the process of learning MS Access and am trying to create a
query for a newly created database. In one of the tables (CaseLog) the
CaseNumber field is an AutoNumber. CaseNumber is also a number field in the
MaterialsOrdered table, and with each new item needed for the case, a new
record is created in MaterialsOrdered table. The number automatically
assigned in the CaseLog table is entered into the MaterialsOrdered table to
keep track of the items needed for each case. I am trying to create a query
which will calculate the total value of materials ordered for each individual
case. Is this possible? If so, how do I build the expression?

I'd appreciate any help and advice!
 
Use this --
SELECT MaterialsOrdered.CaseNumber, Sum(MaterialsOrdered.MaterialsValue) AS
Materials_Ordered_Value
FROM MaterialsOrdered
GROUP BY MaterialsOrdered.CaseNumber;

Create a query and paste this in the SQL View. Edit the MaterialsValue to
your field name.
 
Jojoapple said:
I am still in the process of learning MS Access and am trying to create a
query for a newly created database. In one of the tables (CaseLog) the
CaseNumber field is an AutoNumber. CaseNumber is also a number field in the
MaterialsOrdered table, and with each new item needed for the case, a new
record is created in MaterialsOrdered table. The number automatically
assigned in the CaseLog table is entered into the MaterialsOrdered table to
keep track of the items needed for each case. I am trying to create a query
which will calculate the total value of materials ordered for each individual
case. Is this possible? If so, how do I build the expression?
Click to expand...


I think is the idea you're looking for:

SELECT CaseLog.CaseNumber,
Sum(MaterialsOrdered.price) As TotalValue
FROM CaseLog INNER JOIN MaterialsOrdered
ON CaseLog.CaseNumber = MaterialsOrdered.CaseNumber
GROUP BY CaseLog.CaseNumber
 
Back
Top