Count Record Twice in Crosstab Query 2

[betachristopher]

I am running a crosstab query and am having a problem getting the totals I am looking for. The crosstab works fine, but I am trying to get monthly totals of a specific lab test, and I need it to count some records in more than one place.

In my table, there is a field that lists one of the following: High Risk, Low Risk or Both. I want to show totals of the High Risk and Low Risk. If a record is listed as Both, I want it added to the total of both the High Risk and Low Risk.

I added a field in my query to properly label the totals: Switch(TABLE!FIELD In (“High Risk”, “Both”),"HR", TABLE!FIELD In (“Low Risk”, “Both”),"LR")

This doesn’t work because it first counts anything marked as “Both” as “HR” and doesn’t count it again as “LR”.

If I make these two separate queries and create a union query, I am able to get the results I want, but I am looking for a better solution.

I am using MS Access 2007 with a SQL 2000 backend. I hope this is enough information to help me.

 

[lespaul]

How about:

SELECT COUNT(IIF(TABLE!FIELD In (“High Risk”, “Both”), 1, 0)) As HighRisk, COUNT(IIF(TABLE!FIELD In (“Low Risk”, “Both”), 1, 0)) As LowRisk FROM TableName

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[betachristopher]

Thank you. This didn't work either. It just made both the High Risk and Low Risk counts the same no matter what.

 

[dhookom]

Change Leslie's solution to Sum() rather than Count().

Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

Thank you both. The Sum does work, and I am able to get the totals I am looking for.

However, I had to make it a select query instead of a crosstab query because I can only have one Value in a crosstab.

I previously had the data set to display as:
Jan Feb Mar
High Risk 18 22 10
Low Risk 17 20 5

Now it is displayed as:
High Risk Low Risk
Jan 18 17
Feb 22 20
Mar 10 5

I can work with this if needed, but what can I do to display it as before?

 

[lespaul]

my bad, must have been too early to think straight! thanks for sorting that out Duane!

Leslie

 

[dhookom]

Provide the SQL view of your previous crosstab query. Generally if you ask a question in the Query forum, we need to know your significant tables/fields as well as SQL views that you need fixed.

Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

No problem, I just thought that might be too much info so I tried to be general.

here is the previous query

TRANSFORM Count(TEST.NO) AS TOTAL
SELECT RTrim(UCase(LOCATION.DSCR)) AS PRACTICE, RTrim(UCase(DOCTOR.NAME)) AS PHYSICIAN, Switch(TESTTYPE.CODE="P" And TEST.FIELD_146=" ","PAP-TP",TESTTYPE.CODE="P" And TEST.FIELD_146="D","PAP-DNA",TESTTYPE.CODE="H" And TEST.LINKED03 In (11680,11681),"HPV-HR",TESTTYPE.CODE="H" And TEST.LINKED03 In (11679,11681),"HPV-LR") AS [ORDER]
FROM (((TEST INNER JOIN PROJECT ON TEST.PROJECT = PROJECT.NO) INNER JOIN TESTTYPE ON TEST.TESTTYPE = TESTTYPE.NO) INNER JOIN LOCATION ON TEST.LOCATION = LOCATION.NO) INNER JOIN DOCTOR ON TEST.DOCTOR = DOCTOR.NO
WHERE (((Year(TEST.COLLECT_DT))=2007) AND ((TEST.PROJTYPE)="I") AND ((TESTTYPE.CODE)="P") AND ((TEST.FIELD_146)=" ")) OR (((Year(TEST.COLLECT_DT))=2007) AND ((TEST.PROJTYPE)="I") AND ((TESTTYPE.CODE)="P") AND ((TEST.FIELD_146)="D")) OR (((Year(TEST.COLLECT_DT))=2007) AND ((TEST.PROJTYPE)="I") AND ((TESTTYPE.CODE)="H") AND ((TEST.LINKED03)=11680 Or (TEST.LINKED03)=11681)) OR (((Year(TEST.COLLECT_DT))=2007) AND ((TEST.PROJTYPE)="I") AND ((TESTTYPE.CODE)="H") AND ((TEST.LINKED03)=11679 Or (TEST.LINKED03)=11681))
GROUP BY RTrim(UCase(LOCATION.DSCR)), RTrim(UCase(DOCTOR.NAME)), Switch(TESTTYPE.CODE="P" And TEST.FIELD_146=" ","PAP-TP",TESTTYPE.CODE="P" And TEST.FIELD_146="D","PAP-DNA",TESTTYPE.CODE="H" And TEST.LINKED03 In (11680,11681),"HPV-HR",TESTTYPE.CODE="H" And TEST.LINKED03 In (11679,11681),"HPV-LR")
ORDER BY RTrim(UCase(LOCATION.DSCR)), RTrim(UCase(DOCTOR.NAME))
PIVOT Format(TEST.COLLECT_DT,"mmmm");

here is the new query:

SELECT RTrim(UCase(LOCATION.DSCR)) AS PRACTICE, RTrim(UCase(DOCTOR.NAME)) AS PHYSICIAN, Format(TEST.COLLECT_DT,"mmmm") AS MONTH, Sum(IIf(TESTTYPE.CODE="P" And TEST.FIELD_146=" ",1,0)) AS PAPTP, Sum(IIf(TESTTYPE.CODE="P" And TEST.FIELD_146="D",1,0)) AS PAPDNA, Sum(IIf(TESTTYPE.CODE="H" And TEST.LINKED03 In (11680,11681),1,0)) AS HPVHR, Sum(IIf(TESTTYPE.CODE="H" And TEST.LINKED03 In (11679,11681),1,0)) AS HPVLR, DatePart("m",TEST.COLLECT_DT) AS MON
FROM (((TEST INNER JOIN PROJECT ON TEST.PROJECT = PROJECT.NO) INNER JOIN TESTTYPE ON TEST.TESTTYPE = TESTTYPE.NO) INNER JOIN LOCATION ON TEST.LOCATION = LOCATION.NO) INNER JOIN DOCTOR ON TEST.DOCTOR = DOCTOR.NO
WHERE (((Year(TEST.COLLECT_DT))=2007) AND ((TEST.PROJTYPE)="I") AND ((TESTTYPE.CODE)="P" Or (TESTTYPE.CODE)="H"))
GROUP BY RTrim(UCase(LOCATION.DSCR)), RTrim(UCase(DOCTOR.NAME)), Format(TEST.COLLECT_DT,"mmmm"), DatePart("m",TEST.COLLECT_DT)
ORDER BY RTrim(UCase(LOCATION.DSCR)), RTrim(UCase(DOCTOR.NAME)), DatePart("m",TEST.COLLECT_DT);

 

[dhookom]

I am assuming the risk information is stored in the field DSCR. I would create a table that has four records
[tt][blue]
tblDSCRRisk
DSCR Risk
---------- ----------
High Risk High Risk
Low Risk Low Risk
Both High Risk
Both Low Risk
[/blue][/tt]

Then add this table to your existing crosstab and join the DSCR fields. Replace [tt][red]RTrim(UCase(LOCATION.DSCR)) AS PRACTICE[/red][/tt] with [tt][red]tblDSCRRisk.Risk AS PRACTICE[/red][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

Actually the risk information is stored in the field TEST.LINKED03 where 11679=Low, 11680=High, and 11681=Both (sorry for not being clear).

But what you have listed here makes sense and seems simple enough. I usually don't like to create extra tables just for use in a query, but it looks like I will need to make an exception in this case.

Thank you all for your help!

 

[dhookom]

I count about a dozen "hard-coded" values in your sql synatax. I like to keep this to a very minimum by creating or using existing tables. You might want to check out this very informative link Data belongs in your tables -- not in your code.

Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

Thank you for the valuable advice. You're right, it does make more sense this way.