Question Count if text and date fields are all the same

[Gladman]

Hi everyone,

I am new to using access but have managed to build a database that I use to find qualifiers for horse racing based on stats I import for all the racers in the days racing. I have a query which shows the date, time, track, horse and trainer but some races have more than 1 selection so I want to add a field that counts the number of qualifiers per race. I am finding it hard to do a countif, date AND time & track are all the same within that query.
Any help would be greatly appreciated.
I also want to add another show the total runners in the race but this will have to look into the tables where the query is run from. Hopefully I can figure this out when I find out how to solve my original problem.

 

[plog]

date, time, track, horse and trainer but some races have more than 1 selection[

I have a general knowledge of horse racing, but what's a selection? For your issue, can you post some sample data? Include 2 sets: starting data and then what results should be produced from that starting data. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 4/5/2006, 14
Larry, 6/7/2009, 51
Sally, 3/13/2013, 44

 

[Gladman]

The selection is just the horse that meets my criteria for the stats. If there's 12 runners in the race there maybe 2 horses that meet my criteria as possible bets (sometimes there's none but that can't be helped.
I have 3 tables (copy and pasted from the racing post site);

1 = TrainerStats
Date, Trainer, Wins, Runs, SrikeRate, Profit, Track

2 = HorseStats
Date, Horse, Race(Time), GoingRuns, GoingWins, GoingStrikeRate, DistanceRuns, DistanceWins, DistanceSR, TrackRuns, TrackWins, TrackSR, Track

3 = Signpost
Date, Horse, Trainer, Race(Time), Miles

All the same named fields have a relationship connecting them and a few queries to filter the trainer / horse stats and my master query where I change the date and export 'todays' qualifiers to a spreadsheet.
What I want is another field, in the master query, to count how many times the date AND time AND track are all the same within that query.

Example,
Date, Horse, Race, Track, Selections per race(Countif expression)
01/02/03, Speed Steed, 3:30, Epsom, 2
01/02/03, Ralphy Boy, 3:30, Epsom, 2
01/02/03, Dancing Sally, 4:00, Epsom, 1
01/02/03, Moviesta, 4:20, Redcar, 3
01/02/03, Token Of Love, 4:20, Redcar, 3
01/02/03, Multi Bene, 4:20, Redcar, 3

 

[plog]

I need to see how the Selectiosn per race are determined, so I need sample starting data and then what you expect. You can't just give me ending data without allowing me to see from what data that was determined. So, post sample starting data and then what you expect as results from that sample data.

 

[Gladman]

Well that's a little complex to give it all but I have a going query where I have a minimum strike rate and award points based on the numbers, same for distance and track. All these are summed in another query and my master query uses max and pulls the date, time and text (Horse / Track fields) If there are 2 leaders in the points awarded there are 2 selections. All I do is import my data, change the date filter in my master query and the days selections are shown. There's a dozen queries that aren't edited but used to calculate the points awarded. I can't really see why this is important to my question and I know there probably is a simpler way to do it but I'm just learning how to run a database but want to add this count column. I've been trying to add another query to do it but have had no luck.

 

[plog]

I can't help you get to Paris if I don't know where you currently are. You can explain about needing to see the Eiffel tower, visiting the Louvre and how you're going to take a Vespa 4 blocks past the Arc D'Triumphe on the Champs Elysee to this great boulangerie all you want, but if I don't know where in the world you are now its all moot.