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);

cleaned up to look better? no, but they might run better if you moved the HAVING conditions to WHERE conditions

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

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.

yes

I just tried and the WHERE statement does work with GROPY BY. Silly me.
Thanx for the help.

WHERE does work with GROUP BY

i do it all. the. time.

can i see your query?

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"));

change it to

WHERE Institute.InstType<>'Q'
AND MedStock.ForMonth="12/2003"
GROUP BY District.Description, MedStock.ForMonth

Thanx, I'll try that

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.

Thanx a TON Bullschmidt, this is exactly wht I have been looking for.

You're welcome and glad it helped!