Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Unanswered: Problem totalling values from database and problem with related subqueries (Oy!)

    Hi, guys. This one is a stumper (for me). I hope someone out there can help.

    I've got three simple tables:

    tblPerson
    ---id, autonumber, pk
    ---name, text

    tblAdvances
    ---id, autonumber, pk
    ---personid, number, fk to tblPerson
    ---amount, currency

    tblRecoupments
    ---id, autonumber, pk
    ---personid, number, fk to tblPerson
    ---amount, currency

    I want to query the database and return one record per person that shows their name, their total advances and their total recoupments. I can't seem to do it in the query designer. If, for example, there is only one person in the database and that person has three advanced and three recoupments, the query first creates a Cartesian product of the data (1:1, 1:2: 1:3, 2:1, 2:2, etc) and then adds the values up. Obviously, this creates totals that are completely wrong.

    So, since I haven't been able to get this to work, I decided to use subqueries to return the sum of each persons advances and the sum of their recoupments. This causes a problem when I try to report because (apparently) Access doesn't like to group on a report if the dependant query uses subqueries.

    What am I doing wrong?!?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could do it with subqueries or DSum/DCount if you'd like.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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