Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Running Query off the results of another query

    Hi, I have gotten results from two queries. I am not very good at writing queries, so i was wondering if there is any way to combine the two queries and treat them as tables and run another query off them.
    They can be stored as different queries, but I was wondering if the code could be cleaned up so it looks better.

    The queries are below.
    Any help will be appreciated.
    Thanx in adv.
    Amrit


    Query One (This is saved as New OPDs in Access)

    SELECT District.Description AS District, OPD.ForMonth, Sum(OPD.New) AS SumOfNew
    FROM District INNER JOIN (Institute INNER JOIN OPD ON Institute.InstID = OPD.InstID) ON District.DistID = Institute.DistID
    GROUP BY District.Description, OPD.ForMonth
    HAVING (((OPD.ForMonth)="12/2003"));

    Query two (This is saved as Cost of Medicines Issued in Access)

    SELECT District.Description AS District, MedStock.ForMonth, Sum(MedStock.Issued*Medicines.Cost) AS CostIssued
    FROM Medicines INNER JOIN ((District INNER JOIN Institute ON District.DistID = Institute.DistID) INNER JOIN MedStock ON Institute.InstID = MedStock.InstID) ON Medicines.MedID = MedStock.MedID
    GROUP BY District.Description, MedStock.ForMonth
    HAVING (((MedStock.ForMonth)="12/2003"));


    The Final Query.


    SELECT [New OPDs].District, [New OPDs].ForMonth, [Cost of Medicines Issued].CostIssued, [New OPDs].SumOfNew AS OPDs, [Cost of Medicines Issued].CostIssued/[New OPDs].SumOfNew AS CostPerOPD
    FROM [Cost of Medicines Issued] INNER JOIN [New OPDs] ON ([Cost of Medicines Issued].District=[New OPDs].District) AND ([Cost of Medicines Issued].ForMonth=[New OPDs].ForMonth);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cleaned up to look better? no, but they might run better if you moved the HAVING conditions to WHERE conditions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    If I move the HAVING statements to WHERE, will I still be able to group the data using GROUP BY?

    Also, is there any utility that can clean up the SQL or any tutorial?

    Tanshu

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select dept_id, count(*)
    from table
    group by dept_id
    having count(*) > 10;

    In the above example, the condition is applied to each individual group as opposed to the entire set.
    Last edited by r123456; 02-08-04 at 23:57.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by tanshu
    If I move the HAVING statements to WHERE, will I still be able to group the data using GROUP BY?
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Posts
    6
    I just tried and the WHERE statement does work with GROPY BY. Silly me.
    Thanx for the help.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE does work with GROUP BY

    i do it all. the. time.

    can i see your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Posts
    6
    This is not the same query, but this query does use WHERE along with GROUP BY.

    SELECT District.Description AS District, MedStock.ForMonth, Sum(MedStock.Issued*Medicines.Cost) AS CostIssued
    FROM Medicines INNER JOIN ((District INNER JOIN Institute ON District.DistID=Institute.DistID) INNER JOIN MedStock ON Institute.InstID=MedStock.InstID) ON Medicines.MedID=MedStock.MedID
    WHERE (((Institute.InstType)<>'Q'))
    GROUP BY District.Description, MedStock.ForMonth
    HAVING (((MedStock.ForMonth)="12/2003"));

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change it to

    WHERE Institute.InstType<>'Q'
    AND MedStock.ForMonth="12/2003"
    GROUP BY District.Description, MedStock.ForMonth
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Posts
    6
    Thanx, I'll try that

  11. #11
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And here's another subquery example if that might hopefully help at all:

    Example of one query (QueryB) based on the results of another query (QueryA):

    QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

    QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" & QueryA & ")"

    But the following is even faster and allows for more than one field to be returned in QueryA:

    QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

    So QueryA would include all the CustID's for customers starting with A.

    And QueryB would include more fields in the customers table (i.e. not just the CustID field) for the records returned in QueryA (which was the customers starting with A).

    I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build from the tblSQLA on the left to other tables that have fields you want to return.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  12. #12
    Join Date
    Feb 2004
    Posts
    6
    Thanx a TON Bullschmidt, this is exactly wht I have been looking for.

  13. #13
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    You're welcome and glad it helped!
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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