Help for a newbie please

[blogger153]

Hello, I am pretty new to Access. I have used databases before and even created some very basic ones many years ago, but new to relational databases.

Currently I use Excel to store my data, but as this has grown, I am regularly told that I should move my data to a proper database.

The data is in the form of results for greyhound racing in Australia. I store the results of all races, performing a number of calculations on those results to return some "performance ratings" for these past races. I then use another worksheet to filter data from these past performances and perform more calculations to get "predictive ratings" for upcoming races. Over the years I have developed a number of formulas and macros to do these calculations and automate the process as much as possible (when I started many years ago I kept the information for each dog on 8 X 5 cards all hand written and all the calculations done in my head or on a calculator.

I have been trying to learn the basic concepts of Access for this task (pity there wasn't some sort of template available that I could see what others have done). From my early learning, it would seem that I should have different tables for different types of data, eg:

Table 1 for the dogs with their name and I'm not sure what else.
Table 2 for the tracks, with track name, different distances, type of track, box statistics
Table 3 for the results, with the race results.

I am a bit lost with a couple of things.

How to break up the data into these various tables and then how it all comes back together again when I want to run queries.

Also, how can I input new data in a datasheet format (I currently get the results as a .csv file with each record as a line of data).

I have attached an image as a sample of the results that I collect and store just to give people an idea of what data I get and maybe some advice on what tables I should use and how to set up the data storage side of things first. I will then need to figure out how to run all the queries I need later on.


Thisis the typical results for a race, with the columns in grey and red some calculations that are used.

Sorry for being a lost newbie, I would appreciate any advice to get me started.

 

[gemma-the-husky]

I have a number of theories about horse racing, but was unable to find a shedload of data I could plug into a database.

the data you need is roughly this. if you find you need to track other stuff, such as trainers, or you don't quite follow the structure, pm me. Happy to explain in general terms.

dogs (dogid, name, dob, etc)
tracks (trackid, location, etc)
meeting (meetid, trackid, date, going)
races (raceid, meeting, raceno, distance, grade, going, prize money)
(note - not sure whether going is best stored in the meeting data or the race data - depends if it might change during a meeting - simpler to have it the meeting ... )
runners (race, dog, dogweight, trapno, position, distance behind winner, odds, etc)

 

[blogger153]

I have a number of theories about horse racing, but was unable to find a shedload of data I could plug into a database.

the data you need is roughly this. if you find you need to track other stuff, such as trainers, or you don't quite follow the structure, pm me. Happy to explain in general terms.

dogs (dogid, name, dob, etc)
tracks (trackid, location, etc)
meeting (meetid, trackid, date, going)
races (raceid, meeting, raceno, distance, grade, going, prize money)
(note - not sure whether going is best stored in the meeting data or the race data - depends if it might change during a meeting - simpler to have it the meeting ... )
runners (race, dog, dogweight, trapno, position, distance behind winner, odds, etc)

Thanks for the reply. There are some suggested fields that I would not use (eg dob, dogweight, probably even going). I had tried to work out what tables to have and what data to put in each table, I have attached my plan so far. What I don't know is how to set up the input "form" which I would love to be as a datasheet because I download results as a csv file and then do some formatting to that. If I could use that same csv file to input the data into a new Access database, that would be great.

The other thing that I don't understand, and this shows just how much of a newbie I am, is how the data gets from an input form or datasheet, to the individual tables and then how, when I want to run my set of queries once I figure out how to do that, how that data is all "assembled" again to something that looks like a ratings sheet.

Oh, and you mentioned needing data to put into a horse racing database to test.......I know the dog data is different but I think has a lot of similarities you might be able to use. I do have about 800,000 rows of data in my Excel sheets. The only thing that you would need to add for horses would be jockey and trainer info, as well as those things like age and prizemoney that are far more important to horses in determining class of races, etc. But the dog data just have different tracks, distances, classes, grades, boxes (which have an impact like barrier positions) and sectional times.

Anyway, I would be grateful if you could look at the attached table layout and comment and advise.

 

[gemma-the-husky]

in your table racedata, you have a repeating group "racedate" and "trackid". This is incorrect normalisation, which is why I suggested putting these into a meeting table. you could add "going" to this (depends whether you want to try and adjust times for going changes)

you have distance in the track record. surely you have different length races over the same track?

if you want to store other details about the races on a card - eg, grade, prize money, distance, time, race number etc, then you need a table for this also

other than these ideas, we basically have the same tables.

maybe your dog table needs sex, dob, (maybe date in heat for bitches), designated wide runner, etc .

-----
when you get your race date, I would import it into a temporary table, and then run a series of queries to populate your tables

so -
identify new dogs, and add them to your table
identify new tracks, and add them to your table
identify new meetings, and add them to your table
identify new races, and add them to your table
add the runners and results to the runners table

then use your normalised tables to extract whatever data you want.


--- if you enter data manually - then you
a) manually enter into a form the race meeting details - select a date, and a track

b) then have another form (maybe a subform) to enter the race details for each race at the meeting

c) then have another form (again maybe a subform) to select the runners, and record whatever finishing details you need.

don't worry too much about fancy presentation. just get the data sorted, and the forms working correctly

-----
wrt to horses, I would have liked to get a stack of data similar to what you have, to load into a database - but I struggled to obtain the data.