Confused over easy relationship issue

[JrBuds2]

Hi, I am very confused. I am creating a DB and cannot get my head around this situation.



I have a Jobs table and a Clients table. Each Job can only have one client. My problem is now that I need to have multiple contacts for each client where only one Client and Contact can be assigned to one job.

So Job Number "502" is for client "Virgin" and the contact is Tim at Virgin who's phone number is ......

I am unsure on how to get around this problem as I am a Access NOOB, any help would be appreciated.

I tried to upload the db file but it kept failing.

[JrBuds2]

Every time I try think of how the relationship should be it ends in a loop from JobTable-ClientID to ClientTable-ClientID to ClientTable-ContactID to ContactTable-ContactID to JobTable-ContactID. It's just seems wrong and I can't get it to work.

[ranman256]

That's 3 tables
tClients, tContacts, and tJobs.
tContacts can have many recs for a tClient.
the tJob table can only have 1 client,for 1 job, for a client,
The tJobs fields are keys (with *) to prevent more than 1 job person per client.

TJobs table
-------------
clientID*
jobName*
contactID

[JrBuds2]

Thanks for the quick reply, would I need the Relationship to go From the tJob to tClient then tContact or would tJob join to both tClient and tContact?

[JrBuds2]

https://drive.google.com/file/d/0BzP...ew?usp=sharing is where I have uploaded the DB

[aytee111]

There would need to be a link each time the field name is the same and contains the same data. Client to jobs and to contacts. Contact to jobs and to client.

(Attachments need to be added to your post, many people can't go to outside links. Click on Go Advanced, then the Attach icon, then upload your file.)

[JrBuds2]

Hi Aytee, everytime I try to use that uploader it fails.

I can get the relationship working (I think) but then I run into issues as on the job form they fill out the job details then select the client and from that it needs to loads the contacts for that client so the use can select which they need. Upon refreshing the form it demands a contact to be filled in but it hasn't generated the list yet.

I think my main issue is it's wrong from the beginning, could you guys quote me how much to sort it out or is that against the rules?

[Homegrownandy]

This is for orders, but could just as easily be jobs. Is this any help?

[ranman256]

The contact tbl would have a ClientID field.

[Homegrownandy]

just as a bit of explanation. Order tbl does have a contact field but this is not defined in relationships because its not always a requirement. However. This is defined at query level. to retrieve the relevant info.

[JrBuds2]

Thanks Homegrown that sorted my issue of not being able to refresh the form to load the relevant contacts.

[Homegrownandy]

Do you have a screenshot of your relationships? If you can upload that maybe you can get some feedback.