Query giving no result

[Ginny2222]

Hi all,

Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).

How can I set my query to show results even if the underlying query doesn't.

thanks in advance.

rgs
Ginny

 

[WayneRyan]

Ginny,

My thought is that you are joining the 3 queries, they are not 3 successive queries.

If you right-click on the lines connecting the tables, you want to choose:

Select ALL from "SomeTable".

You DON'T want to see "...only where the fields are equal".

Basically, when you look at the SQL (right-click ... view SQL), you DO NOT
want to see an INNER JOIN.


Wayne

 

[Guus2005]

You are using an inner join to connect the three queries. If you use a Left or Right Outer Join, you get a result eventhough one of the queries produce no result.

Left Click on the link between two queries in the query design (QBF). Select Join properties. Select 1 (default) for an inner join or 2 or 3 for an outer join.

Post your sample database for a solution/example specific to you.

Enjoy!
Enjoy!

 

[gemma-the-husky]

if your queries are defined as

query3 from query2 from query1 then this is likely

you start with query 1, then query 2 selects some of these, and query 3 selects some of these

-----------
if what you are really trying to do is

query1 together with the items in query2 together with the items in query3 then you need a UNION query

which has to be written in SQL (although can still be done with the access design pane) and needs to be

select * from query1 union
select * from query2 union
select * from query3

but columns for each query have to match

 

[Ginny2222]

Hi all, thank you for your responses. I can't use a Union query as I need the results in separate columns. Also while the data is similar they are not joined fields within the query.

My queries are separate to show - Dept 1 - total and percentage gain, Dept 2 - total and percentage gain, Dept 3 - total and percentage gain.

What I was trying to do was make it easier to use the data on Reports. I need the results of these queries in a single line to display in a List Box.

Should I use a Crosstab query instead?

rgs
Ginny

 

[DCrake]

Simple Software Solutions

Ginny

Reading you last post it seems that each of your 3 queries are based on the same underlying table but are different results for each department. If I am wrong forget the rest of this thread.

Go to the design of query 1 for Dept A

Add a new column

DeptA:IIf([Dept]="A","Dept A","")

Add another

DeptATotal:IIF([Dept]="A",[Total],0)

Repeat for each dept

Next Create a new query

based on the above query and perform the summary options you require. This will give you a horizontal view of each depatment one one line.


CodeMaster: