Crosstab Query based on Ordinal Values

  • Thread starter Thread starter Jeff H
  • Start date Start date
J

Jeff H

I need to be able to run a crosstab query that will use ordinal values as the
column heading. However, the table I'm running the query against doesn't have
a field for the ordinal value.

Right now, I'm dumping the data out to Excel, adding an ordinal value column
and creating a pivot table. I would like to be able to do this in Access...if
I'm out someone else can run the resulting query. I don't have talk or write
a procedure to take them through all the steps. Thank you.

Table:
Recipient ID DHS Referral ID Referral Date
000000029718 REF-0029224 12-Mar-08
000000029718 REF-0042912 16-Dec-08
000000029718 REF-0048914 11-Feb-09
000000034274 REF-0030330 01-May-07
000000034274 REF-0042076 26-Sep-08
000000034665 REF-0022777 01-Jul-07
000000034665 REF-0049263 02-Apr-09
000000036951 REF-0021221 26-Nov-07
000000036951 REF-0042560 11-Dec-08
000000037621 REF-0031168 02-Apr-08
000000038466 REF-0030585 29-May-08
000000038466 REF-0053581 01-Jun-09
000000056227 REF-0046447 17-Feb-09
000000056227 REF-0048103 13-Mar-09
000000056227 REF-0053119 26-May-09
000000063681 REF-0028108 01-May-07
000000064165 REF-0034273 06-Aug-08
000000064785 REF-0023685 01-Jul-07
000000064785 REF-0046055 09-Feb-09
000000064785 REF-0048275 18-Mar-09
000000113824 REF-0017242 04-Feb-08
000000113824 REF-0053006 19-May-09
000000114740 REF-0028308 24-Aug-07
000000114740 REF-0032416 01-Jul-08
000000114740 REF-0039360 27-Oct-08
000000114740 REF-0050204 13-Apr-09
000000118559 REF-0031884 17-Jan-08
000000118559 REF-0033770 25-Jul-08
000000118559 REF-0042768 15-Dec-08
000000119130 REF-0022966 01-May-07
000000119296 REF-0023770 09-Nov-07
000000119296 REF-0036829 19-Sep-08


Resulting Crosstab:
Recipient ID 1st Referral 2nd Referral 3rd Referral 4th Referral
000000029718 3/12/2008 12/16/2008 2/11/2009
000000034274 5/1/2007 9/26/2008
000000034665 7/1/2007 4/2/2009
000000036951 11/26/2007 12/11/2008
000000037621 4/2/2008
000000038466 5/29/2008 6/1/2009
000000056227 2/17/2009 3/13/2009 5/26/2009
000000063681 5/1/2007
000000064165 8/6/2008
000000064785 7/1/2007 2/9/2009 3/18/2009
000000113824 2/4/2008 5/19/2009
000000114740 8/24/2007 7/1/2008 10/27/2008 4/13/2009
000000118559 1/17/2008 7/25/2008 12/15/2008
000000119130 5/1/2007
000000119296 11/9/2007 9/19/2008
 
I learned this from John Spencer.

Start by creating a ranking query "qselRankReferrals":

SELECT tblJeffH.[Recipient ID], tblJeffH.[DHS Referral ID],
tblJeffH.[Referral Date], Count(tblJeffH_1.[Recipient ID]) AS
[CountOfRecipient ID]
FROM tblJeffH AS tblJeffH_1 INNER JOIN tblJeffH ON tblJeffH_1.[Recipient ID]
= tblJeffH.[Recipient ID]
WHERE (((tblJeffH_1.[Referral Date])<=[tblJeffH]![Referral Date]))
GROUP BY tblJeffH.[Recipient ID], tblJeffH.[DHS Referral ID],
tblJeffH.[Referral Date]
ORDER BY tblJeffH.[Recipient ID], tblJeffH.[Referral Date];

Then create a crosstab based on the previous query:

TRANSFORM First(qselRankReferrals.[Referral Date]) AS [FirstOfReferral Date]
SELECT qselRankReferrals.[Recipient ID]
FROM qselRankReferrals
GROUP BY qselRankReferrals.[Recipient ID]
PIVOT [CountOfRecipient ID] & "Referral";
 
I'm not following what you mean by ranking in a group query. Group query I'm
OK with, ranking that's confusing me. That's why I'm dumping the data into
Excel and ranking there.
 
Duane,

Is tblJeffH_1 a second copy of the same table?

Duane Hookom said:
I learned this from John Spencer.

Start by creating a ranking query "qselRankReferrals":

SELECT tblJeffH.[Recipient ID], tblJeffH.[DHS Referral ID],
tblJeffH.[Referral Date], Count(tblJeffH_1.[Recipient ID]) AS
[CountOfRecipient ID]
FROM tblJeffH AS tblJeffH_1 INNER JOIN tblJeffH ON tblJeffH_1.[Recipient ID]
= tblJeffH.[Recipient ID]
WHERE (((tblJeffH_1.[Referral Date])<=[tblJeffH]![Referral Date]))
GROUP BY tblJeffH.[Recipient ID], tblJeffH.[DHS Referral ID],
tblJeffH.[Referral Date]
ORDER BY tblJeffH.[Recipient ID], tblJeffH.[Referral Date];

Then create a crosstab based on the previous query:

TRANSFORM First(qselRankReferrals.[Referral Date]) AS [FirstOfReferral Date]
SELECT qselRankReferrals.[Recipient ID]
FROM qselRankReferrals
GROUP BY qselRankReferrals.[Recipient ID]
PIVOT [CountOfRecipient ID] & "Referral";

--
Duane Hookom
Microsoft Access MVP


Jeff H said:
I need to be able to run a crosstab query that will use ordinal values as the
column heading. However, the table I'm running the query against doesn't have
a field for the ordinal value.

Right now, I'm dumping the data out to Excel, adding an ordinal value column
and creating a pivot table. I would like to be able to do this in Access...if
I'm out someone else can run the resulting query. I don't have talk or write
a procedure to take them through all the steps. Thank you.

Table:
Recipient ID DHS Referral ID Referral Date
000000029718 REF-0029224 12-Mar-08
000000029718 REF-0042912 16-Dec-08
000000029718 REF-0048914 11-Feb-09
000000034274 REF-0030330 01-May-07
000000034274 REF-0042076 26-Sep-08
000000034665 REF-0022777 01-Jul-07
000000034665 REF-0049263 02-Apr-09
000000036951 REF-0021221 26-Nov-07
000000036951 REF-0042560 11-Dec-08
000000037621 REF-0031168 02-Apr-08
000000038466 REF-0030585 29-May-08
000000038466 REF-0053581 01-Jun-09
000000056227 REF-0046447 17-Feb-09
000000056227 REF-0048103 13-Mar-09
000000056227 REF-0053119 26-May-09
000000063681 REF-0028108 01-May-07
000000064165 REF-0034273 06-Aug-08
000000064785 REF-0023685 01-Jul-07
000000064785 REF-0046055 09-Feb-09
000000064785 REF-0048275 18-Mar-09
000000113824 REF-0017242 04-Feb-08
000000113824 REF-0053006 19-May-09
000000114740 REF-0028308 24-Aug-07
000000114740 REF-0032416 01-Jul-08
000000114740 REF-0039360 27-Oct-08
000000114740 REF-0050204 13-Apr-09
000000118559 REF-0031884 17-Jan-08
000000118559 REF-0033770 25-Jul-08
000000118559 REF-0042768 15-Dec-08
000000119130 REF-0022966 01-May-07
000000119296 REF-0023770 09-Nov-07
000000119296 REF-0036829 19-Sep-08


Resulting Crosstab:
Recipient ID 1st Referral 2nd Referral 3rd Referral 4th Referral
000000029718 3/12/2008 12/16/2008 2/11/2009
000000034274 5/1/2007 9/26/2008
000000034665 7/1/2007 4/2/2009
000000036951 11/26/2007 12/11/2008
000000037621 4/2/2008
000000038466 5/29/2008 6/1/2009
000000056227 2/17/2009 3/13/2009 5/26/2009
000000063681 5/1/2007
000000064165 8/6/2008
000000064785 7/1/2007 2/9/2009 3/18/2009
000000113824 2/4/2008 5/19/2009
000000114740 8/24/2007 7/1/2008 10/27/2008 4/13/2009
000000118559 1/17/2008 7/25/2008 12/15/2008
000000119130 5/1/2007
000000119296 11/9/2007 9/19/2008
Click to expand...
Click to expand...
 
Yes the table is the same. Next time, it would help if you provided the
actual names of tables and fields.
--
Duane Hookom
Microsoft Access MVP


Jeff H said:
Duane,

Is tblJeffH_1 a second copy of the same table?

Duane Hookom said:
I learned this from John Spencer.

Start by creating a ranking query "qselRankReferrals":

SELECT tblJeffH.[Recipient ID], tblJeffH.[DHS Referral ID],
tblJeffH.[Referral Date], Count(tblJeffH_1.[Recipient ID]) AS
[CountOfRecipient ID]
FROM tblJeffH AS tblJeffH_1 INNER JOIN tblJeffH ON tblJeffH_1.[Recipient ID]
= tblJeffH.[Recipient ID]
WHERE (((tblJeffH_1.[Referral Date])<=[tblJeffH]![Referral Date]))
GROUP BY tblJeffH.[Recipient ID], tblJeffH.[DHS Referral ID],
tblJeffH.[Referral Date]
ORDER BY tblJeffH.[Recipient ID], tblJeffH.[Referral Date];

Then create a crosstab based on the previous query:

TRANSFORM First(qselRankReferrals.[Referral Date]) AS [FirstOfReferral Date]
SELECT qselRankReferrals.[Recipient ID]
FROM qselRankReferrals
GROUP BY qselRankReferrals.[Recipient ID]
PIVOT [CountOfRecipient ID] & "Referral";

--
Duane Hookom
Microsoft Access MVP


Jeff H said:
I need to be able to run a crosstab query that will use ordinal values as the
column heading. However, the table I'm running the query against doesn't have
a field for the ordinal value.

Right now, I'm dumping the data out to Excel, adding an ordinal value column
and creating a pivot table. I would like to be able to do this in Access...if
I'm out someone else can run the resulting query. I don't have talk or write
a procedure to take them through all the steps. Thank you.

Table:
Recipient ID DHS Referral ID Referral Date
000000029718 REF-0029224 12-Mar-08
000000029718 REF-0042912 16-Dec-08
000000029718 REF-0048914 11-Feb-09
000000034274 REF-0030330 01-May-07
000000034274 REF-0042076 26-Sep-08
000000034665 REF-0022777 01-Jul-07
000000034665 REF-0049263 02-Apr-09
000000036951 REF-0021221 26-Nov-07
000000036951 REF-0042560 11-Dec-08
000000037621 REF-0031168 02-Apr-08
000000038466 REF-0030585 29-May-08
000000038466 REF-0053581 01-Jun-09
000000056227 REF-0046447 17-Feb-09
000000056227 REF-0048103 13-Mar-09
000000056227 REF-0053119 26-May-09
000000063681 REF-0028108 01-May-07
000000064165 REF-0034273 06-Aug-08
000000064785 REF-0023685 01-Jul-07
000000064785 REF-0046055 09-Feb-09
000000064785 REF-0048275 18-Mar-09
000000113824 REF-0017242 04-Feb-08
000000113824 REF-0053006 19-May-09
000000114740 REF-0028308 24-Aug-07
000000114740 REF-0032416 01-Jul-08
000000114740 REF-0039360 27-Oct-08
000000114740 REF-0050204 13-Apr-09
000000118559 REF-0031884 17-Jan-08
000000118559 REF-0033770 25-Jul-08
000000118559 REF-0042768 15-Dec-08
000000119130 REF-0022966 01-May-07
000000119296 REF-0023770 09-Nov-07
000000119296 REF-0036829 19-Sep-08


Resulting Crosstab:
Recipient ID 1st Referral 2nd Referral 3rd Referral 4th Referral
000000029718 3/12/2008 12/16/2008 2/11/2009
000000034274 5/1/2007 9/26/2008
000000034665 7/1/2007 4/2/2009
000000036951 11/26/2007 12/11/2008
000000037621 4/2/2008
000000038466 5/29/2008 6/1/2009
000000056227 2/17/2009 3/13/2009 5/26/2009
000000063681 5/1/2007
000000064165 8/6/2008
000000064785 7/1/2007 2/9/2009 3/18/2009
000000113824 2/4/2008 5/19/2009
000000114740 8/24/2007 7/1/2008 10/27/2008 4/13/2009
000000118559 1/17/2008 7/25/2008 12/15/2008
000000119130 5/1/2007
000000119296 11/9/2007 9/19/2008
Click to expand...
Click to expand...
Click to expand...
 
Back
Top