Access and Brisnet

[arkansasman]

I am in the process of learning Access and I have ran into a Query problem. First of all I can query and get data from all the Brisnet (multi past performance comma delimited files) correctly except File 3 (3rd of 4 files).

I am analyzing data in Excel, so I need each row to be one horse with each column in that row to be data from the horse's past. In Brisnet file 3 the data in the past performances is in rows. Example - If a Horse has only 3 races in its lifetime, there will be 3 rows of data in File 3. If a Horse has 10 or more past races, there will be 10 rows of data in file 3.

I am trying to get the data from Brisnet file 3 from rows to columns. Example - Horse A has 3 past races, so consequently Horse A has 3 Brisnet speed figures. Instead of those speed figures in row 1,2, and 3, how do I get those figures to row 1 columns d,c,and e?

If I only had one horse I could do that with a Crosstab query, but with 5000 past races, this is not possible.

I know someone out there knows how to accomplish this and if you can help me with this, I would certainly appreciate your help.

Thanks

[Tom]

Not sure if this is what you are looking for, but <select> the data you need, then <copy> , then <paste special> and check off <transpose>. That will change row/column orientation.

[K9Pup]

I am analyzing data in Excel, so I need each row to be one horse with each column in that row to be data from the horse's past. In Brisnet file 3 the data in the past performances is in rows. Example - If a Horse has only 3 races in its lifetime, there will be 3 rows of data in File 3. If a Horse has 10 or more past races, there will be 10 rows of data in file 3.

I am trying to get the data from Brisnet file 3 from rows to columns. Example - Horse A has 3 past races, so consequently Horse A has 3 Brisnet speed figures. Instead of those speed figures in row 1,2, and 3, how do I get those figures to row 1 columns d,c,and e?


Thanks
I'm not sure why you WANT the data arranged that way, but here goes. This solution assumes you have a seq# assigned to each past race for each horse. If the data doesn't contain this seq# then you would need to add that (using a user function).

Using the IIF function "create" fields in your query for each past speed figure up to some max past races. Call these SF1, SF2, etc. The formula for each would be

SP1:IIF(raceseq#=1,speedfigure,0)
SP2:IIF(raceseq#=2,speedfigure,0)
etc etc etc

Then change the query to a totals query. Do a group by on horse and MAX on the SP1, SP2, etc. fields. If done correctly you will end up with one row per horse with speed figures 1-x.

You can also look at Microsoft's solution here http://support.microsoft.com/default.aspx?scid=kb;EN-US;283875

[BIG49010]

I'm not exactly sure what your trying to do, but I think you will run into a problem with the 255 col. restriction, that is why bris has constructed the files the way they have. The new Excel, which if you look up some other posts to find Beta addition, might be able to do what you want.