Access Help

[Tom]

I have a db with one line for each horse and various data points about that race. What I want to do is have an output that -on one line - shows horse, distance, total number of E horses, total EP, total P, total S and Total R, plus winner's RS and payout

Date-Track-RN-Dist -Horse- #E - #EP - #P - #S - #R - Winner's RS - $$$

2/27 Aqu R5 6Fur OldPaint 1 3 3 1 0 P #6.80

What I want to do is lok ar various pace pictues by how many early horss are in the race. One I am looking at is 1 E horse,1 EP horse, and any other mix. My theory is the E will win or one of the Ps, the EP being used trying to keep up with a lone speed.

I've grouped by track, race number and distance, and put RS field in 5 times, but cannot get them to count by the grouping

Thanks

[PaceAdvantage]

I moved your thread to Handicapping Software 'cause I think it fits better here...we'll see...

[Tom]

Thanks!

[Wiseguy]

I'm no expert but couldn't you just create new RS fields and then use the Count(*) function with the specific match criteria? For example, create new fields with one for each type of Running Style: RS_E, RS_EP, RS_P, RS_S. Then for each field use the Count function to simply display the total number of records from the designated date/track/race that match that Running Style? By the way, what's the Running Style "R" that you mention?

[Tom]

Thanks, Wiseguy.....R = rear.

I think I have it.....trying it out now. I wrote a seperate Query for each style, then I am writing a summary query and taking the count of each from the several q's. Looks like it might work. I mis-typed something yesterday - my data is in 1,2,3,4,5 for values and I kept putting in F,E,P,S,R and of course, no data could be found! D'oh!

[BillW]

- my data is in 1,2,3,4,5 for values and I kept putting in F,E,P,S,R and of course, no data could be found! D'oh!

Use the "doasImeannotasIsay" function. :lol:

[Tom]

Woo Hoo!
Got it to work. Problem was putting the main table first - it had to go last, after the 5 querys to collect the count totals.

[BIG49010]

Tom,

I just put together the same basic data you describe in a database. I have
a sample of 15,000 races, anything I can help you with?

[RonTiller]

Tom,

There's an easier way to do this in Access, in one query - we do it all the time.

Do the GROUP BY however you want. Then instead of doing a COUNT for each running style, in 5 separate queries, then pulling them together in another query , do this:

Add these fields after the GROUP BY fields, and set them to EXPRESSION (rather than COUNT)

Sum(IIF([RS] = "RS_E", 1, 0)
Sum(IIF([RS] = "RS_EP", 1, 0)
Sum(IIF([RS] = "RS_P", 1, 0)
Sum(IIF([RS] = "RS_S", 1, 0)
Sum(IIF([RS] = "RS_R", 1, 0)

In one query, this will give you the count of all the running styles by whatever GROUP BY you are doing. This also works in other SQL syntaxes - you just replace the Access IIF statement with a CASE statement. This saves A LOT of time and reduces the query count dramatically.

Ron Tiller
HDW

[Tom]

Thanks Ron..that looks a lot easier -will try it out.

Big....I'm interested in a specific race shape on the Aqu inner track.
1 early horse, 1 early presser and any number of pressers/sustained.

[BIG49010]

Last year at Aqu Inner I show 36 races with 1-E and 1-E/P.

3 - E Winners
10 - E/p Winners
13 - P Winners
5 - S Winners
5 - NA Winners

Sorry I did stats for the Inner only last year, I have GP, CD, Bel, SAR, KEE.

I did learn something though, that I will share. It appears Bris changes the
style of the horse after each win or good performance.

[Tom]

Thanks, Big.....shoots my theory full of holes! :eek:

[BIG49010]

All races in database see if this help?

863 with 1 - E & 1-E/P

131 E winners
162 E/P winners
253 P winners
184 S winners
133 NA winners

The thing I find interesting is that you would think with lone E or only E win a higher percentage then 15%