Crosstab Query Rows

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

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

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

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

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

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

Doh! Thanks heaps Rob. That will teach me to read more carefully. Fixed and
working.

"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