A Relationship problem in Access
Hi guys!
I am creating a database (obviously) for a cricket game and I have currently got 5 tables - each share the same ID - Player ID. I have then tried to establish a relationship between a table that I named "Personal Details" and the other 4.
What I wanted to happen was that the data from the other 4 tables will be viewable on the Personal Details table when you expand. Instead, I can just view one table at a time when you expand - and you have to select this from a menu beforehand.
The Join Type that I currently have is:
"Include ALL records from 'Players - Contracts' and only those records from 'Players - Personal Details' where the joined fields are equal."
I don't know if there is a better way of doing this, hopefully you guys can help!
Thanks
JeevesBond posted this at 12:13 — 27th December 2003.
He has: 3,956 posts
Joined: Jun 2002
Expand what? When you are viewing the tables?
Timewell posted this at 16:46 — 27th December 2003.
They have: 344 posts
Joined: Jun 2002
I've taken some screenshots, that are attached below. Firstly, see Ex1.jpg.
As you can see there is a "+", you are able to click on this. When you click on this, it brings up a box (see subsheet.jpg). You can choose which of the tables you want but you cannot choose all of them. So when you click one you get expanded.jpg.
What I would like is that they are all viewable under the Personal Details thing at the same time, not one at a time.
If you could help, I'd be grateful!
Thanks!
no1golfpro.co.uk - High quality golf equipment at competitive prices.
JeevesBond posted this at 21:05 — 27th December 2003.
He has: 3,956 posts
Joined: Jun 2002
Well, I just re-created what you're doing with 3 tables (one-to-many relationship from the first to the second then the same from the second to the third) and it worked fine.
Although, I do use the Lookup Wizard to define relationships in most tables...Access automatically creates a query for this, so I would imagine that's why it works. Am I correct Bob?
If so, you can use the Lookup Wizard to easily do what you need - good old Microsoft; so easy, until you need to do something useful
a Padded Cell our articles site!
Timewell posted this at 22:49 — 27th December 2003.
They have: 344 posts
Joined: Jun 2002
Hmm...when I created a query but no data appears at all. I've clicked the Show button for all of the things I want. I am combining two tables, but that is possible.
I don't know if it isn't because I haven't specified a criteria. I've tried that but the Expression Builder just p*ssed me off!
So I only have three rows filled. The Field, Table and Show rows. For example:
Field: Player ID Contract ID
Table: Personal Details Contracts
Show: Ticked Ticked
If you need a screenshot, I'll get one.
Sorry to be a pest...and thanks for the help!
no1golfpro.co.uk - High quality golf equipment at competitive prices.
Suzanne posted this at 22:56 — 27th December 2003.
She has: 5,507 posts
Joined: Feb 2000
is this desktop only or web database with Access?
Timewell posted this at 23:56 — 27th December 2003.
They have: 344 posts
Joined: Jun 2002
Desktop Only
Timewell posted this at 00:52 — 28th December 2003.
They have: 344 posts
Joined: Jun 2002
Its ok, I seem to have (somehow) solved it.
Thanks for your help here guys! I really appreciate it...so once again, many thanks!
And if I have anymore problems, you'll be the first to know! 
no1golfpro.co.uk - High quality golf equipment at competitive prices.
Timewell posted this at 01:53 — 28th December 2003.
They have: 344 posts
Joined: Jun 2002
Right. A minor problem now. I have two forms set up. One is called Personal Details, the other is called Contracts. I have a button at the bottom of Personal Details that is linked to Contracts...on the contracts form there is a Contract_ID and a Player_ID. I want the button to link to the correct ID.
For example, I have a player with an ID of 125. I want the button to then link to the Contracts screen where the player's ID is 125, or to create a new record in Contracts that gives the Player an ID of 125 and a Contract ID of 125.
The ID's field type is Number - AutoNumber, I'm not sure if that will help.
Sorry for being a bit rushed, but it's rather late over in England!
no1golfpro.co.uk - High quality golf equipment at competitive prices.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.