Crosstab difficulties

[Nick 'The Database Guy']

I am running a crosstab query, no problem. It is a query that has two
tables. I want the query to return all rows from one of the tables so
I made the join LEFT handed, no problem there either, only that when I
run the query it is only the rows that have data are returned, the
result looks the same with or without the LEFT join. Has anyone heard
of this happening? Does anyone have a work around?

MTIA

 

[Allen Browne]

What criteria do you have? Did you explicitly ask for Nulls?

If you have criteria under a field from the outer side of the join, try
adding:
Or Is Null

 

[Nick 'The Database Guy']

What criteria do you have? Did you explicitly ask for Nulls?

If you have criteria under a field from the outer side of the join, try
adding:
    Or Is Null

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.






- Show quoted text -

Thanks for attempting to resolve my problem Allen, but I am still at
somewhat of a loss to know what is going on. First of all, yes I did
have a criteria on the 'Many' side, and no I did not specifically ask
for any nulls. So when I saw your suggested resolution to my problem
I slapped my forehead and thought 'ah, of course'. However, when I
specifically asked for nulls as well as the row total only increased
by 30 to 100, rather then 610 which in the number of rows in the 'One'
side table.

Below is my SQL if it helps:

TRANSFORM Sum(HfL_AcuteIP_Detail.CountOfRUN_SEQ) AS
SumOfCountOfRUN_SEQ
SELECT HfL_AcuteIP_Detail.CSP, HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code AS HRG35
FROM HfL_ActFormat LEFT JOIN HfL_AcuteIP_Detail ON HfL_ActFormat.Code
= HfL_AcuteIP_Detail.HRG35
WHERE (((HfL_AcuteIP_Detail.CSP)="ENT" Or (HfL_AcuteIP_Detail.CSP) Is
Null))
GROUP BY HfL_AcuteIP_Detail.CSP, HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code
ORDER BY HfL_AcuteIP_Detail.PbR DESC
PIVOT [pod] & " " & [Sex of patient] & " " & [FinalAgeBand] In ("ELORD
Male 0-16","ELORD Male 17-39","ELORD Male 40-64","ELORD Male
65-84","ELORD Male 85+","ELORD FEMale 0-16","ELORD FEMale
17-39","ELORD FEMale 40-64","ELORD FEMale 65-84","ELORD FEMale
85+","ELDAY Male 0-16","ELDAY Male 17-39","ELDAY Male 40-64","ELDAY
Male 65-84","ELDAY Male 85+","ELDAY Female 0-16","ELDAY Female
17-39","ELDAY Female 40-64","ELDAY Female 65-84","ELDAY Female
85+","NEL Male 0-16","NEL Male 17-39","NEL Male 40-64","NEL Male
65-84","NEL Male 85+","NEL Female 0-16","NEL Female 17-39","NEL Female
40-64","NEL Female 65-84","NEL Female 85+","REGAT Male 0-16","REGAT
Male 17-39","REGAT Male 40-64","REGAT Male 65-84","REGAT Male
85+","REGAT Female 0-16","REGAT Female 17-39","REGAT Female
40-64","REGAT Female 65-84","REGAT Female 85+");

Please help me, you're my only hope.

Nick

 

[Allen Browne]

Not sure what else to suggest, Nick.

Perhaps you could break the problem down into smaller chunks. Try the query
without crosstabbing or grouping:

SELECT HfL_AcuteIP_Detail.CSP,
HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code AS HRG35
FROM HfL_ActFormat LEFT JOIN HfL_AcuteIP_Detail
ON HfL_ActFormat.Code = HfL_AcuteIP_Detail.HRG35
WHERE (HfL_AcuteIP_Detail.CSP="ENT")
Or (HfL_AcuteIP_Detail.CSP Is Null);

If that gives all the right results add teh GROUP BY clause.

Then try the crosstab without the IN in the PIVOT.

Hopefully you will be able to pin down the point at which it goes
pear-shaped. Is so, the solution might involve saving the query that works,
and then building on that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

What criteria do you have? Did you explicitly ask for Nulls?

If you have criteria under a field from the outer side of the join, try
adding:
Or Is Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message




- Show quoted text -

Thanks for attempting to resolve my problem Allen, but I am still at
somewhat of a loss to know what is going on. First of all, yes I did
have a criteria on the 'Many' side, and no I did not specifically ask
for any nulls. So when I saw your suggested resolution to my problem
I slapped my forehead and thought 'ah, of course'. However, when I
specifically asked for nulls as well as the row total only increased
by 30 to 100, rather then 610 which in the number of rows in the 'One'
side table.

Below is my SQL if it helps:

TRANSFORM Sum(HfL_AcuteIP_Detail.CountOfRUN_SEQ)
AS SumOfCountOfRUN_SEQ
SELECT HfL_AcuteIP_Detail.CSP,
HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code AS HRG35
FROM HfL_ActFormat LEFT JOIN HfL_AcuteIP_Detail
ON HfL_ActFormat.Code = HfL_AcuteIP_Detail.HRG35
WHERE (HfL_AcuteIP_Detail.CSP="ENT")
Or (HfL_AcuteIP_Detail.CSP Is Null)
GROUP BY HfL_AcuteIP_Detail.CSP,
HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code
ORDER BY HfL_AcuteIP_Detail.PbR DESC
PIVOT [pod] & " " & [Sex of patient] & " " & [FinalAgeBand]
IN ("ELORD Male 0-16","ELORD Male 17-39", ...

 

[Nick 'The Database Guy']

Not sure what else to suggest, Nick.

Perhaps you could break the problem down into smaller chunks. Try the query
without crosstabbing or grouping:

SELECT HfL_AcuteIP_Detail.CSP,
HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code AS HRG35
FROM HfL_ActFormat LEFT JOIN HfL_AcuteIP_Detail
  ON HfL_ActFormat.Code = HfL_AcuteIP_Detail.HRG35
WHERE (HfL_AcuteIP_Detail.CSP="ENT")
  Or (HfL_AcuteIP_Detail.CSP Is Null);

If that gives all the right results add teh GROUP BY clause.

Then try the crosstab without the IN in the PIVOT.

Hopefully you will be able to pin down the point at which it goes
pear-shaped. Is so, the solution might involve saving the query that works,
and then building on that.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

What criteria do you have? Did you explicitly ask for Nulls?

If you have criteria under a field from the outer side of the join, try
adding:
Or Is Null

messagenews:a6d15475-b183-4648-8e50-fcee6422cb8f@v56g2000hsf.googlegroups.com...

- Show quoted text -

Thanks for attempting to resolve my problem Allen, but I am still at
somewhat of a loss to know what is going on.  First of all, yes I did
have a criteria on the 'Many' side, and no I did not specifically ask
for any nulls.  So when I saw your suggested resolution to my problem
I slapped my forehead and thought 'ah, of course'.  However, when I
specifically asked for nulls as well as the row total only increased
by 30 to 100, rather then 610 which in the number of rows in the 'One'
side table.

Below is my SQL if it helps:

TRANSFORM Sum(HfL_AcuteIP_Detail.CountOfRUN_SEQ)
  AS SumOfCountOfRUN_SEQ
SELECT HfL_AcuteIP_Detail.CSP,
HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code AS HRG35
FROM HfL_ActFormat LEFT JOIN HfL_AcuteIP_Detail
  ON HfL_ActFormat.Code = HfL_AcuteIP_Detail.HRG35
WHERE (HfL_AcuteIP_Detail.CSP="ENT")
  Or (HfL_AcuteIP_Detail.CSP Is Null)
GROUP BY HfL_AcuteIP_Detail.CSP,
HfL_AcuteIP_Detail.PbR,
HfL_ActFormat.Code
ORDER BY HfL_AcuteIP_Detail.PbR DESC
PIVOT [pod] & " " & [Sex of patient] & " " & [FinalAgeBand]
IN ("ELORD Male 0-16","ELORD Male 17-39", ...- Hide quoted text -

- Show quoted text -

Allen, got it sorted, what I had to do was create a new query and
included the table whose rows I wanted all of and my crosstab query
(please see SQL above in earlier message) I joined them with a left
join and hey presto, all 610 rows come through.

Thanks for all your help.

Nick