How do you combine multiple queries into a single query? 1

[robertsfd]

I have the following two tables that track vendors and purchases made from vendors (one-to-many relationship):

Vendor(VendorID, VendorName)
Purchase(PurchaseID, Date, Amount, VendorID)

I want to list all vendors that have total purchases greater than the average total of purchases considering all vendors.

I know how to do this using a series of queries, but have not been able to combine it all into one query. Is this possible? I was unable to resolve this question based on prior postings I found.

Here are the four queries that I created to arrive at my deasired result, but I can't figure out how to get them all into one query.

Query 1 - list all unique VendorIDs in Purchase table:

SELECT DISTINCT Purchase.VendorID
FROM Purchase;

Query 2 - count the number of records in query 1 recordset:

SELECT Count(*)
FROM Query1;

Query 3 - sum the amount for all purchases:

SELECT Sum(Purchase.Amount)
FROM Purchase;

Query 4 - list the vendor names that have total purchases in the Purchase table greater than the average total purchases (Query 3 divided by Query 2):

SELECT Vendor.VendorName
FROM Vendor, Purchase
WHERE Vendor.VendorID=Purchase.VendorID
GROUP BY Vendor.VendorName
HAVING Sum(Purchase.Amount) >
(SELECT Query3.Expr1000 / Query2.Expr1000
FROM Query3, Query2);

 

[mpastore]

I think this query is equavalent, although arrived at differently:

SELECT vendor.vendorID, vendor.vendorName, Sum(purchase.amount) AS SumOfamount
FROM vendor, purchase
WHERE (((vendor.vendorID)=[purchase].[vendorid]))
GROUP BY vendor.vendorID, vendor.vendorName
HAVING (((avg(purchase.amount))>(select avg(amount) from purchase)));


Mike Pastore

Hats off to (Roy) Harper

 

[winnechan]

have u tried use the access query? i think that would do.

 

[robertsfd]

mpastore, thanks this does work. I still wonder - is it poosible to combine queries in other ways? For example, could query 1 and 2 be combined? I tried doing the following (which seemed logical but did not work):

SELECT Count(*)
FROM
(SELECT DISTINCT Purchasetest.VendorID
FROM Purchasetest);

I get "Syntax error in FROM clause" error message. Is there another way this could be done?

Thanks.

 

[mpastore]

Unfortunately, I don't think so. Other SQL dialects have the COUNT(DISTINCT *) clause that allows for this. Funny that MS access does not.

Mike Pastore

Hats off to (Roy) Harper