Access Help with relationships

Brian Stuart

Brian Stuart

Brian Stuart

Associate
Joined
23 Oct 2002
Posts
2,203
Location
N Ireland
Hi
I have two tables in Access
Clients with the client number as the primary key
jobs record with the job number as the primary key

I created a relationship between the jobs table(client number) and the client table (client number) it is a one to many relationship and shows the the jobs for each particular client. This works fine.

What I want next is a relationship that shows the client details for a particular job. I have tried just about every combination of relationship possible but no joy. As you can tell I am new to access and would appreciate any help
Thanks
Brian
 
I'm not sure if I understand you correctly but from what I gather you are looking to create two relationships between the Clients table and the Jobs table - As far as I know you can only have one relationship between two tables and in fact, you should only need one to be able to achieve what you want to do.

In your Clients table you have all the relevant client info with a Primary Key of ClientNo and in the Jobs table you have info relating to the jobs with JobNo as the Primary key plus the client number (ClientNo) from the Clients table (possibly as a Foreign Key) - am I right here?

You also have a 1 to many relationship set up going from the Clients table to the Jobs table - is that right?

If the above is right, then you should be able to perform a query on the two tables to get the information you're after.

I can't remember exactly what to do in Access as it's been a while, but you're looking to select client information from the Clients table based on a specific JobNo in the jobs table. You link the two tables together with the ClientNo (Foreign Key) in the Job table and the ClientNo (Primary Key) in the Client table.

I hope that makes some sort of sense and at least sets you off in the right direction :)
 
Thanks Shoot1st,
Yes that makes sense but I will have to play about with the queries to get what I want. I do not have a foreign key assigned to the clientno in the jobs table but I will tootle off now and try it
Thanks for the help
Brian
p.s i told you I was a novice at this :)
 
You must log in or register to reply here.
Back
Top Bottom