Another crosstab question...

  • Thread starter Thread starter 666
  • Start date Start date
6

666

I have the following crosstab query:

TRANSFORM CaseTracking.CaseNo AS CaseNo
SELECT DateDiff("ww", [DateForwarded], Date()) AS Weeks
FROM CaseTracking
WHERE (DateDiff("ww", [DateForwarded], Date())>0) AND
(CaseTracking.ForwardedLocation='Org1' OR
CaseTracking.ForwardedLocation='Org2' OR
CaseTracking.ForwardedLocation='Org3' OR
CaseTracking.ForwardedLocation='Org4' OR
CaseTracking.ForwardedLocation='Org5' OR
CaseTracking.ForwardedLocation='Org6')
GROUP BY DateDiff("ww", [DateForwarded], Date()), CaseNo
PIVOT CaseTracking.ForwardedLocation;


This works fine and is used to produce a report with the numbers of weeks a
case file has been at a specfic location as row headers, the location names
as row headers, and the case number as the actual data. As in:

Weeks Org1 Org2 Org3 Org4

1 CASE1 CASE2
2 CASE3
3 CASE2 CASE5

A case may be sent to several locations before it is closed. How can I
change this query so I only get the last location that received a case?
Currently the query returns every location the report went to (as you can
see with CASE2 above). I only want to see how long the file has been at it's
current location.

Hope that makes sense... haven't been able to figure this out... thanx for
any info...
 
I'm only guessing at the table structure, but you could try adding this to
the WHERE clause:

AND ([DateForwarded] =
(SELECT Max([DateForwarded]) FROM CaseTracking As CT2
WHERE CT2.CaseNo = CaseTracking.CaseNo))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

P.S. You might want to consider changing your newsgroup signature. Some
people place special significance on three sixes - and might avoid reading
or responding to your messages.
 
Thank you John...

I can see what your code is trying to do but I am having trouble adding it
to my crosstab query... this is what I have so far:

TRANSFORM CaseTracking.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE ((DateDiff("ww",[DateForwarded],Date())>0) AND
((CaseTracking.ForwardedLocation='Location1') Or
(CaseTracking.ForwardedLocation='Location2') Or
(CaseTracking.ForwardedLocation='Location3') Or
(CaseTracking.ForwardedLocation='Location4') Or
(CaseTracking.ForwardedLocation='Location5') Or
(CaseTracking.ForwardedLocation='Location6')) AND
CaseTracking.CaseNo=(SELECT MAX(CaseTracking2.DateForwarded) FROM
CaseTracking AS CaseTracking2 WHERE
CaseTracking2.CaseNo=CaseTracking.CaseNo))
GROUP BY DateDiff("ww",[DateForwarded],Date()), CaseTracking.CaseNo + ' : '
+ Cases.CaseTitle
PIVOT CaseTracking.ForwardedLocation;

It is complaining that it doesn't recognise CaseTracking.CaseNo in the WHERE
clause of the SELECT subquery that finds the max date... how do I provide a
reference from the sub query to it's parents data? Or I am approaching that
the wrong way.

Cheers for the help...



John Viescas said:
I'm only guessing at the table structure, but you could try adding this to
the WHERE clause:

AND ([DateForwarded] =
(SELECT Max([DateForwarded]) FROM CaseTracking As CT2
WHERE CT2.CaseNo = CaseTracking.CaseNo))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

P.S. You might want to consider changing your newsgroup signature. Some
people place special significance on three sixes - and might avoid reading
or responding to your messages.

666 said:
I have the following crosstab query:

TRANSFORM CaseTracking.CaseNo AS CaseNo
SELECT DateDiff("ww", [DateForwarded], Date()) AS Weeks
FROM CaseTracking
WHERE (DateDiff("ww", [DateForwarded], Date())>0) AND
(CaseTracking.ForwardedLocation='Org1' OR
CaseTracking.ForwardedLocation='Org2' OR
CaseTracking.ForwardedLocation='Org3' OR
CaseTracking.ForwardedLocation='Org4' OR
CaseTracking.ForwardedLocation='Org5' OR
CaseTracking.ForwardedLocation='Org6')
GROUP BY DateDiff("ww", [DateForwarded], Date()), CaseNo
PIVOT CaseTracking.ForwardedLocation;


This works fine and is used to produce a report with the numbers of
Click to expand...
weeks
a
case file has been at a specfic location as row headers, the location names
as row headers, and the case number as the actual data. As in:

Weeks Org1 Org2 Org3 Org4

1 CASE1 CASE2
2 CASE3
3 CASE2 CASE5

A case may be sent to several locations before it is closed. How can I
change this query so I only get the last location that received a case?
Currently the query returns every location the report went to (as you can
see with CASE2 above). I only want to see how long the file has been at it's
current location.

Hope that makes sense... haven't been able to figure this out... thanx for
any info...
Click to expand...
Click to expand...
 
Close, but no cigar. You're asking it to compare the CaseNo to
DateForwarded. Try this:

TRANSFORM CaseTracking.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE ( DateDiff("ww",[DateForwarded],Date()) > 0 ) AND
(CaseTracking.ForwardedLocation IN
('Location1', 'Location2', 'Location3', 'Location4', 'Location5',
'Location6') ) AND
( CaseTracking.DateForwarded = (SELECT MAX(DateForwarded) FROM
CaseTracking AS CaseTracking2 WHERE
CaseTracking2.CaseNo=CaseTracking.CaseNo) )
GROUP BY DateDiff("ww",[DateForwarded],Date()), CaseTracking.CaseNo + ' : '
+ Cases.CaseTitle
PIVOT CaseTracking.ForwardedLocation;

If it's still complaining about not being able to recognize
CaseTracking.CaseNo in the subquery, you might need to break this out into a
simple Select query that you then use as the input to the Crosstab.

SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks, CaseTracking.CaseNo,
Cases.CaseTitle, CaseTracking.ForwardedLocation
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE ( DateDiff("ww",[DateForwarded],Date()) > 0 ) AND
(CaseTracking.ForwardedLocation IN
('Location1', 'Location2', 'Location3', 'Location4', 'Location5',
'Location6') ) AND
( CaseTracking.DateForwarded = (SELECT MAX(DateForwarded) FROM
CaseTracking AS CaseTracking2 WHERE
CaseTracking2.CaseNo=CaseTracking.CaseNo) )

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
666 said:
Thank you John...

I can see what your code is trying to do but I am having trouble adding it
to my crosstab query... this is what I have so far:

TRANSFORM CaseTracking.CaseNo + ' : ' + Cases.CaseTitle AS CaseNo
SELECT DateDiff("ww",[DateForwarded],Date()) AS Weeks
FROM CaseTracking INNER JOIN Cases ON CaseTracking.CaseNo = Cases.CaseNo
WHERE ((DateDiff("ww",[DateForwarded],Date())>0) AND
((CaseTracking.ForwardedLocation='Location1') Or
(CaseTracking.ForwardedLocation='Location2') Or
(CaseTracking.ForwardedLocation='Location3') Or
(CaseTracking.ForwardedLocation='Location4') Or
(CaseTracking.ForwardedLocation='Location5') Or
(CaseTracking.ForwardedLocation='Location6')) AND
CaseTracking.CaseNo=(SELECT MAX(CaseTracking2.DateForwarded) FROM
CaseTracking AS CaseTracking2 WHERE
CaseTracking2.CaseNo=CaseTracking.CaseNo))
GROUP BY DateDiff("ww",[DateForwarded],Date()), CaseTracking.CaseNo + ' : '
+ Cases.CaseTitle
PIVOT CaseTracking.ForwardedLocation;

It is complaining that it doesn't recognise CaseTracking.CaseNo in the WHERE
clause of the SELECT subquery that finds the max date... how do I provide a
reference from the sub query to it's parents data? Or I am approaching that
the wrong way.

Cheers for the help...



John Viescas said:
I'm only guessing at the table structure, but you could try adding this to
the WHERE clause:

AND ([DateForwarded] =
(SELECT Max([DateForwarded]) FROM CaseTracking As CT2
WHERE CT2.CaseNo = CaseTracking.CaseNo))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

P.S. You might want to consider changing your newsgroup signature. Some
people place special significance on three sixes - and might avoid reading
or responding to your messages.

666 said:
I have the following crosstab query:

TRANSFORM CaseTracking.CaseNo AS CaseNo
SELECT DateDiff("ww", [DateForwarded], Date()) AS Weeks
FROM CaseTracking
WHERE (DateDiff("ww", [DateForwarded], Date())>0) AND
(CaseTracking.ForwardedLocation='Org1' OR
CaseTracking.ForwardedLocation='Org2' OR
CaseTracking.ForwardedLocation='Org3' OR
CaseTracking.ForwardedLocation='Org4' OR
CaseTracking.ForwardedLocation='Org5' OR
CaseTracking.ForwardedLocation='Org6')
GROUP BY DateDiff("ww", [DateForwarded], Date()), CaseNo
PIVOT CaseTracking.ForwardedLocation;


This works fine and is used to produce a report with the numbers of
Click to expand...
weeks
a
case file has been at a specfic location as row headers, the location names
as row headers, and the case number as the actual data. As in:

Weeks Org1 Org2 Org3 Org4

1 CASE1 CASE2
2 CASE3
3 CASE2 CASE5

A case may be sent to several locations before it is closed. How can I
change this query so I only get the last location that received a case?
Currently the query returns every location the report went to (as you can
see with CASE2 above). I only want to see how long the file has been
Click to expand...
at
it's
current location.

Hope that makes sense... haven't been able to figure this out... thanx for
any info...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top