Re: Update query.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

jfaz wrote:
I am going round in circles with this and would be grateful for any help offered. It may be a case that I am missing something quite simple but phrase wood and trees comes to mind!!!!!!!!!!!!!!!!!!!!!!!!!

I have a table TblJobTotals with the following fields.
Job Number, Trade (both these fields together make up the primary key), Original hours, variation hours, completed hours original, completed hours variation.

I need to populate this table with summary information from two additional tables TblOriginal and TblVariation.

I have created queries over each of the tables grouping by job number and trade and sum of hours.

I need to pass these summary totals to my TblJobTotals but am unsure how to do it.

How can I create an update query which will drop the totals into the relevant record based on job number and trade?

JS's advice is well heeded, just an additional thought...

It sounds like you have a working query to get the information you need. Why not just use this query to further your analysis/populate your report/ or whatever the destination is for this data? By using a query, you will always get current data. If TblJobTotals feeds some other report or whatnot, you can substitute your working query very easily.
.

Relevant Pages

  • Re: Update query.
    ... You should calculate these values as needed in a query and should not even have the table TblJobTotals. ... UPDATING is much more complex since you cannot use the total queries in an update query, but must use the aggregate functions DSum, DLookup, etc. ... Job Number, Trade, Original hours, variation hours, completed hours original, completed hours variation. ...
    (microsoft.public.access.queries)
  • Re: Query
    ... Thanks for your post John. ... THEN you can use a union query to combine the three queries into one query. ... It comprises all of my trade ...
    (microsoft.public.access.gettingstarted)
  • Re: Union Query?
    ... >I am trying to write a query that will result in getting the name, ... > and trade of my firm being added to the name address and trade of the ... > consultants for every job. ...
    (microsoft.public.access.queries)
  • Re: Query
    ... qryIllinoisPlanSponsorMonthtoDate.[SumOfCommission Amount], ... THEN you can use a union query to combine the three queries into one query. ... It comprises all of my trade ...
    (microsoft.public.access.gettingstarted)
  • Re: Table Query Parameters problem
    ... It might not be as efficient when the query is run, but if your tables are fairly small the speed difference may be unnoticeable to a human. ... Criteria: [What County] ... Notice how the parentheses include all the criteria for the trade fields. ... PersonTrades ...
    (microsoft.public.access.queries)