Access and Sequence

[arkansasman]

I am trying to sequence past races in a horse's past performances. When I run a query using SQL, the query will only show 220 records. I am querying Brisnet Drf3 file and I have 457,000 records in this table. has anyone ran into this problem and if you have, do you have a solution you could share with a poor soul? Or - does someone have an easier way to query and sequence the Drf3 file and would be willing to share that with me?

John

[BIG49010]

Do you mean 220 columns? Not records they should be 1 record at a time?


I use a program called RR report writer, I can look up address for you, but it is rather pricey. It allows you to right a report that connects the Drf-1,2,3 and 4 files, plus anything else you want and isn't limited to 220 columns. I had this problem years ago, and this was how I solved it. You also may have to go to another ODBC driver besides Mr. Softy's jet. You can make a very custom racing form, but takes a while to set up.

[singunner]

I'd heard of a hack that extends Excel/Access's base limits out towards infinity. I'd suggest you Google for an Access forum and run a search there.

[arkansasman]

I guess I should have said 220 rows.

John

[BIG49010]

http://www.livewarepub.com/default.htm

This is link for Report Writer, this was the only way I could make a report that looked similar to a racing form, when you have 4 files with over 800 fields (rows). This is a good product I have used it for 15 years.

Good Luck.

[sjk]

John,

Perhaps you can make it clearer as to what you are trying to do for those of us that have no idea what a Drf3 file looks like.

[arkansasman]

Below is an example of 2 horses in Drf3. I know you will probably know these, but TR is Track, Date is the date running, R is Race, P is Post, Prev is the Previous race dates that Horse A and Horse B have run. Horse A has 3 rows of data, hence 3 past races and Horse B has 5 past races. The Seq is the sequence I am trying to get. 1 would be the first race back, 2 the second race back, etc. It looks like you could sequence these with a really easy query, but I have not found one yet. One could even rank Prev and get the same results.


TR-----Date----- R-P----Prev-----Name----Seq
"GP ","20070113", 1, 1,"20060309", HorseA,--1
"GP ","20070113", 1, 1,"20050813", HorseA---2
"GP ","20070113", 1, 1,"20050710", HorseA---3
"GP ","20070113", 1, 2,"20061215", HorseB---1
"GP ","20070113", 1, 2,"20061116", HorseB---2
"GP ","20070113", 1, 2,"20061013", HorseB---3
"GP ","20070113", 1, 2,"20060907", HorseB---4
"GP ","20070113", 1, 2,"20060722", HorseB---5

This is what I am trying to resolve. I hope I explained this well enough to understand.

Thanks,

John

[sjk]

What you want to do is to create a new table with all of the same fields (TR, Date, Name, etc) as the one you have but also with a new field with an index with autonumber type. Then run an append query appending your original table into the new one sorted first by name then by date.

Define a query on this new table where you group by name and find the min of the index. Then you can make a query which includes all of your original fields and also the one which calculates the minimum of the index and calculates the seq as the index of the field less the minimum of the index +1.

[AwolAtPA]

if this is an issue with number of RECORDs

then this thread from HTR forum may help

http://www.homebased2.com/forums/showthread.php?t=6089

duane

[BIG49010]

You should be able to sort in decending order or asending order, it is very simple in Excel, I don't know about Access.

[K9Pup]

Below is an example of 2 horses in Drf3. I know you will probably know these, but TR is Track, Date is the date running, R is Race, P is Post, Prev is the Previous race dates that Horse A and Horse B have run. Horse A has 3 rows of data, hence 3 past races and Horse B has 5 past races. The Seq is the sequence I am trying to get. 1 would be the first race back, 2 the second race back, etc. It looks like you could sequence these with a really easy query, but I have not found one yet. One could even rank Prev and get the same results.


TR-----Date----- R-P----Prev-----Name----Seq
"GP ","20070113", 1, 1,"20060309", HorseA,--1
"GP ","20070113", 1, 1,"20050813", HorseA---2
"GP ","20070113", 1, 1,"20050710", HorseA---3
"GP ","20070113", 1, 2,"20061215", HorseB---1
"GP ","20070113", 1, 2,"20061116", HorseB---2
"GP ","20070113", 1, 2,"20061013", HorseB---3
"GP ","20070113", 1, 2,"20060907", HorseB---4
"GP ","20070113", 1, 2,"20060722", HorseB---5

This is what I am trying to resolve. I hope I explained this well enough to understand.

Thanks,

John

To just sequence the past races by Horse you can write a simple user function to assign a sequence number to each race. The function gets passed the horse name and will restart the sequencing when the horse name changes from the last one. Here is the code for the function. (I will email you the complete solution to your problem).


Function raceseq(Horse As String) As Integer
Static lasthorse As String
Static temp_raceseq As Integer
If Horse = lasthorse Then
temp_raceseq = temp_raceseq + 1
Else
lasthorse = Horse
temp_raceseq = 1
End If
raceseq = temp_raceseq
End Function

[arkansasman]

Thank you both!!! I got the sequence to work. Now, its time to build a new and hopefully better logit model.

Again, my sincere thanks to both of you!!!!

John