Multiple Contacts for single customer

[Marotta]

Hi, I am currently trying to design a jobs allocation database and am having trouble setting up a certain aspect, I have a jobs table with a drop down list of customers these customers need multiple clients for the multiple departments they have. I setup 3 tables the jobs table which has and relationship to the customer and then the customer table which has a relationship that allows creation of the contacts record i can create multiple contacts using a portal but when I select them in the drop down box I have created it overwrites the contact in the portal with the last contact I had selected until the same contact exists through all records and when I select a different record the street or phone number for that contact does not change.

I know it is a little confusing please look at the attachment I have included which may help show what i have done wrong

thanks in advance for any assistance

[Fenton]

I have a jobs table with a drop down list of customers these customers need multiple clients for the multiple departments they have

This is the problem. It sounds like need a couple more tables, for clients and departments (before Customers). But I can't tell from the above statement exactly what you mean.

The "client" is the furthest down the hierarchy. So that's who would be chosen in the line items table of actual work done, Job Details I think. (There is some confusion between Job Details and Job Times. I wouldn't think you'd have Times or Vehicle in both.)

From Client to Department to Customer (I'm assuming these are businesses) are many to one relationships. Whether Customer (company) is automatically implied by a client choice is a different matter; whether you have separate tables to store these entities outside of the job entry tables (likely if you have repeat business), or whether you just enter this data in client fields (ie., the join table is not a join table, but a flat line items table).

Because you say multiple clients per job, you need a little join table between Job Details and Clients; then Departments, then Customers.

I may be way off. We need more info on what a client, department and customer are, their relationship to each other (as well as to a Job), and whether this info will be stored properly in its own tables, for repeated use, or whether it will just be entered on-the-fly.

[Marotta]

Thanks for your reply I will attach a copy of the database so far as I cannot solve this problem, what i need is for my customer(a business) to have multiple contacts (employees) each contact with their own phone number, address etc. now when a new job is created I want to be able to select a customer from a drop down box this customer selection then shows only the contacts of that customer in the contacts field, contacts should be able to be selected by a drop down box, when the contact is chosen from the drop down box their address phone numbers etc will show up on the main jobs page. these customers and contacts will be used repeatedly and need to be stored so they can be selected for later jobs. This way the main jobs page can then be printed and given to staff who go to customers sites and they have all the details without having to enter the same thing every time they make a new job. hope this is a bit clearer, please if you have the time to help me sort this problem out it would be much appreciated, thank you again in advance.

marcomIT.zip

[Marotta]

bump

[Fenton]

The problem is that you've set up the relational value list correctly, which allows you to filter the contacts via the customer relationship; but you also need a direct relationship to Contacts, via the Contact. Otherwise you cannot get the details from that specific contact, after you've made the choice.

I attached a few more of the other relationships, possibly correctly -]

MarcomIT5.zip

[Marotta]

Thank you so much for your help fenton!