Access Query/Design Question

[TurmoilMishka]

Hello,



In my database i have Clients and Consultants. The Clients are Assigned to Consultants by State , how would i set that up as a relationship? I also want to run the query to print the assigned client and consultant

table1 - ID, First Name , Last Name , Address
table2 - ID, First Name , Last Name , Address
lets say they are assigned by state , NY to NY, MA to MA

[TurmoilMishka]

table1 - ID, First Name , Last Name , Address
table2 - ID, First Name , Last Name , Address
lets say they are assigned by state , NY to NY, MA to MA

[alansidman]

You need to have a Foreign Key in the Consultant Table which equals the ID key in the Client table so that a relationship can be created. Make sure that your address fields(s) contain the State in a Separate field. I usually make it a number and have a separate table for states. This then avoids the issue of misspellings.

Then you can run your query based upon one or more states.

[TurmoilMishka]

I might be not saying it correct.

How does the database know that they are related by states during the query so Sally NJ and John NJ , Jake NY and Bill NY.

[alansidman]

When you build your query you join on the state fields.

[ssanfu]

In my database i have Clients and Consultants. The Clients are Assigned to Consultants by State

Which statement is correct?

Statement 1: "One Client has one Consultant and one Consultant has many Clients"
This is a one to many relationship. You need two tables.

Consider tables:

tblConsultants
-------------
ConsultantID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Address1 (Text)
CityName (Text)
StateName (Text)
ZipCode (Text)


tblClients
-------------
ClientID_PK (Autonumber)
ConsultantID_FK (Number - Long) Link to tblConsultant
FirstName (Text)
LastName (Text)
Address1 (Text)
CityName (Text)
StateName (Text)
ZipCode (Text)




Statement 2: "One Client has many Consultants and one Consultant has many Clients"
This is a many to many relationship. You need three tables.

Consider:

tblConsultants
-------------
ConsultantID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Address1 (Text)
CityName (Text)
StateName (Text)
ZipCode (Text)


tblClients
-------------
ClientID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Address1 (Text)
CityName (Text)
StateName (Text)
ZipCode (Text)


jnctClientsConsultants
--------------------
ClientConsultID_PK (Autonumber)
ClientID_FK (Number - Long) Link to tblClients
ConsultantID_FK (Number - Long) Link to tblConsultants
(Other fields if/as necessary)

[ArviLaanemets]

Statement 3: "One Client has one Consultant at any time moment, but may have different Consultants at different time moments"
You again need 3 tables.


tblConsultants
-------------
ConsultantID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Address1 (Text)
CityName (Text)
StateName (Text)
ZipCode (Text)


tblClients
-------------
ClientID_PK (Autonumber)
FirstName (Text)
LastName (Text)
Address1 (Text)
CityName (Text)
StateName (Text)
ZipCode (Text)

tblClientsConsultants
--------------------
ClientConsultID_PK (Autonumber)
ClientID_FK (Number - Long) Link to tblClients
ConsultantID_FK (Number - Long) Link to tblConsultants
ValidFrom (date)

tblClients
-------------
ClientID_PK (Autonumber)
ConsultantID_FK (Number - Long) Link to tblConsultant
...

This looks like marriage in very chatolic country