database query displaying more than i want

I have 3 tables in a  MS Access97 database. I created a query where table 1 contains a one to many relationship to table 2 and table 3.

In this query I am displaying the Primary Key from table 1 and a field from table 2 and a field from table 3. Therefore the final outcome of the qery may look like this.

PK 3      Field 2      Field 1
1      C      B
1      C      A
1      D      B
1      D      A
1      E      B
1      E      A

The SQL for the above is:

SELECT Table3.[PK 3], Table2.[Field 2], Table1.[Field 1]
FROM (Table3 INNER JOIN Table2 ON Table3.[PK 3] = Table2.[FK 2]) INNER JOIN Table1 ON Table3.[PK 3] = Table1.[FK 1];

However, what I am trying to achieve is this:

PK 3      Field 2      Field 1
1      C      
1      D      
1      E      
1            A
1            B

How can I modify the design of the query to achieve this result?

Kind regards

Gilmac            
gilmacAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
daczConnect With a Mentor Commented:
try this SQL query:

SELECT DISTINCT Table3.[PK 3], Table2.[Field 2], '' AS [Field 1]
FROM Table2 INNER JOIN Table3 ON  Table2.[FK 2] =Table3.[PK 3]

UNION

SELECT DISTINCT Table3.[PK 3], '' AS [Field 2], Table1.[Field 1]
FROM Table1  INNER JOIN Table3 ON Table1.[FK 1] =  Table3.[PK 3]
0
 
AristoCommented:
Hello Gilmac,

You should use a union query. Write a manual query in Access like this:

SELECT Table3.[PK 3], Table2.[Field 2], NULL as [Field 1]
FROM (Table3 INNER JOIN Table2 ON Table3.[PK 3] = Table2.[FK 2])
UNION ALL
SELECT Table3.[PK 3], NULL AS [Field 2], Table1.[Field 1]
FROM (Table3 INNER JOIN Table1 ON Table3.[PK 3] = Table1.[FK 1]);
0
 
mnrzCommented:
Hi

would you tell us the result you want to achieve again because the thing I see is not correct
and also show the date of tables 1 and 2 and 3.

I am not familiar with MS Access but
test this:
SELECT T3.[PK 3], T2.[Field 2], T1.[Field 1]
FROM Table3 t3, Table2 t2, Table1 t1
where
t3.pk3 = t2.fk2 and
t3.pk3 = t1.fk1

0
 
gilmacAuthor Commented:
thank you dacz and everyone else. the solution from dacz works fine, so i'll give him the points.

however, i simplified the problem in hope of understanding how it works and then apply it to real life problem. however, i found that i am struggling to understand it, so i'll raise another question with the real problem, which has more tables and is a little bit more complicated.

if you could please have a look at that and help me with it, it would be much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.