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 said:
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
:
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