Database Relationship

J

Jeroen

I have question with regard to the structure of my database.

I have a main contact details table (primary key of table “MAIN†= number).
In a second table I want to put specific information that is applicable to
some of my contacts (other information of these contacts is already in table
MAIN) (primary key of table “DETAIL†= number)
I make a relation between the two tables (primary key MAIN= primary key
DETAIL).

Suppose now that I have 200 contact details in table MAIN and only for 20
contact details I need to put information in table DETAIL.

My queries in which I want to combine information from table MAIN and table
DETAIL are only working if I have also 180 contacts in table DETAIL with only
the primary key field filled out, the other fields empty.

Why do I have to make a reference to these 180 “empty contacts†in table
DETAIL?
What can I do to avoid this inefficiency?

Thanks in advance
 
A

Arvin Meyer

You need a Left or Outer Join. In your query grid, right-click on the join
line between the 2 tables and choose Join Properties. Then pick the solution
which says All of the data from tblMain and whatever Matching records from
tblDetail. The SQL should look something like:

SELECT tblMain. *, tblDetail. *
FROM tblMain LEFT JOIN tblDetail ON tblMain.ID = tblDetail ID;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top