I have a table holding a list of na

[bobmunkhouse]

I have a table holding a list of names. There are currently 4 names, and they are each held a number of times.
(e.g. bob, jim, joe, paul, paul, joe, jim, jim, jim)

I need a query to display each name; with the number of times it appears in the table next to it
(e.g.
Bob 1
Jim 4
Joe 2
Paul 2
)

I have tried to achieve this with a COUNT query. The problem is, if one name does not appear in the table I need the query to display a 0 next to the name, rather than the name not appearing at all.

I also have a second query holding each name once (so that you can see which names are missing from the table), if this helps.

Please help. Layman’s terms preferred

p.s. if this problem can be achieved using a report instead that would be fine, thank you

thankyou in advance

 

[KenReay]

Hi

you need to make a query, joining your two tables.

Without knowing the table structure cannot advise on join column.

Your join need to be a left join, ie including all records from the 'master' list of names.

For names not present in you second table, you will get a null value, so you need to count on Nz(fieldname,0)

Sorry this is not very 'layman' but you have not included any information on you table structures of names etc, so it is difficult to explain with an example. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bobmunkhouse]

alright, I'm a little confused, but that's because I think you're using SQL, which i'm not too familiar with, but I know where to insert it in a query.

I've got a "tblTeam" table where one of the fields is called "TeamName" and is thus a list of all the names.

I then have a "qryLeagueWon" query with two fields, the first being "Won", and the second being "TeamName". the won field holds a 1 always (don't ask!), and the Teamname field holds different amounts of each name(as the first post explains).

how can I now write this in SQL?

confused? please don't be, i need this

 

[bobmunkhouse]

This is the SQL in the query I currently use. The problem with this one is that the TeamName disappears if there is no value for the Won field.

SELECT qryLeagueWon.TeamName AS Expr1, Sum(qryLeagueWon.Won) AS Won
FROM qryLeagueWon
GROUP BY qryLeagueWon.TeamName;

 

[bobmunkhouse]

Don't worry about it, I solved it.

Have a good evening