Help for Access 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.

[ssanfu]

It looks like you have a good start with the tables. Need to develop field names. Post back with your table structure and field names for review.

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.

Here is a tutorial to help refresh you on relational databases:
http://www.rogersaccesslibrary.com/forum/forum46.html

[blogger153]

I was looking at a structure like the attached. My limited knowledge will let me set up these tables, if that is the best structure, but how do I then get the data into the tables on a daily basis, from my csv as per my original post. I was hoping there was a way to have a datasheet interface that would allow me just to copy and paste from the csv file, and then that would post the info to the various tables..........but I have no idea how that works and that where I start to get lost.

[ssanfu]

Looks good. One thing I would change is that the PK for all three tables is "ID". Gets kinda confusing...
I would name the fields

table Dogs
---------
DogID_PK (or Dog_PK)

table Race_Data
----------------
RaceDataID_PK (or RaceDataID or RaceData_PK or RaceID_PK or DataID_PK or ...)

table Tracks
--------------
TrackID_PK (or Track_PK)


I use the "_PK" suffix (and "_FK") to help me (and other programmers) know which field is the PK/FK when looking at SQL or queries.



As for the datasheet..... nope, there is no way to cut and paste. Tow options: create a form/sub form to enter data or to use VBA to import the CSV file.
What does the CSV file data look like?

To import the CSV file you would:
1) read in the first line of the CSV file
2) parse the line
3) convert the dog name to the PK number
4) convert the track name to the PK number
6) do any calculations
7) append the data to the "Race_Data" table.
8) loop until all records have been read.

Regarding steps 3 & 4. If there is a new track or a new dog, the code could add the track name/dog name then continue on.
Not too hard.