Acess Novice needs help

[CharlesB]

I’m sure this is very simple for you pros, but I’m mystified. Can someone tell me how to whittle down a query to identify a discrete group of data.

Here’s my dilemma:

I’m doing a trifecta study on a number of angles, but I need to know how to make one more reduction in the data.

For example, if I want to analyze how often K1, K2 and K3 score in a trifecta,
I performed a query with rKrat and Xfin. I had rKrat “1”and X Fin “between 1 and 3”. I did the same with rKrat “2” and rKrat “3”.

When I run the query, I still get a number of races where rKrat “1” or rKrat “2” ran 1, 2, or 3, but not rKrat 3.

I can move the data in Excel and manually identify the races where rKrat ‘1” “2” or “3” were in the trifecta, since the spreadsheet shows 3 numbers in a row. That is a pain in the neck, of course.

Is there another command I can use in Access that will identify only the races where rKrat 1, 2, or 3 are in the trifecta?

[Donnie]

I beleive if you do a Total Query...hit that button on the toolbar that looks like a Greek E ...then the criteria will be "1" OR "2" OR "3" and do a COUNT of the times it finds any one of those.

Hope I understood your question properly...if not post back. Never done a Total Query...enter that into the help section. Post back if you get lost!

[CharlesB]

Donnie:

Thanks, but that doesn’t work.

Here’s an example of what I’m getting when I do my query of rKrat 1 and Xfin “between 1 and 3”.

tTRK tDATE rKrat xFIN nEXA nTRI
ALB 9/26/2004 1 2 $36.80 $91.80
ALB 9/26/2004 1 2 $12.00 $77.60
ALB 9/26/2004 2 1 $12.00 $77.60
ALB 9/26/2004 3 2 $12.00 $77.60
ALB 9/26/2004 1 1 $19.60 $102.20
ALB 9/24/2004 1 1 $12.00 $75.00
ALB 9/24/2004 2 2 $12.00 $75.00

As you can see, on 9/26/04 there was one trifecta in the group.

I want to be able to narrow down the query so that the database only identifies the trifecta, and leaves out the other races.

Once I move out the other races, I can then take the data into Excel to run some statistical samples and profit/loss calculations, but I need to weed out the chaff first.

This is the mysterious query, I’m looking for.

[Rick]

Use [nTRI]>0

That should give you all the races that show trifecta payout.

[CharlesB]

Ricks:

nTri > 0 still results in the additional races shown in my reply to Donnie.

[Rick]

If I was doing something like that, that is still one of the things I would want to know - how many tris races there were in the group.

Then I would want to know how many fell into each of your categories.

Then I would want to know how many had all three.

Then I would want to try and figure out if it was profitable and where.

[Rick]

You have figured out what you want.

Now sit down with pencil and paper and go backwards to figure out what info you need to get the answer you want.

It would not surprise me that you will have to do a separate query for each step and then a final query to tie it all together. It could easily take 5 to 6 simple queries to do it.

[MikeDee]

Charles I would do it with 3 queries.


The first query finds the races where K1 finished 1 2 or 3
in the first query use
date,
track
race

K=1
xfn = 1 or 2 or 3

save this query.

2nd query
use your hx4 table and join it to the first query with the fields
date, track, race,

by doing this the second query is only looking are races where k1 has finsihed 1,2 or 3.

put the following fields in this query
date
track
race
k=2
fin = 1 2 or 3

save the query

3rd query
make a new query with hx4 and join it to the second query with date track and race, jsut like the previous one.

With this query and the join to the second query are only looking at the races where k1 or k2 have finished 1 or 2 or 3

In this query you set k =3 and fin = to 1 or 2 or 3 and you are looking at only those races where k1 or k2 or k3 have finished 1 or 2 or 3.

These are tri winners you are looking for.

You can join tables to queries, just like joining tables to tables. Joining tables is coverd in the user guide, if you have any trouble post back.

[CharlesB]

Mike:

Thanks! :) Your solution worked like a charm.

For some reason, when I followed your steps, the additional queries only pulled up the rKrat being examined by the specific query. So, I added one final query linking the first three queries together. It had the sole parameter of rKrat <=3.

That put the first three together into one fantastic time saver.

Thanks a million!