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.

__________________
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

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.

__________________
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 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.

__________________
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