Join two tables

[Lisa]

Tbl.Address
AddressID (PK)
LName
FName
etc.
Tbl.Client
ClientID (PK)
AddressID (FK)
LName
FName
etc.

I need to create a junction table because these tables have a many to many
relationship. I'm not sure what I did wrong, but when I created the query I
got no results. Any suggestions? Oh, and I wonder about my foreign key.
How does Access know it's supposed to be FK?

Thanks!

 

[Jerry Whittle]

Are you saying that a client can have more than one address AND there can be
more than one client at the same address? That would be a M-M relationship. I
guess that could happen if a couple had a home and summer home or something.

Either way, why do you have a FName and LName in both tables? The adress
table should only have address data like Address, City, State, Zip, etc. The
Client table would have FName, LName, DOB, etc.

If you need a junction table, it should have as a minumun the ClientID (FK)
and AddressID (FK) fields in it.

 

[John W. Vinson]

Tbl.Address
AddressID (PK)
LName
FName
etc.

My address does not have a first name or a last name - just a street number.

The LName and FName fields should exist ONLY in the "people" table, tblClient.

Tbl.Client
ClientID (PK)
AddressID (FK)

The foreign key goes into the Many side table - does this mean that you have
many clients at each address? Mabye so, but a bit out of the ordinary.

LName
FName
etc.

I need to create a junction table because these tables have a many to many
relationship.

Is it really? Do you want to store two or more addresses per person, and have
two or more people share the same address? It may be the case, but it's a lot
more common for each Client to have one (actually used) address. If so the
Address table should have the ClientID.

I'm not sure what I did wrong, but when I created the query I
got no results. Any suggestions? Oh, and I wonder about my foreign key.
How does Access know it's supposed to be FK?

It's a FK in the way that it's used. It's not a property of the field and it's
not visibly a foreign key in table design view; but if you use the
Relationships window to create a join line from tblClient.ClientID to
tblAddress.ClientID, that makes the latter ipso facto the FK.

It might help if you explained the etc. and the real-life relationship between
clients and addresses. A Query joining the Address table to the Client table
will indeed have no records until there are records in both tables with a
matching ClientID field.

John W. Vinson [MVP]