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?
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?