If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|