One query blanking another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several queries I wish to combine to one query to creat a report.
These queries are asking for info based on a date. If the result of one query
is empty (no results from the that date), then the combined query turns up
completely empty even though individually, the other queries have information.

How can I get the combined query to list all available info and simply have
a blank cell where the is no info?

Thanks
 
If I understand correctly, you created a query using 3 other queries as
input "tables", and in the upper pane of table design, there is a line
joining one query to the others?

If so, double-click the line going to the query that has no records. Access
opens a dialog offering 3 options. The first is the default. Choose the 2nd
or the 3rd - whichever one gives you the table that as records.

If both the other queries join to the one that has no records, you may need
a different approach.
 
I have several queries I wish to combine to one query to creat a report.
These queries are asking for info based on a date. If the result of one query
is empty (no results from the that date), then the combined query turns up
completely empty even though individually, the other queries have information.

How can I get the combined query to list all available info and simply have
a blank cell where the is no info?

Thanks
Click to expand...

Use a Left Outer Join to join the queries, rather than an Inner Join.

If you would like help doing so, please post the SQL of the queries (which we
cannot see from here!)

John W. Vinson [MVP]
 
John W. Vinson said:
Use a Left Outer Join to join the queries, rather than an Inner Join.

If you would like help doing so, please post the SQL of the queries (which we
cannot see from here!)

John W. Vinson [MVP]
Click to expand...

SELECT [Month Civil Active].[Project type], [Month Civil Active].[Proposed
date By Month], [Month Civil Active].[Sum Of Civil Amt], [Month Civil
Active].[Count Of index1], [Month Civil Active].[Project status], [Month
Structural Active].[Project type], [Month Structural Active].[Proposed date
By Month], [Month Structural Active].[Sum Of Structural Amt], [Month
Structural Active].[Count Of index1], [Month Structural Active].[Project
status], [Month Survey Active].[Project type], [Month Survey
Active].[Proposed date By Month], [Month Survey Active].[Sum Of Survey Amt],
[Month Survey Active].[Count Of index1], [Month Survey Active].[Project
status]
FROM [Month Civil Active], [Month Structural Active], [Month Survey Active];

The report I have been asked to create is to have Civil, Structural and
Survey info by the month, and by the year on the same report. I can create
the Yearly info; now I want the monthly info to show up with only having to
type in the month parameter once. Thanks for any help you can send my way.
 
John W. Vinson said:
Use a Left Outer Join to join the queries, rather than an Inner Join.

If you would like help doing so, please post the SQL of the queries (which we
cannot see from here!)

John W. Vinson [MVP]
Click to expand...

SELECT [Month Civil Active].[Project type], [Month Civil Active].[Proposed
date By Month], [Month Civil Active].[Sum Of Civil Amt], [Month Civil
Active].[Count Of index1], [Month Civil Active].[Project status], [Month
Structural Active].[Project type], [Month Structural Active].[Proposed date
By Month], [Month Structural Active].[Sum Of Structural Amt], [Month
Structural Active].[Count Of index1], [Month Structural Active].[Project
status], [Month Survey Active].[Project type], [Month Survey
Active].[Proposed date By Month], [Month Survey Active].[Sum Of Survey Amt],
[Month Survey Active].[Count Of index1], [Month Survey Active].[Project
status]
FROM [Month Civil Active], [Month Structural Active], [Month Survey Active];
Click to expand...

You don't have ANY joins here. This will certainly not work as desired; it
will instead pear every record in [Month Civil Active] with every record in
[Month Structural Active] with every record in [Month Survey Active]! So if
you have 12 records in each query (guessing here) you would get all 1,728
possible combinations.

I'm not sure what your queries contain or what you want to see so I'm not sure
what to suggest. A Report with three Subreports might work; putting a JOIN
line between the queries joining on [Proposed datae by month], using Outer
Joins appropriately (and I don't know what is appropriate for your data) might
also; or if you want to see all the records from the first query, then all the
records from the second, then all the records from the third, a UNION query
may be better.


John W. Vinson [MVP]
 
John W. Vinson said:
John W. Vinson said:
I have several queries I wish to combine to one query to creat a report.
These queries are asking for info based on a date. If the result of one query
is empty (no results from that date), then the combined query turns up
completely empty even though individually, the other queries have information.

How can I get the combined query to list all available info and simply have
a blank cell where there is no info?

Thanks

Use a Left Outer Join to join the queries, rather than an Inner Join.

If you would like help doing so, please post the SQL of the queries (which we
cannot see from here!)

John W. Vinson [MVP]
Click to expand...

SELECT [Month Civil Active].[Project type], [Month Civil Active].[Proposed
date By Month], [Month Civil Active].[Sum Of Civil Amt], [Month Civil
Active].[Count Of index1], [Month Civil Active].[Project status], [Month
Structural Active].[Project type], [Month Structural Active].[Proposed date
By Month], [Month Structural Active].[Sum Of Structural Amt], [Month
Structural Active].[Count Of index1], [Month Structural Active].[Project
status], [Month Survey Active].[Project type], [Month Survey
Active].[Proposed date By Month], [Month Survey Active].[Sum Of Survey Amt],
[Month Survey Active].[Count Of index1], [Month Survey Active].[Project
status]
FROM [Month Civil Active], [Month Structural Active], [Month Survey Active];
Click to expand...

You don't have ANY joins here. This will certainly not work as desired; it
will instead pear every record in [Month Civil Active] with every record in
[Month Structural Active] with every record in [Month Survey Active]! So if
you have 12 records in each query (guessing here) you would get all 1,728
possible combinations.

I'm not sure what your queries contain or what you want to see so I'm not sure
what to suggest. A Report with three Subreports might work; putting a JOIN
line between the queries joining on [Proposed datae by month], using Outer
Joins appropriately (and I don't know what is appropriate for your data) might
also; or if you want to see all the records from the first query, then all the
records from the second, then all the records from the third, a UNION query
may be better.


John W. Vinson [MVP]
Click to expand...

SELECT [Month Civil Active].[Project type], [Month Civil Active].[Proposed
date By Month], [Month Civil Active].[Sum Of Civil Amt], [Month Civil
Active].[Count Of index1], [Month Civil Active].[Project status], [Month
Structural Active].[Project type], [Month Structural Active].[Proposed date
By Month], [Month Structural Active].[Sum Of Structural Amt], [Month
Structural Active].[Count Of index1], [Month Structural Active].[Project
status], [Month Survey Active].[Project type], [Month Survey
Active].[Proposed date By Month], [Month Survey Active].[Sum Of Survey Amt],
[Month Survey Active].[Count Of index1], [Month Survey Active].[Project
status]
FROM ([Month Civil Active] LEFT JOIN [Month Structural Active] ON [Month
Civil Active].[Proposed date By Month] = [Month Structural Active].[Proposed
date By Month]) LEFT JOIN [Month Survey Active] ON [Month Structural
Active].[Proposed date By Month] = [Month Survey Active].[Proposed date By
Month];

It's getting closer. I have three queries which all request a date. When I
try to combine them into one query, it only asks for a date once - which is
what I want for the next step - the report.

The way it is configured now, however, I can only get the correct
information if there is any data in the Month Civil Active query. If there
happens to not be any data for February for Civil, the February info for
Structural and Survey will not show up.

I REALLY appreciate your help. Please don't give up on me yet . . .
Thanks
 
It's getting closer. I have three queries which all request a date. When I
try to combine them into one query, it only asks for a date once - which is
what I want for the next step - the report.

The way it is configured now, however, I can only get the correct
information if there is any data in the Month Civil Active query. If there
happens to not be any data for February for Civil, the February info for
Structural and Survey will not show up.
Click to expand...

So you want the other two querys' data to show up even if any one of the three
has no data? Ouch. That requires two "full outer joins", which Access does not
support.

You may need to create a little calendar table with only one field, of the
same datatype as your month field. Rather than joining these three queries to
one another, join all three of them, left outer join, to this month table.

John W. Vinson [MVP]
 
I added the little calendar table and tried changing to LEFT OUTER JOIN in
the SQL, but it kept saying "JOIN expression not supported.". But you have
really helped. I've learned a lot and I now know that what I was trying for
won't work. I had figured out another way to do the report, but the operator
would have to input the date 12 times. My boss said that was better than what
they had - so she will accept it. I just wanted to make it more friendly by
only having to input the date once.

I will continue to learn about joins and maybe try the little table again in
the future when I know more. For now, they just want the report.
Thanks. You are appreciated.
 
I added the little calendar table and tried changing to LEFT OUTER JOIN in
the SQL, but it kept saying "JOIN expression not supported.". But you have
really helped. I've learned a lot and I now know that what I was trying for
won't work. I had figured out another way to do the report, but the operator
would have to input the date 12 times. My boss said that was better than what
they had - so she will accept it. I just wanted to make it more friendly by
only having to input the date once.
Click to expand...

Since you chose not to post the SQL or any information about the tables, of
course we can't help you correct the join expression... since we don't know
what it was.

We'd like to help if you're willing!

John W. Vinson [MVP]
 
Back
Top