Two queries in one

[SPW_12]

I built a dynamic query interface form for end users such that they can query any way they like; this is a financial db. One of the options is to query on date. If the user selects a date range, the sum of unique accts is aggregated by date. If the user wants to see the sum of a date range, but not return a row for each date within the date range, I need to first aggregate, then query on that aggregated. Querying a query...
Is there a way to combine the following into one query? In one shot?



Here's the SQL of the first:
SELECT DISTINCT tbl_GL.Months, tbl_Physician.LastName, tbl_GL.Account, tbl_GL.Amount
FROM tbl_Physician INNER JOIN tbl_GL ON tbl_Physician.PhysicianID = tbl_GL.PhysicianID
GROUP BY tbl_GL.Months, tbl_Physician.LastName, tbl_GL.Account, tbl_GL.Amount;

Then the other:
SELECT Example.LastName, Example.Account, Sum(Example.Amount) AS SumOfAmount
FROM Example
GROUP BY Example.LastName, Example.Account;

Thanks

[June7]

You can do copy/paste in SQL View window of the first SQL string into the second. The result would be:

SELECT Example.LastName, Example.Account, Sum(Example.Amount) AS SumOfAmount
FROM (SELECT DISTINCT tbl_GL.Months, tbl_Physician.LastName, tbl_GL.Account, tbl_GL.Amount
FROM tbl_Physician INNER JOIN tbl_GL ON tbl_Physician.PhysicianID = tbl_GL.PhysicianID
GROUP BY tbl_GL.Months, tbl_Physician.LastName, tbl_GL.Account, tbl_GL.Amount) AS Example
GROUP BY Example.LastName, Example.Account;

[SPW_12]

thank you, this works, but I omitted the part where I tell the first query the date range parameter. Here is how I modified it:

SELECT Example.LastName, Example.Account, Sum(Example.Amount) AS SumOfAmount
FROM (SELECT DISTINCT tbl_GL.Months, tbl_Physician.LastName, tbl_GL.Account, tbl_GL.Amount
FROM tbl_Physician INNER JOIN tbl_GL ON tbl_Physician.PhysicianID = tbl_GL.PhysicianID
GROUP BY tbl_GL.Months, tbl_Physician.LastName, tbl_GL.Account, tbl_GL.Amount) AS Example
GROUP BY Example.LastName, Example.Account
HAVING (((tbl_GL.Months) Between #8/1/2012# And #7/1/2012#));

thanks for the help!

[June7]

The nested query works? I am surprised because the date criteria is in the outer SQL and not the inner.

[SPW_12]

well, i thought it was working but the results are not correct. I added quite a few more parameters, I am trying to get it to work. Here are the two queries I need to combine:

1st
SELECT DISTINCT tbl_GL.Months, tbl_Physician.LastName, tbl_PhysSpecialtyID.Specialty, tbl_DemoCountyID.County, tbl_CoA.Sub_Rpt_Descr, tbl_PhysSpecialtyID.SpecialtyID, tbl_DemoCountyID.CountyID, tbl_CoA.Category_ID, tbl_GL.Amount
FROM tbl_PhysSpecialtyID INNER JOIN (tbl_Physician INNER JOIN ((tbl_DemoCountyID INNER JOIN (tbl_DemoAddressID INNER JOIN tbl_Demographics ON tbl_DemoAddressID.AddressID = tbl_Demographics.AddressID) ON tbl_DemoCountyID.CountyID = tbl_Demographics.CountyID) INNER JOIN (tbl_CoA INNER JOIN tbl_GL ON tbl_CoA.Account = tbl_GL.Account) ON tbl_Demographics.PhysicianID = tbl_GL.PhysicianID) ON tbl_Physician.PhysicianID = tbl_GL.PhysicianID) ON tbl_PhysSpecialtyID.SpecialtyID = tbl_Physician.SpecialtyID
GROUP BY tbl_GL.Months, tbl_Physician.LastName, tbl_PhysSpecialtyID.Specialty, tbl_DemoCountyID.County, tbl_CoA.Sub_Rpt_Descr, tbl_PhysSpecialtyID.SpecialtyID, tbl_DemoCountyID.CountyID, tbl_CoA.Category_ID, tbl_GL.Amount, tbl_Physician.Inactive
HAVING (((tbl_GL.Months) Between #8/1/2011# And #7/1/2012#) AND ((tbl_CoA.Category_ID)=1 Or (tbl_CoA.Category_ID)=2) AND ((tbl_Physician.Inactive)=False));

2nd
SELECT Example.LastName, Example.Specialty, Example.County, Example.Sub_Rpt_Descr, Sum(Example.Amount) AS SumOfAmount
FROM Example
GROUP BY Example.LastName, Example.Specialty, Example.County, Example.Sub_Rpt_Descr;

[SPW_12]

Thanks June7, I finally got it with your help.

SELECT Example.LastName, Example.Specialty, Example.County, Example.Sub_Rpt_Descr, Sum(Example.Amount) AS SumOfAmount
FROM (SELECT DISTINCT tbl_GL.Months, tbl_Physician.LastName, tbl_PhysSpecialtyID.Specialty, tbl_DemoCountyID.County, tbl_CoA.Sub_Rpt_Descr, tbl_PhysSpecialtyID.SpecialtyID, tbl_DemoCountyID.CountyID, tbl_CoA.Category_ID, tbl_GL.Amount
FROM tbl_PhysSpecialtyID INNER JOIN (tbl_Physician INNER JOIN ((tbl_DemoCountyID INNER JOIN (tbl_DemoAddressID INNER JOIN tbl_Demographics ON tbl_DemoAddressID.AddressID = tbl_Demographics.AddressID) ON tbl_DemoCountyID.CountyID = tbl_Demographics.CountyID) INNER JOIN (tbl_CoA INNER JOIN tbl_GL ON tbl_CoA.Account = tbl_GL.Account) ON tbl_Demographics.PhysicianID = tbl_GL.PhysicianID) ON tbl_Physician.PhysicianID = tbl_GL.PhysicianID) ON tbl_PhysSpecialtyID.SpecialtyID = tbl_Physician.SpecialtyID
GROUP BY tbl_GL.Months, tbl_Physician.LastName, tbl_PhysSpecialtyID.Specialty, tbl_DemoCountyID.County, tbl_CoA.Sub_Rpt_Descr, tbl_PhysSpecialtyID.SpecialtyID, tbl_DemoCountyID.CountyID, tbl_CoA.Category_ID, tbl_GL.Amount, tbl_Physician.Inactive
HAVING (((tbl_GL.Months) Between #8/1/2011# And #7/1/2012#) AND ((tbl_CoA.Category_ID)=1 Or (tbl_CoA.Category_ID)=2) AND ((tbl_Physician.Inactive)=False))) As Example
GROUP BY Example.LastName, Example.Specialty, Example.County, Example.Sub_Rpt_Descr;