Showing blank records in a union query

[AlexCuse]

Hello all,

I need to create an excel report that shows planned vs. actual information for ten programs, with each program getting a little chart on a tabloid page. Each little table has different columns, so I will need to probably export query and then write a little vb routine to handle all of the formatting and place the data in the right places. The problem is as follows:

My data is coming from two sources. Actual data comes from a collection of tables on SQL server, Planned comes from a table within access. I have the union query working, but in some instances there is no Actual data. How can I get a blank row to go into my union query where there isn't any actual data? (I need to standardize output for the formatting procedure).

I was thinking of starting with a left join from a made up table with year/quarter/program information filled in, but that seems kind of sloppy.

Any advice is greatly appreciated. I can post the query if needed, but it is a pretty long one and I don't want to take up to much space.

Thanks a lot,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[rac2]

Actually it is not sloppy, it is the solution. Another common situation which is solved by that approach is one where a row is desired for every date, but there are some dates with no activity. If there were activity on every date, the straightforward GROUP BY query is sufficient. If not, the way to get those zero rows is by LEFT JOINing the GROUP BY with a table of dates. And of course using IIf(IsNull...) to display zeros instead of NULLs. Other situations involve a fixed and known set of categories, again with possibly missing detail rows for some categories. Same thing, create a table with the complete structure and join it with the detail or aggregate query.

 

[AlexCuse]

Glad to know I was on the right track! About an hour after posting I realized that it probably was the ideal solution from a maintenance perspective. Thanks for the confirmation!

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.