Hey all

As you prob know by now I do my own ratings etc, I save them as a "data warehouse" type setup in a MS Access database. I am self taught re computers so I use MS Query in Excel to run form queries and ratings queries (ie past race) to rate races and to bring up the form etc upcoming. It's messy but it works for me.

My query is this:

How do I make an inter-relational (is that the right term?) DB in Access, so that I can make say Horse Name the common field so that info I save in the secondary database can be automatically brought up when I query the first DB?

Hi Duritz,

A little complicated to explain but I'll give it a shot.

I need to know exactly what you require and the field names to explain it fully, but here goes.

As your information is contained in excel spreadsheets already, you can create tables in Access and name each field the same as the spreadsheet, you would then import your current spreadsheet data into the Access tables.

So you'd have two tables for example,

1. Horses
2. Past Runs/Form

You then create a relationship based on the horse name fields in both tables.

Then create a query that pulls the horse name from the horse table and the data from the second table in date descending order.

So your query has the runs in descending date order (most recent run at the top of the table).

I know this is a little sketchy, but it's hard to explain fully without more info.

If you'd like me to make up something for you, I can do it if you supply EXAMPLES of what you're trying to achieve. You could send me dummy info, so you don't reveal your secrets, and tell me which version of Office you have too please.

racestats at hotmail dot com

__________________
RaceCensus - powerful system testing software.
Now with over 413,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/01/2025
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

OK I know what you're saying - I will explain more fully tomorrow.

Thanks.

OK see what I have got is this:

First table, into which I paste all my data of races gone by. Essentially, it contains columns like horse name, weight, distance, going, rating etc and heaps others. Basically, for a meeting gone by a race might look like the example below (race example)

So then I paste that info, plus other columns containing other equally boring stuff, into the Access DB, so then basically there are heaps of individual lines of info.

Then when I query for an upcoming race it will query by the horse name and return all that info, see.

What I need is to have another table, lined to the first one by horse name, where I can add other info like a general comment about the horse (timeform style) and age, breeding, prizemoney, these kind of details that wouldn't be included in the race example above. Then when I query in excel it can query both tables and return the different sets of info.

Thanks.

Attached Files

race example.xls (16.5 KB, 642 views)

Duritz,

Attached are two excel files:

One for Horse.xls (contains the Horse history and additional comments which should be imported to the access table Horse.dbf)

One for Race.xls (contains the raceday information which should be imported to the access table Race.dbf)

One Access file:
Has a query built which pulls the information for today's race AND comments about the horse's last run etc.

You can add, delete or rename fields in the spreadsheets accordng to your requirements, but you must then modify the table field names and the corresponding query.

It's not perfect as I'm flatout and did it in a hurry, but it will get you started as an example anyway.

Attached Files

Desktop.zip (21.4 KB, 595 views)

__________________
RaceCensus - powerful system testing software.
Now with over 413,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/01/2025
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

CP - how do you get that auto sort thingy at the top of each column?

thanks - Chuck

Chuck,

Select the whole sheet by clicking in the top left hand corner and go to
top menu:
Data / Filter / Autofilter

__________________
RaceCensus - powerful system testing software.
Now with over 413,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/01/2025
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

Chrome you're a LEGEND, thanks. Much appreciated. May all your bets salute.

thanks again mate...and i can see your neering the 1000 mark. good onya

OK now I get how to link them properly.

You see I already have a DB where I have been putting all the race data for quite a while now, the Horse DB is the one I have been needing, basically as we said for general comments re a horse etc.

This is my query - what if now when my query in Excel queries the two tables, one horse is not yet contained in the new Horse DB? Example tomorrow's race Alinghi is of course in my race DB but I have just designed the Horse one and there is as yet no info.

Won't this mean it will return no info for Alinghi at all?