Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Location
    Baton Rouge
    Posts
    6

    Angry Unanswered: Expert needed for this one( Select with sums on self joined table)

    I have a services table that holds two types of records(new & old) and among the fields I have is Price. I need to add the total Price of all the new records and all total price of the old records. so I came up with this code.

    SELECT Sum(tbl1.Price) AS OldCost, tbl1.ID, Sum(tbl2.Price) AS NewCost, tbl2.ID
    FROM tblServices AS tbl1 INNER JOIN tblServices AS tbl2 ON tbl1.ClientID = tbl2.ClientID
    WHERE ((tbl1.Type)="O") AND ((tbl2.Type)="N"))
    GROUP BY tbl1.ClientID, tbl2.ClientID;

    Now this almost works. except for the fact that it is doing some weird stuff by multiplying the totals times the number of records that it is pulling or something really close to that. I have never seen this before.

    As a end Result I want three fields per record.
    Total of New Services, Total of Old Services, and The client ID.

    I have looked at it for 3 hrs and tryed everything I know to try. If I separate the query into two separate ones ( 1 for new and one for Old) it works perfectly but combinding them it just craps out.

    Any Advice on what I am doing wrong.?
    I really need some help....

  2. #2
    Join Date
    Jun 2002
    Location
    Baton Rouge
    Posts
    6

    nevermind....

    I am using access for my front end and I was playing around for kicks and I you can take two querys and make a nother one base doff those two, so that was my awnser.
    Thanks

Posting Permissions

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