Counting and calculating over different tables in MS access

Alireza Ghavaminia 0
Viewed 53

I have three tables in my Microsoft access that stores the following data.

Wight table

tbl_weights
------------------------
Bill Number| Weight
1111          50
1112          60
1113          70
1114          30

The second table stores the pallets which were weighted:

tbl_pallets
------------------------
Pallet ID | Weighting Bill Number
ZM-001      1111
ZM-002      1111
ZM-003      1111
ZM-004      1112
ZM-005      1112
ZM-006      1113

The third table stores equipment data:

tbl_equipments
----------------------------
equipment ID | Pallets
O-001         ZM-001,ZM-002
O-002         ZM-003
O-003         ZM-003,ZM-004

The Pallets column is a multi-value field.

I need to calculate each equipment weight and store it on a table. How can I do that?

In a simple programming language, I would have a loop over each pallet in the tbk_equipments and search for the appearance of the corresponding pallet ID on the tbl_pallets then count the number of Weighting Bill Number and divide the corresponding weight for that bill number by the number of repetition of the bill number and then sum all of the resulted values to get the final answer. But I don't know how is the process in MS access.

I have tried "calculated fields" in the access but it does not allow me to have access to the fields on the other tables. I thought maybe there is a query or a function in VBA that I can use.

Thank you very much for your help.

ms-access vba
1 Answers

Assuming Pallets field in tbl_equipments is a multi-value field, consider this query:

SELECT EquipmentID, Sum(tbl_weights.Weight) AS SumWeight
FROM (tbl_weights INNER JOIN tbl_pallets ON tbl_weights.BillNumber = tbl_pallets.WeightBillNumber) 
INNER JOIN tbl_equipment ON tbl_pallets.PalletID = tbl_equipment.Pallets.Value
GROUP BY tbl_equipment.EquipmentID;

Result:

EquipmentID SumWeight
O-001         100
O-002          50
O-003         110

Note I have not used spaces in naming convention.

Saving calculated aggregate data to table is usually a bad idea. If it can be calculated for saving then it can be calculated when needed.

June70
Related