Crosstab Query Rows

And a follow-up ...

My last-night's post is correct, but there may be one other problem: The
name of the region field from your tblRegions is shown (in the last line of
your SQL) as "Regions", but in my response I entered it (in the replacement
for the first line of your SQL) as "Region". It must be the same (and match
your actual fieldname in tblRegions) in both places.

Again, HTH,

Rob

PS. I suspect that, since you didn't mention any complaints about the join
when you constructed the query, that your fieldname is actually "Regions".
Note that the reason you got a blank row in your query was because you were
using calRegion in your select statement, and there is no calRegion entry
from your [Attendees Qry Previous All_Crosstab] query for regions that don't
appear in it (but do appear in tblRegions). Precisely the problem you are
trying to solve ;-)

Rob Parker wrote:
Hi Andrew,

This is a quick answer, based on a late-night skim of your SQL. I'll
have a closer look tomorrow, and post a follow-up if necessary.

In my previous post, I said to join the Region field from the new
tblRegion to the Region field in your existing query, and to use the
Region field from that table in your query. It seems that although
you've joined the field from the new table, you're still using the
Region field from your current query. Try changing the first line of
your SQL (as shown in the post below) to:

SELECT [tblRegions].Region, ...

HTH (and more tomorrow if this isn't the solution),

Rob


"Andrew Glennie" wrote in
message ...
Hi Rob (and anyone else following this thread)

Here is the SQL - rather long I'm afraid. I note that although I
followed your instructions for getting an outer join, its ended up
only an a left join. I have found that creating an outer join is
rather inconsistent... SELECT [Attendees Qry Previous
All_Crosstab].calRegion, [Attendees
Qry Previous All_Crosstab].[Medtech Train the Trainer] AS [Prev Med
TTT], [Attendees Qry Previous All_Crosstab].[Health Care Pathways]
AS [Prev HCP],
[Attendees Qry Previous All_Crosstab].[Heath Assessment] AS [Prev
Hlth Ass],
[Attendees Qry Previous All_Crosstab].HDC AS [Prev HDC], [Attendees
Qry Previous All_Crosstab].[Primary Mental Health Care] AS [Prev
PMH], [Attendees
Qry Previous All_Crosstab].[QuitCard Training] AS [Prev QuitCard],
[Attendees
Qry Previous All_Crosstab].[CARS Training] AS [Prev CARS],
[Attendees Qry Previous All_Crosstab].[IOMS Training for Health
Staff] AS [Prev IOMS], [Attendees Qry Previous
All_Crosstab].[Cultural Responsiveness] AS [Prev Cult], [Attendees
Qry Previous All_Crosstab].[CPR Initial] AS [Prev CPR Init],
[Attendees Qry Previous All_Crosstab].[CPR Refresher] AS [Prev CPR
Ref], [Attendees Qry Previous All_Crosstab].[Pre-Hospital Emergency
Care] AS [Prev PHEC Init], [Attendees Qry Previous
All_Crosstab].[Pre-Hospital Emergency Care Refresher] AS [Prev PHEC
Ref], [Attendees Qry Previous All_Crosstab].ACC AS [Prev ACC],
[Attendees Qry Previous All_Crosstab].[Hostage Awareness] AS [Prev
Host], [Attendees Qry Previous All_Crosstab].[Suicide Awareness for
UM and HS] AS [Prev Suicide], [Attendees
Qry Previous All_Crosstab].[Getting Got] AS [Prev Get Got],
[Attendees Qry Previous All_Crosstab].[Advanced Skills for Front
Line] AS [Prev Adv], [Attendees Qry Previous
All_Crosstab].Vaccinator AS [Prev Vacc Init], [Attendees Qry
Previous All_Crosstab].[Vaccinator Refresher] AS [Prev Vacc Ref],
[Attendees Qry Previous All_Crosstab].[Preceptor Training] AS [Prev
Prec], [Attendees Qry Previous All_Crosstab].[Infection Control] AS
[Prev Inf], [Attendees Qry Previous All_Crosstab].[ECG Technician]
AS [Prev ECG], [Attendees Qry Previous All_Crosstab].[Quality
Management CQAA] AS [Prev QM],
[Attendees Qry Previous All_Crosstab].[Report Writing for Health
Staff] AS [Prev Rpt Wrt]

FROM tblRegions LEFT JOIN [Attendees Qry Previous All_Crosstab] ON
tblRegions.Regions = [Attendees Qry Previous All_Crosstab].calRegion;

Your thoughts?

Andrew

"Rob Parker" wrote:

If you post the SQL of your query, maybe I (or someone else) can
spot the problem.

Rob


Andrew Glennie wrote:
Thanks Rob,

Unfortunately doesn't assist completely. Where there is data
relating to a region, the region title displays correctly.
However, if there is no data, the region title doesn't display but
a blank row is inserted. Any thoughts anyone?

Regards

Andrew

"Rob Parker" wrote:

Hi Andrew,

You need to force all the regions into either the query that your
crosstab is based on, or your final query. The way to do that is
to set up a table (let's call it tblRegions) containing a single
field - Region - and include that table in the query where you
want all regions to appear; join it to the existing Region field
in your current table/query, and change the join to an outer
join, by right-clicking the join line, selecting Join Properties,
then selecting "Show all records from tblRegions and only those
records ...". Use the Region field from this table in your query,
and all regions will appear.

HTH,

Rob

Andrew Glennie wrote:
Hi All,

I am reporting on training and need to include YTD training for
each month
as well as the current month. Currently, I have a crosstab query
which collates the YTD training, and another crosstab which
collates the for the current month. I then combine them into one
big query to report the now and before values. Each query is
based on up to 5 regions within the organisation.

Problem arises when the current month doesn't have training in
all regions. The conbined query only contains the number of
regions for which training has occurred in that month and I need
to report on all regions every month.

Tried mucking about with different types of joins - no luck.

So over to the gurus - any ideas?

TIA

Andrew