Count how many times a word appears, even not at all

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.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe it's this:

SELECT Agents.Name, COUNT(Sales.HomeType) As LandCount
FROM Agents LEFT JOIN Sales
ON Sales.Agent_ID = Agents.Agent_ID
WHERE Sales.HomeType = 'land'
OR Sales.Agent_ID IS NULL
GROUP BY Agents.Name
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAap3YechKqOuFEgEQJX+QCeLMboGs5zjUTw1Sq+3piGFrWcSKIAoOo9
xHkC2Q188ew+2oJucOS/LSx2
=n26E
-----END PGP SIGNATURE-----
 
C

crystal_xyz

MG,

Thanks so much for this suggested code. Unfortunately it seems to
return the same results, which is a line for each agent that has ever
had a land sale, along with the land count (> 0). I think this is on
the right track and I'll keep playing with it. If there is only one
record for agent 16 and it's a condo sale, I'd like them to appear in
the list with his/her name and '0' next to it.

Out of curiousity (if you don't mind explaining), what does the line
"OR Sales.Agent_ID IS NULL". I ask because I'm just beginning to
understand NULL...it doesn't mean the obvious, which is that there are
records where the Agent_ID is actually blank or null, right (which
there are none)?

Thanks again,
Jennifer.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

"OR Sales.Agent_ID IS NULL" is supposed to mean any record in table
Sales that doesn't have a home type of 'land' will have a NULL
Agent_ID. Now that I explain it I realize this is why it doesn't work
'cuz the criteria HomeType = 'land' removes any NULL Agent_IDs from the
recordset. Hmmm.... Maybe this:

SELECT Agents.Name,
COUNT(IIf(Sales.HomeType='land',1,Null)) As LandCount
FROM Agents LEFT JOIN Sales
ON Sales.Agent_ID = Agents.Agent_ID
GROUP BY Agents.Name

Now the count will only occur when the HomeType = 'land.' The
disadvantage of this is it will scan the whole table, which may slow it
down.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAdNP4echKqOuFEgEQLz6QCfSGdDCJ8APsPSyVIgqIKwp5JT0nsAn3LV
7w+UO/3BnBjWB9qvMfDTUigU
=8gSr
-----END PGP SIGNATURE-----
 
Top