Joined queries and no records problem (1 Viewer)

olxx

Registered User.
Local time
Yesterday, 23:15
Joined
Oct 2, 2009
Messages
52
I have two queries that collect and join data from different tables. Lets call them qry1 and qry2.Those two queries have no criteria and always return data.
Now I have a third query based on that two queries that joins these two queries for a report. Report is launched from a form where user adds criteria. Now there is a problem when this criteria is not met in one of the queries (qry1 or qry2) and then the third query returns no records and report shows no records. But I want the the report to show records from one query that has values and show "no records found" and "0" for numeric fields (those zeros are most important, because report calculates some fields based on that) from second query.
Both queries contain multiple fields and it is not possible to collect data from all tables with one query. Is there a solution for this problem or it is just a bad db design?
 

jzwp22

Access Hobbyist
Local time
Today, 02:15
Joined
Mar 15, 2008
Messages
2,629
Both queries contain multiple fields and it is not possible to collect data from all tables with one query. Is there a solution for this problem or it is just a bad db design?

Since you did not provide any details on the design, it is hard to say whether it is bad. Could you provide more details?

As to the queries (qry1 & qry2), is one of the two queries dominant in that it will always contain the basic info you want on some particular field? If so, you may need to change the join type between the 2 queries (in your third query). For example, you many want to select all records from qry1 and only those from qry2 that match (join type 2 or a left join) or the other way around (join type 3 a right join). Join type 1 only returns where the key fields of both queries match (inner join)

For example, qry1 might contain customers while qry2 might have a monthly sales total by customer. Of course, not all customers will have bought something, so you would want to report zeroes for those customers who have not bought anything, but you still want to show the customer in the results. qry1 (customers) would include all customers and qry2 would have those that match. You would need a left join from qry1 to qry2.
 

Users who are viewing this thread

Top Bottom