Question Count/Dcount or something else?

[Lensmeister]

Hi,

Having taken a break from playing with a project I have come back to it and one of the original stumbling block has come back to haunt me.

I have searched through and although there are some posts on Football databases this question doesn't appear to be in them.

I have a Access 2003 database and in it I have a table (tbl_Players) and a table (tbl_1stXI).

tbl_1stXI has the fields ID, P1 thru to P20. ID is an auto number primary key that links other tables (Scorers and matches) together through relationships.

In players I would like to have another field that calculates the number of time a name i.e. David appear in the table tbl_1stXI.

In Excel the countif function does this perfectly but can it be done in Access and if so how? I have tried to google and search other forums etc. but nothing like this comes up.

I am going to build a VB6 front end on to this database to make it easier to use eventually, but I am stuck with this counting of players names bit.

I can provide a sample mdb if you require.

Thanks in advance.

 

[gemma-the-husky]

you mean for appearances? depending on your table names, you want to analyse the one that has the players for each game.

in general do a totals query on your teams table, which will give you all appearances for each player. then just do a dlookup for the active player.

for just 1 player

dcount("*","teams","playerid = " & whatever) will do a similar thing

 

[Lensmeister]

Hi,

every match appearance is on tbl_1stXI, and every player will be on tbl_Players. The sample only has 400 on it but the main database I use has over 3000 matches/results and over 2300 players and I need to be able to keep a running totoal so as I update the results/appearance/goal scorers/ subs it automatically updates the players totals for these.