Assistance required with query

[Andrew]

Hi,

I'd like to combine the two queries below into one (just to eliminate the
number of queries I have).

The query 'Qry_Group_Vendor_Name' consists of:
SELECT tbl_Comm_Data.VENDOR_NAME AS Vendor, tbl_Comm_Data.DEPT_NO AS Dept
FROM tbl_Comm_Data GROUP BY tbl_Comm_Data.VENDOR_NAME, tbl_Comm_Data.DEPT_NO;

It produces a list of unique) vendor names by department.

I then want to produce a count of (unique) vendor names by department in a
table (Tbl_Count_Of_Vendors).
SELECT Qry_Group_Vendor_Name.Dept, Count(Qry_Group_Vendor_Name.Vendor) AS
Vendors INTO Tbl_Count_Of_Vendors FROM Qry_Group_Vendor_Name GROUP BY
Qry_Group_Vendor_Name.Dept;

Can you please assist.

 

[John Spencer]

SELECT Q.Dept, Count(Q.Vendor) AS Vendors
INTO Tbl_Count_Of_Vendors
FROM (SELECT tbl_Comm_Data.VENDOR_NAME AS Vendor
, tbl_Comm_Data.DEPT_NO AS Dept
FROM tbl_Comm_Data
GROUP BY tbl_Comm_Data.VENDOR_NAME
, tbl_Comm_Data.DEPT_NO) as Q
GROUP BY Q.Dept;


If that works that is fine, but there is normally no good reason to do
an insert. Just use a select query that looks like the following. That
way your data will always be current.

SELECT Q.Dept, Count(Q.Vendor) AS Vendors
FROM (SELECT tbl_Comm_Data.VENDOR_NAME AS Vendor
, tbl_Comm_Data.DEPT_NO AS Dept
FROM tbl_Comm_Data
GROUP BY tbl_Comm_Data.VENDOR_NAME
, tbl_Comm_Data.DEPT_NO) as Q
GROUP BY Q.Dept;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================