Results 1 to 5 of 5

Thread: Query Help

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

    Unanswered: Query Help

    Can someone take a quick look at the attached database file? Instead of writing a lengthy description I thought it would be best to include the file (a picture = 1000 words). It contains relevant parts only and is only 172kb in size. I am having trouble getting the correct results from a query.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would love to help

    can't

    all i have is access 97
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27
    Thanks for looking R937,

    I am currently using 2000 and just used the"convert database to prior version function". Looks like it saved down to 97.

    Here is a little background...

    For every Job we can have one or more CPO customer purchase orders (usually only one but sometimes there are upgrade adders after getting an order). Then, 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).

    Thanks for your time...
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    step 1: join the tables and have a look-see at the data
    Code:
    select Job.Job
         , CPO.CPO
         , CPO.Amount AS CPOAmount
         , CIN.CIN
         , CIN.Amount AS CINAmount
      from (
           Job 
    inner 
      join CPO 
        on Job.Job = CPO.Job
           )
    left outer
      join CIN 
        on CPO.CPO = CIN.CPO
    order 
        by Job.Job
         , CPO.CPO
         , CIN.CIN
         
    Job    CPO    CPOAmount    CIN      CINAmount
    J1234  X-1-1  $100,000.00  J1234-A  $10,000.00
    J1234  X-1-1  $100,000.00  J1234-B  $80,000.00
    J1234  X-1-2  $1,000.00    
    J1235  10101  $50,000.00   J1235-A  $20,000.00
    J1235  10101  $50,000.00   J1235-B  $10,000.00
    J1235  10102  $1,000.00
    step 2: change the query to aggregate the invoice amounts to each PO:
    Code:
    select Job.Job
         , CPO.CPO
         , CPO.Amount AS CPOAmount
         , count(CIN.CIN) as CINs
         , sum(CIN.Amount) AS SumCINAmount
      from (
           Job 
    inner 
      join CPO 
        on Job.Job = CPO.Job
           )
    left outer
      join CIN 
        on CPO.CPO = CIN.CPO
    group 
        by Job.Job
         , CPO.CPO
         , CPO.Amount
         
    Job    CPO    CPOAmount    CINs  SumCINAmount
    J1234  X-1-1  $100,000.00  2     $90,000.00
    J1234  X-1-2  $1,000.00    0  
    J1235  10101  $50,000.00   2     $30,000.00
    J1235  10102  $1,000.00    0
    step 3a: save the above query (access 97 does not support derived tables, but there's no trouble selecting from a saved query)

    step 3b: aggregate the PO and invoice totals:
    Code:
    select Job
         , count(CPO) as CPOs
         , sum(CPOAmount) as SumCPOAmount
         , sum(CINs) as CountCINs
         , sum(SumCINAmount) as SumSumCINAmount
      from savedquery
    group
        by Job
    
    Job    CPOs  SumCPOAmount  CountCINs  SumSumCINAmount
    J1234  2     $101,000.00   2          $90,000.00
    J1235  2     $51,000.00    2          $30,000.00
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Worked Great...

    The solution(s) was more complicated then I thought it would be. It will take a little time to digest, understand, and experiment with your code.

    Thanks Again !

Posting Permissions

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