Stuck on a list of big winners vs Chalk losers

[Lee Carson]

How do I write this;
I want a listing of Date, Track and Race#
From say, 8/1/04 through 11/15/04
I want those races where the winners were above 6/1, where the chalk <2/1 lost
Lee Thanks

I get hung up on how to deal with xfin and xwin fields.

[Donnie]

Lee-
Do one query that finds all AODDS <2/1 AND XFIN <>1. This gives you all horses under 2-1 that did not win. Then do a link with this query to a new query where the AODDS >6 AND XFIN = 1. When you link these two queries together you will get only races where the 2-1 horses didnot win, but the winner was over 6-1. NOTE: you may have more than one horse who was less than 2-1 who diod not win within one race so your final query may throw duplicates at you!

[Lee Carson]

Thanks Donnie - now how do you link queries? this is truly access 101
Lee

[Donnie]

open your query grid and write your first query. Save it back. then when you start your new query, open first your HX4 table, then switch to the query tab in the Show Tables box. Once you have the saved query and the HX4 table showing on the screen, drag and drop trk to trk, race to race, date to date, and tPRG to tPRG. these 4 fields are needed to prevent duplicates. Check the Access manual Mike wrote and put on line here.

[Lee Carson]

Donnie,
@@#$&^$#* that's the least of what I would care toi express - OK 1. Wrote and named the first query.Saved it.

Before I began writing Query#2, I opened the HX4 table, then I opened Query #1 and I was supposed to link the hx4 with query #1 TRk,Date,Race,PGm - OK so far.

Then I wrote query #2 AND? Hooked Query #2 to Query #1 Right?

So where is the List of winning horses with losing chalks combined?

In my screwwing around with this, I managed to entangle the whole mess into a bunch of circular ref's

Donnie, How far did I get it right? and where did I screw up? and How to get the listing I'm after? Help! my Atavar is killing me!
Lee

[Donnie]

And you exported after the results were in so you actually have data in the AODDs field? First check to see if there is data in your table and that the AODDs field is populated.

[Lee Carson]

Nope. didn't export. So I need to export and then build then run - list shud appear in #2 query in view mode, rite? lemme go back and fit - will let you know
Lee

[Lee Carson]

OK believe I did everything correct. with HTR2 exported. the Oct period -. between 10/1/04 abd 10/31/04. Ran the Build button. Trashed old stuff and wrote #1query.saved as "chalk". before writing #2 query opened hx4 table and #1 "chalk" and linked the 4 items. Wrote the #2 query, saved as winners. Then?? opened another NEW query and brought HX4, Chalk query and Winner query together with show table. Noticed I lost the original links between HX4 and "Chalk", so reestablished the 4 links , then linked Winner query with Chalk query. the name "query" was NOT included. Just chalk and winner.

Then got a message: Query must have at least one destination field. when I opened either chalk or winner-???

Still stuck Lee

[Rick]

If Donnie doesn't get back to you soon you can email me your queries.

Just open them in SQL View, copy and paste to the email and email to ricks231@cox.net

Hands on is the only way I can figure them out.

[Lee Carson]

Thanks Ricks, got it figured out.
Thanks,Lee

[hurrikane]

I believe there is a rAODDS field that will give you the chalk.

rAODDS = 1
nAODDS <2
xFIN <> 1

should get rid of the duplicates

[MikeDee]

I like to use a table to select a sub set of races to look at. I don't know the true definition of a pivot table, but this works for me. This concept will work for any condition one wants to set for the selection of races.

First make a new table from scratch. Put 3 fields in it
tDate as a date
tTRK as text
nRACE as number (integer)

Make the 3 fields key fields (highlight all 3 by holding the crtl key down and clicking on each one with the mouse) Next click on the key icon. This makes the 3 fields key fields.

Now go to the first query. This is the query you will use to select the sub set of races to be analyzed. First put the all_hx4 table in the design and the fields tdate, ttrk, and nrace.

In Lee's example put the following in the design.
xfin <>1
naodds <=2

Once this is done go to the query type and select "append query" Access will ask you what table do you want to append the data to. You select the pivot table that you just built.

When you do this you will note a new row heading has been added to your design named "append to" If you built the field names correctly in your pivot table you will see them inserted in the tdate, ttrk, and nrace columns of the design. Access is telling you that it is going to append these data fields to the pivot table. There will be nothing in the other columns of your design because Access did not find any matching fields in the pivot table. This is how you want it to look.

Next run the query. An append query is a action query it does not generate a output you can look at. So when you run it there is nothing to see. Depending on the filter you build you will most likely have duplicates. The one we are talking about here could generate many races where more then 1 horse is less then 2-1 and did not win. When access tells you there are duplicates, do you want to run the query anyway, you answer yes.

The pivot table now contains all races in your all_hx4 table where the winner was not one of the top 2 off odds favorites.

To look at the sub set of races you make a second query. In the design you put the all_hx4 table and the pivot table. You join the tables with tdate, ttrk, and nrace. You can now do any and all analysis on any fields in All_hx4 while only looking at the races that are in your pivot table. When finished you can delete the contents of the pivot table and it is ready to be used again for a different test.

The advantages of this approach over using queries on queries is that duplicates are automatically eliminated, and any query in your db can be changed to an append query to populate your pivot table.