C
crystal_xyz
Hi Everyone,
I have a pretty simple access database with two tables, Agents and
Sales.
[Agents:Table]
Agent_ID Name Phone email address
[Sales:Table]
Agent_ID HomeType Address Price etc
single
condo
multiple
mobile
land
I'd like to query the Sales table for a count of (HomeType) 'land'
sales grouped by Agent.Name. **The part I'm struggling with: if an
agent doesn't have a 'land' sale, I'd like them to appear in the
results anyway with '0' next to their name:
Barbara Jones 0
Tom Miller 2
etc
I've figured out how to count only those records with land, like so:
SELECT Agents.Name, COUNT(Sales.HomeType)
FROM Agents, Sales
WHERE Sales.Agent_ID = Agents.Agent_ID AND HomeType = land
GROUP BY Agents.Name
But I can't get any farther. I'd be very thankful for your help.
Thank you,
Crystal.
I have a pretty simple access database with two tables, Agents and
Sales.
[Agents:Table]
Agent_ID Name Phone email address
[Sales:Table]
Agent_ID HomeType Address Price etc
single
condo
multiple
mobile
land
I'd like to query the Sales table for a count of (HomeType) 'land'
sales grouped by Agent.Name. **The part I'm struggling with: if an
agent doesn't have a 'land' sale, I'd like them to appear in the
results anyway with '0' next to their name:
Barbara Jones 0
Tom Miller 2
etc
I've figured out how to count only those records with land, like so:
SELECT Agents.Name, COUNT(Sales.HomeType)
FROM Agents, Sales
WHERE Sales.Agent_ID = Agents.Agent_ID AND HomeType = land
GROUP BY Agents.Name
But I can't get any farther. I'd be very thankful for your help.
Thank you,
Crystal.