Solved

database query displaying more than i want

Posted on 2006-10-30
4
291 Views
Last Modified: 2008-02-01
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            
0
Comment
Question by:gilmac
4 Comments
 
LVL 1

Accepted Solution

by:
dacz earned 500 total points
ID: 17840069
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
 
LVL 1

Expert Comment

by:Aristo
ID: 17840130
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
 
LVL 8

Expert Comment

by:mnrz
ID: 17840141
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
 

Author Comment

by:gilmac
ID: 17847631
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now