Include zero's on a query

[Purfleet]

I have a list of players in my football team (table one) and large table
listing all the appearances with a date (table two).

I am trying to run a query that will list ALL the players on the left and
the number of appearance in thenext column.

I can get it to count the players easy enough but i cant get it to include
the players that have not started a game (they maybe in the subs list)

I think i need to put in a NZ function but i dont know where (i know nothing
of SQL)

Please help this database has taken me nearly a year to get this far!

Thanks

Alan

 

[Allen Browne]

If you have set up the tables correctly, you will have a Player table (one
record per person, with a PlayerID primary key) and a related table with
other information.

When you create a query, include both tables. Double-click the line joining
the 2 tables in the upper pane of query design. Access pops up a dialog with
3 options. Choose the one that says:
All records from Player table, and any matches from ...

This will give you a record for all players. If the player has never played
a game, the columns from the other table will be blank (Null). It would be
possible to use Nz() to convert that value to a zero, but that will probably
be counter-productive: a player who has not played a game (Null) is quite
different from a player who scores zero.

 

[John Vinson]

I have a list of players in my football team (table one) and large table
listing all the appearances with a date (table two).

I am trying to run a query that will list ALL the players on the left and
the number of appearance in thenext column.

I can get it to count the players easy enough but i cant get it to include
the players that have not started a game (they maybe in the subs list)

I think i need to put in a NZ function but i dont know where (i know nothing
of SQL)

Please help this database has taken me nearly a year to get this far!

You don't need an NZ (I don't think). Instead, select the Join line in
the query design window, and select option 2 - "Show all records in
Players and matching records from Appearances".

If this isn't making sense, please open your current query in SQL view
and post the SQL text here.

John W. Vinson[MVP]

 

[Purfleet]

Brilliant thanks - probally the easy question you will have today!

Thanks

Alan