Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    11

    Unanswered: Can't create a total field between three tables

    Hi,
    I am using MS Access 2010 on Windows System 7.

    I have three tables: A, B, C
    Table A has the field Task NO
    Table B has the fields Task No, PriorActuals
    Table C has the fields Task No, HoldbackActuals

    I need to create a query that tasks the Task No from Table A and matches it to Table B and Table C. I then need to show in the query the following:

    Task no PriorActuals HoldbackActuals Total

    My problem is that depending on how I connect the tables, different values are stored in the Total field. the Total field will either be all the data in the PriorActuals field or it will be all the data in the HoldbackActuals field. I can't get the total field to show PriorActuals + holdbackActuals.

    Please help. i have tried everything I can think of and I can't get it to work.

    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Air code:
    Code:
    SELECT
    a.[Task No]
    , b.PriorActuals
    , c.HoldbackActuals
    , b.PriorActuals + c.HoldbackActuals AS TotalActuals
    FROM
    a
    INNER JOIN
    b
    ON
    a.[Task No] = b.[Task No]
    INNER JOIN
    c
    ON
    a.[Task No] = c.[Task No];
    This will only return results for tasks that have prior and holdback actuals. Also, if you have the same task number more than once in any table, you will get odd results.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2011
    Posts
    11

    Trouble creating a total column from two other columns

    thank you.

    Here is the code that works:

    SELECT [JPAS TASKS].[TASK NO], Sum([PRIOR ACTUALS (PID) QRY].[PRIOR ACTUALS]) AS [SumOfPRIOR ACTUALS], Sum([PRIOR ACTUALS (PID) QRY].[PRIOR ACTUALS]) AS TOTPrior, Sum([PRIOR HOLDBACKS (PID) QRY].[PRIOR HOLDBACKS]) AS [SumOfPRIOR HOLDBACKS], Sum([PRIOR HOLDBACKS (PID) QRY].[PRIOR HOLDBACKS]) AS TOTHoldBacks, Sum(Nz([prior actuals],0)+Nz([prior Holdbacks],0)) AS Total
    FROM ([JPAS TASKS] LEFT JOIN [PRIOR ACTUALS (PID) QRY] ON [JPAS TASKS].[TASK NO] = [PRIOR ACTUALS (PID) QRY].[TASK NO]) LEFT JOIN [PRIOR HOLDBACKS (PID) QRY] ON [JPAS TASKS].[TASK NO] = [PRIOR HOLDBACKS (PID) QRY].[TASK NO]
    GROUP BY [JPAS TASKS].[TASK NO];


    The only problem I have now is that I'm unable to sum the total column.

    Sharon

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm fairly sure that you can't refer to calculated columns by the aliases in aggregate queries. You'll need to add the full version for each one again.

    Also, you'll find it easier to write queries if you amend your tables so that you only use letters, numbers and underscores in their names and the names of fields.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2011
    Posts
    11
    thanks for the info, I'll get rid of the alias and see if that works. This database was created by someone else and I'm just trying to help them out. I agree with you that you only use letters, numbers and underscores in their names and the names of fields. It makes life a lot simpler.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •