Access Database Help

Access Discussion in 'Microsoft Office' started by bradhanson, Jul 17, 2007.

  1. bradhanson

    bradhanson

    Joined:
    Jul 17, 2007
    Likes Received:
    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
     
    bradhanson, Jul 17, 2007
    #1
    1. Advertisements

  2. bradhanson

    bradhanson

    Joined:
    Jul 17, 2007
    Likes Received:
    0
    Database

    I have attached a word document that shows structure as the tables didn't work on my thread
     

    Attached Files:

    bradhanson, Jul 17, 2007
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.