Results 1 to 10 of 10

Thread: help in reports

  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: help in reports

    hello i have 3 differents query making different calculations, i want to show the results in 1 report how do i do that?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Me.Geek = True

  3. #3
    Join Date
    Jun 2009
    Posts
    12
    also when i tried the report wizard i got the msg: you have chosen fields from record sources which the wizard can't connect...

  4. #4
    Join Date
    Jun 2009
    Posts
    12
    total money query:
    SELECT sum(Account.price)
    FROM Account, Client
    WHERE Client.Active='Yes' AND Client.Accountspeed=Account.Speed;

    money remaining query:
    SELECT sum(account.price)
    FROM Client, Account
    WHERE Client.Accountspeed = Account.speed AND Client.Active='YES' AND Client.id NOT IN (SELECT Client_ID_Monthpayed FROM MonthPayed Where Month_Payed=Month(date()));

    and the 3rd to be shown in the report is the result of the 1st one minus the 2nd

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    I think you'll need a subquery.
    Me.Geek = True

  6. #6
    Join Date
    Jun 2009
    Posts
    12
    ok thanks i did this
    SELECT sum(Account.price) AS total,
    (SELECT sum(account.price)
    FROM Client, Account
    WHERE Client.Accountspeed = Account.speed AND Client.Active='YES' AND Client.id NOT IN (SELECT Client_ID_Monthpayed FROM MonthPayed Where Month_Payed=Month(date()))) AS Remaining
    FROM Account, Client
    WHERE Client.Active='Yes' AND Client.Accountspeed=Account.Speed;

    it works for the query but for the reports it gives the msg: Multi-level Group By Clause is not allowed in a subquery, also how do i calculated the difference between the 1st and the 2nd query and add them to the single one that i did

  7. #7
    Join Date
    Jun 2009
    Posts
    12
    i managed to add the 3rd one ( the difference btw the 1st and second) but still same msg on report

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Have you verified that both original queries work independantly?
    Me.Geek = True

  9. #9
    Join Date
    Jun 2009
    Posts
    12
    what do u mean by independantly?

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Have you tried each query separately to verify they return what you want?
    Me.Geek = True

Posting Permissions

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