Database help - MS Access
 

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

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.

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.

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

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?

Duritz,

You are correct.

To fix this issue, open up the access query in design mode:

Right click the relationship (join line between the two tables) and select join properties. Then select show ALL records from table race, and only matching records from table horse. That way if there is no matching horse data, Alinghi won't be missing from the query, only the comments field will be blank.

Thanks - however the query itself is in Exel, using MS Query, rather than in Access.....

Hi Duritz!
I am sure you cannot use a table link in Access to give the same links in Excel unless you use a query created in Access and then opened in Excel.
Also you have to be careful if you create a link using only the horse's name, it will pick every record with that name regardless of date race num. venue etc.
You really would have to go back and redesign your tables with well defined primary keys, and avoid using the same field names in different tables, it will cause errors because the database engine does not know whether you meant hName in Horses or hName in Form?
It is very easy to get results that are inaccurate.
Also when you query past races you have to be careful that only the form parameters prior to the race date are being used!
Sorry if I confused you, if it doesn't help just forget it.
I use Access extensively but write my own front end applications to access the data.
Cheers!

Thanks lomaca. In fact, I don't really have a prob with the way my data is returned to excel via my MS Query queries, as when I do the form I WANT every record returned, and I have made the query it runs through in Access sorted in date order, so that when the MS Query accesses the records they are sorted that way. Essentially, I end up with every run of the horse in the database, from most recent back to day dot, which is how I want it.

What I need is this:

To be able to have another table to which I can add other odds and sods about horses, so that I can extract this info also. Say I want to put a general comment about a horse, I need somewhere to put it where it can be retrieved in a query in excel. Or the horse's age, or prizemoney won so far, or what it's favourite colour is - stuff that doesn't come in normal results which are the meat and two veg of keeping the normal "race" DB up to date. This new DB - horse - I would also like to only be allowed to have one line for each horse, and should I want to update the info on a particular field of that horse, how do I do it without eliminating the other info?

Know what I mean?

Hi Duritz,
I'm a bit puzzled as to why you're not querying your Ms Access tables in directly MS Access. Why use a middleman, Excel?

'Cos I do the form in excel, so rather than typing in horse's names individually in an access query, I paste them into excel and hit refresh, and it queries all of the runners at once.

Is there a way I can do that in access?

(I should say, if my methods seem odd, I am self taught on all this DB stuff....)

Duritz,

That's why I set it up to have a race table and horse table and then a query in access.

What you can do is this:

Import your spreadsheet with race information to the race table.

Now run the access query and export the results into excel.

You now have the race info and corresponding horse comments on the one spreadsheet without using excel queries.

In this particular instance you don't need primary keys either, as you want all comments returned for a particular horse.

OK I must be missing the point though.

For an upcoming meeting, say Flemington today, how do I get it to query for all the runners running there at once, so that the results returned are the history's of all runners running there today?

Because if I have all my records of all horses etc in the race database and run that query you sent me, won't that just return me all records of all horses in the DB?

Sorry if this is a nuisance.

Not a nuisance Duritz, not at all.

Just import todays race field into the race table, run the query and it will show all past info and comments for horses running today, but not others not running, that's the purpose of the horse relationship.

Hey thanks all for the help so far. I haven't ended up modifying my current method, as I don't think I've explained the way I have my eclectic method set up properly yet. What I have done for the comments about the horses is hijacked a column I wasn't using and have made it a "general horse comment" column.

Thanks, though, I clearly have heaps to learn re programming. I would LOVE the time to learn it, it seems fascinating.

Duritz.

Duritz,

Whatever works for you is fine.

In fact, many more proficient programmers frown upon my way of doing things, as it's not "normal", however, it works for me, and I don't have the time to learn all the intricasies - as long as the result is what I'm after, then I'm happy.

Feel free to ask later if you have questions or ideas.