- Joined
- Jul 17, 2007
- Messages
- 3
- Reaction score
- 0
I have a database for an engineering project that has 3 main tables. These are the ProjectCode, the Area and the Costs
The ProjectCode has multiple Areas and the Areas have multiple Costs. Data entry to the Costs table includes a [ProjectCode], [Area] and an [Amount Aus]
My Area Table has all the area / project code combinations entered as these all have an individual budget figure that needs the costs to compare to
I want my database to be able to sum up the [Amount Aus] in the costs table and update the field [AmountAus] in the Area table, but only for the costs that match the [ProjectCode] and [Area]
Area Table
AutoIDBudgetAusAmountAusAreadDescriptProjectCodeArea4$900,700.00 Crushing1125$1,939,820.00 Process Plant Design 015$1,413,854.67 Milling126$953,957.33 Flotation137$450,176.00 CIL148$356,532.00 Elution / Recovery159$140,000.00 Reagents1610$254,666.67 Plant Services1711$362,757.33 Concentrate Handling1812$1,096,594.67 Village / Camp12026$32,609.33 Village & Camp02013$2,662,938.67 Mining Services12114$0.00 Gold Recovery12215$3,312,594.67 Plant HV Power Distribution12316$449,604.00 Communications12417$584,370.67 Construction Tools / Equip12518$740,558.67 Travel12619$1,270,021.33 Plant Buildings12727$42,493.33 Budget Re-validation02820$0.00 Meetings & Administration12921$1,270,021.33 Roaster14122$0.00 Waste Heat Recovery14228$32,609.33 Waste Heat Recovery04223 Power Station14329$70,852.00 Power Station04324$0.00 Off-Gas14430$30,916.00 Off-Gas044
Cost Table
AutoIDDateProject CodeAreaPerson or SupplierDetailQuantityRate AusAmount Aus28416-Jul-0711BECStuff2$250.00$500.0028316-Jul-0711AIMSDCS1$1,000.00$1,000.0028516-Jul-0701AIMSMore Stuff2$100.00$200.00
I am using an update query that will allow me to write to the [AmountAus] field in the Area Table, but I cannot get my criteria right to only sum the same [ProjectCode] and [Area] fields. My update query writes a vale of $1700 which is the sum of the entire Cost Table. In the above example my figures in Area table should be;
ProjectCode Area AmountAus
1 1 $1500
0 1 $200
I do not understand the use of " ' and & in the criteria fields
The ProjectCode has multiple Areas and the Areas have multiple Costs. Data entry to the Costs table includes a [ProjectCode], [Area] and an [Amount Aus]
My Area Table has all the area / project code combinations entered as these all have an individual budget figure that needs the costs to compare to
I want my database to be able to sum up the [Amount Aus] in the costs table and update the field [AmountAus] in the Area table, but only for the costs that match the [ProjectCode] and [Area]
Area Table
AutoIDBudgetAusAmountAusAreadDescriptProjectCodeArea4$900,700.00 Crushing1125$1,939,820.00 Process Plant Design 015$1,413,854.67 Milling126$953,957.33 Flotation137$450,176.00 CIL148$356,532.00 Elution / Recovery159$140,000.00 Reagents1610$254,666.67 Plant Services1711$362,757.33 Concentrate Handling1812$1,096,594.67 Village / Camp12026$32,609.33 Village & Camp02013$2,662,938.67 Mining Services12114$0.00 Gold Recovery12215$3,312,594.67 Plant HV Power Distribution12316$449,604.00 Communications12417$584,370.67 Construction Tools / Equip12518$740,558.67 Travel12619$1,270,021.33 Plant Buildings12727$42,493.33 Budget Re-validation02820$0.00 Meetings & Administration12921$1,270,021.33 Roaster14122$0.00 Waste Heat Recovery14228$32,609.33 Waste Heat Recovery04223 Power Station14329$70,852.00 Power Station04324$0.00 Off-Gas14430$30,916.00 Off-Gas044
Cost Table
AutoIDDateProject CodeAreaPerson or SupplierDetailQuantityRate AusAmount Aus28416-Jul-0711BECStuff2$250.00$500.0028316-Jul-0711AIMSDCS1$1,000.00$1,000.0028516-Jul-0701AIMSMore Stuff2$100.00$200.00
I am using an update query that will allow me to write to the [AmountAus] field in the Area Table, but I cannot get my criteria right to only sum the same [ProjectCode] and [Area] fields. My update query writes a vale of $1700 which is the sum of the entire Cost Table. In the above example my figures in Area table should be;
ProjectCode Area AmountAus
1 1 $1500
0 1 $200
I do not understand the use of " ' and & in the criteria fields