Query based on two queries

[mela]

Hi I have a query which is supposed to pull up the data from two other queries. If both queries have atleast on result then the 3rd query is working prperly. The problem is if one of the queries returns no results then the query based on the other two queries also displays no results but I want to to show the results of the query with results still. Any help would be appreciated.

Thanks

 

[WayneRyan]

Mela,

Very easy to do ... just hard to explain.

In the query design view, bring in your two queries.

Join them by connecting the appropriate field(s).

Then right-click on the connecting line and change from the "where the
field(s) are equal" to "Select ALL from ..." by selecting the query that
will always have data.

If you look at the SQL view, you're just changing an INNER JOIN to a
LEFT JOIN.

hth,
Wayne

 

[mela]

Thanks for the help!!! That worked perfectly! Just one other quetion for you. I want to create a form based on this query but I have a similar type problem if one of the sum columns in my query which is based on two queries is empty then it won't fill out the form. Any thoughts??

 

[WayneRyan]

Mela,

For the ones in the right-hand side (with no entries), just use the Nz function
to assign a 0 (????) value to them.

Nz(B.NoProbableValue, 0)

hth,
Wayne

 

[Atomic Shrimp]

If either one of the two queries could potentially be empty, then you may need to create a third one that just selects the base fields (or you might be able to use the underlying table), then Left join both of the other two out from it.