Subquery Count

[des0906]

I use this query to populate a combo box. tblPersonnel has a many-to-one relationship with tblOffices (a lot of people can work at one office). However, some people have two or more offices they work at. To indicate this, I currently display both the personnel name and the office name. I would like to do away with the office name and replace it with an instance count value. Any suggestions on a resolution to this problem will be appreciated.

For instance:

tblOffices.ID PersonName OfficeName
289 Bob Smith Milwaukee
323 Bob Smith Bob Smith Home

Could I replace the Officename with a 1, a 2, etc.


SELECT tblOffices.ID, [First] & ' ' & [Last] AS PersonName, tblOffices.OfficeName
FROM tblOffices INNER JOIN tblPersonnel ON tblOffices.ID = tblPersonnel.OfficeID
GROUP BY tblOffices.ID, [First] & ' ' & [Last], tblOffices.OfficeName, tblPersonnel.First, tblPersonnel.Last, tblPersonnel.Status
HAVING (((tblPersonnel.First) Is Not Null) AND ((tblPersonnel.Last) Is Not Null) AND ((tblPersonnel.Status)=1))
ORDER BY [First] & ' ' & [Last];


G3r\Octel VMX 300
"Sanity is a goal, not a guarentee"

 

[lespaul]

only if you remove the tblOffices.ID. here's how aggregate queries work:
[tt]
An aggregate function performs based on all the other fields in your select.

If you have a table like:

Salesman SaleDate Amount
JR 1/1/2006 $500.00
EM 1/1/2006 $250.00
JR 1/1/2006 $100.00
EM 1/2/2006 $101.00
JR 1/2/2006 $75.00
JR 1/2/2006 $100.00

and you want to sum the sales for the month for EACH salesman:

SELECT Salesman, SUM(Amount) FROM TableName GROUP BY Salesman

the results would be:

JR $775.00
EM $351.00

If you want to know how much per day:

SELECT SalesDate, SUM(Amount) FROM TableNmae GROUP BY SalesDate

the results would be:

1/1/2006 850.00
1/2/2006 276.00

If you want by Salesman, by Date:

SELECT Salesman, SalesDate, SUM(Amount) FROM tableName GROUP BY Salesman, SalesDate ORDER BY Salesman, SalesDate

EM 1/1/2006 250.00
EM 1/2/2006 101.00
JR 1/1/2006 600.00
JR 1/2/2006 175.00[/tt]

additionally Access is notorious for incorrectly using the HAVING clause. here's some information on how it's SUPPOSE to be used:
[tt]
you only need to use a HAVING clause to filter on an aggregate function. Say you wanted to see total orders by customer:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customer

this will return:

1 10000
2 20000

if you only want customers who live in California, you would put that in a WHERE clause (similar to just wanting 'OS'):

SELECT Customer, SUM(OrderTOtal) From Orders WHERE State = "CA" Group by Customer

If you only want customers who have a OrderTotal sum of greater than 15000 then you would use a HAVING clause:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customers HAVING Sum(OrderTOtal) > 15000

To get customers in California with an OrderTotal > 15000 then:

SELECT Customer, SUM(OrderTotal) FROM Orders WHERE State = "CA" GROUP BY Customer HAVING Sum(OrderTotal) > 15000

Hope you have a better understandin of the difference between a WHERE clause and the HAVING clause.[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi