Solved

database query displaying more than i want

Posted on 2006-10-30
4
283 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
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…
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…

747 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

11 Experts available now in Live!

Get 1:1 Help Now