Linking three filed to one

[LukeJ Innov]

Hello All,

I have a database, that holds information on a customer, then links information on that customer such as their contacts, site names and addresses, contract etc. See attached screen print:



Basically what I would like to know is how would I link these three fields to the one on the screen print so that it all relates, and when viewed shows all the data?

Sorry if this is confusing.

thanks in advance,

[rpeare]

you're not likely to get the results you want with a single query because you have one to may relationships with tables that are unrelated to one another (contact data is not related to site data). If you're trying to generate a report you are likely going to have more success with reports/subreports than trying to build a single query that has everything on it.

[LukeJ Innov]

Sorry maybe im not being clear enough, I want to allow users to have the database allow them to add a customer, then add data on their site name and address, then add contacts etc. so all the identifiers link.

Without manually typing the ids, I want the database to be able to know that you are adding say a site name, for customer (*generic name*).

[ssanfu]

I would first normalize your table structure before worrying about forms.

For instance, you have a table for customer data. In "Customers Data", you have one field for the customer name. Is this a company? If an individual, there should be a field for first name and a field for last name. If you have one field, how will you control if the name is entered as "Jon Smith" or "Smith, Jon"?
No other data for the customer?
Is "Contact Data" different than "Customer Data"?
One field for "Contact Name"?

Why are there two tables for the sites? Can a site be in two locations at the same time?


Other things I noticed:
** You are using spaces in field names (and table names)
** You are using special characters in field names (the underscore is ok to use)
The primary key field is usually the first field in a table. (At least that was the convention I was taught)

[LukeJ Innov]

Hi Steve thanks for the input.
Basically the 'customers' table will be a company, then that company will have contacts, as well as contracts, and many different sites, and a site will have a portfolio of resources.

[LukeJ Innov]

Hello Steve,

Does this look better?



Ignore the first Image that was the wrong screen shot I uploaded.

[ssanfu]

Yes, the names are better (referring to the top image)

Basically the 'customers' table will be a company, then that company will have contacts, as well as contracts, and many different sites, and a site will have a portfolio of resources.

This helps


One customer (company) can have many contacts. (Doesn't a customer (company) have an address/phone/...??)
One customer (company) can have many contracts. (Not sure about this one. Are the contracts related to the customer or to the site?)

One contact can have many sites and one site can have many contacts. Many to many, so a junction table ("ContactSite") . OK, but the name fields are unnecessary. Just need the two foreign key fields - I also add an autonumber PK field.

One contract can be for many sites. One site has one contract? Can one site have more than one contract? (If so, need a junction table.)

One site can have many portfolios.
One portfolio can have many products.


Why is the "Sites" table named "CustomerAddress"? Confusing..

The date fields in "Contract" have "00/00/0000" in the name??? That is unnecessary... (special character used .... and a lot of extra typing)


IMO, no one but the developer should see the underbelly (table and field names) of the database so using "Proper" english, grammar and punctuation is unnecessary. On forms and reports, labels are used to describe the controls - like "First Name" instead of "FName" (what I use)



There are many more people here that are better at structure design than me; June7, rpeare and Orange to name a few. Hopefully they will comment also.