Results 1 to 3 of 3

Thread: qry help

  1. #1
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27

    Unanswered: qry help

    Greetings dbForums,

    We are a small office getting ready to convert our customer order log from an archaic flat file excel layout to a more modern relational access layout. I am having trouble with one aspect.

    ************************************************

    Here's what I got (the relative parts only):

    tblJob
    *Job
    J1234
    J1235

    tblCPO
    *CPO......Job........Amount
    X-1-1......J1234.....100,000
    X-1-2......J1234.........1,000
    10101.....J1235........50,000
    10102.....J1235..........1,000

    tblCIN
    *CIN..........CPO..........Amount
    J1234-A......X-1-1........10,000
    J1234-B......X-1-1........80,000
    J1235-A......10101.......20,000
    J1235-B......10101.......10,000

    A "one-to-many" relationship links Job-->CPO-->CIN. For every Job we can have one or more CPO customer purchase orders (usually only one but sometimes there are small upgrade adders). For every CPO customer purchase order we invoice many CIN customer invoice's (most of our work is long term so we will issue many invoices over time against the customer's purchase order).

    ************************************************

    Here's is my current query output and settings:

    qryJobStatus (showing all 3 tables in the graphic area)

    Job...........Sum Of CPO.......Sum Of CIN
    J1234........100,000.............90,000
    J1235..........50,000.............30,000

    Field.......Job.............Sum Of CPO: Amount......Sum Of CIN: Amount
    Table......Job.............CPO.................... ...........CIN
    Total.......GroupBY.....GroupBY................... ......Sum

    As you can see the sum of the customer purchase orders is not working correctly (should be 101,000 and 51,000). When I have a simple query using only the CPO table I can get the correct CPO totals, however, when I introduce the other two tables I get the error.

    Thanks In Advance...

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Have you created relationships between the tables?

  3. #3
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27
    Yes tables Job, CPO, and CIN are linked.
    All relationships include:
    "Enforce Referential Integrity"
    "Cascade Update Related Fields"

    Thanks for your time...

Posting Permissions

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