Cross tab query, column heading

G

Guest

Hi,
I have a cross tab query with hour of the day as column heading.
The result shows hours, 0,1,2,3……..23 as columns, a total of 24 columns, I
am trying to group hour 0 to 5 together as one group, and shows as one
column, so the result would shows 0 to 5 (one column), 6,7…….23.
Is this possible to do in Access? Can someone please help?

Thanks

JH
 
G

Guest

My SQL:

TRANSFORM Count([SYSTEM_PATIENT_VISITS]![ACCESSION_NUMBER]) AS PatientCount
SELECT Val([SYSTEM_PATIENT_VISITS]![GL_CODE]) AS GL_CODE,
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]) AS [Service Mth #],
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])) AS [Mth
Name], WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]))
AS [Day Name],
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd") AS
[Service Date]
FROM (SYSTEM_PATIENT_VISITS INNER JOIN SYSTEM_WORK_LOCATIONS ON
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE =
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_CODE) INNER JOIN
SYSTEM_ACCOUNTING_LOCATIONS ON SYSTEM_WORK_LOCATIONS.ACCOUNTING_LOCATION_CODE
= SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE
WHERE (((SYSTEM_PATIENT_VISITS.PAT_TYPE)="P") AND
((SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE)<>"67") AND
((SYSTEM_PATIENT_VISITS.LAB_XRAY)="L") AND
((SYSTEM_PATIENT_VISITS.ACCESSION_SERVICE_DATE) Between #2/1/2007# And
#2/23/2007#))
GROUP BY Val([SYSTEM_PATIENT_VISITS]![GL_CODE]),
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]),
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd")
ORDER BY Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])
PIVOT Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]);

JH
 
G

Guest

There is a problem in that zero thru five is six hours. In the partiton
function below if you use "0" instead of "1" then it will group 0-4 as lowest
group.

PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),1,24,5);

--
KARL DEWEY
Build a little - Test a little


JH said:
My SQL:

TRANSFORM Count([SYSTEM_PATIENT_VISITS]![ACCESSION_NUMBER]) AS PatientCount
SELECT Val([SYSTEM_PATIENT_VISITS]![GL_CODE]) AS GL_CODE,
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]) AS [Service Mth #],
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])) AS [Mth
Name], WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]))
AS [Day Name],
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd") AS
[Service Date]
FROM (SYSTEM_PATIENT_VISITS INNER JOIN SYSTEM_WORK_LOCATIONS ON
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE =
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_CODE) INNER JOIN
SYSTEM_ACCOUNTING_LOCATIONS ON SYSTEM_WORK_LOCATIONS.ACCOUNTING_LOCATION_CODE
= SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE
WHERE (((SYSTEM_PATIENT_VISITS.PAT_TYPE)="P") AND
((SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE)<>"67") AND
((SYSTEM_PATIENT_VISITS.LAB_XRAY)="L") AND
((SYSTEM_PATIENT_VISITS.ACCESSION_SERVICE_DATE) Between #2/1/2007# And
#2/23/2007#))
GROUP BY Val([SYSTEM_PATIENT_VISITS]![GL_CODE]),
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]),
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd")
ORDER BY Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])
PIVOT Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]);

JH

KARL DEWEY said:
Post your crosstab SQL.
Click to expand...
Click to expand...
 
G

Guest

Better would be in 6 hour groups --
PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),0,24,6);

0 - 5
6 - 11
12 - 17
18 - 23
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
There is a problem in that zero thru five is six hours. In the partiton
function below if you use "0" instead of "1" then it will group 0-4 as lowest
group.

PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),1,24,5);

--
KARL DEWEY
Build a little - Test a little


JH said:
My SQL:

TRANSFORM Count([SYSTEM_PATIENT_VISITS]![ACCESSION_NUMBER]) AS PatientCount
SELECT Val([SYSTEM_PATIENT_VISITS]![GL_CODE]) AS GL_CODE,
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]) AS [Service Mth #],
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])) AS [Mth
Name], WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]))
AS [Day Name],
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd") AS
[Service Date]
FROM (SYSTEM_PATIENT_VISITS INNER JOIN SYSTEM_WORK_LOCATIONS ON
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE =
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_CODE) INNER JOIN
SYSTEM_ACCOUNTING_LOCATIONS ON SYSTEM_WORK_LOCATIONS.ACCOUNTING_LOCATION_CODE
= SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE
WHERE (((SYSTEM_PATIENT_VISITS.PAT_TYPE)="P") AND
((SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE)<>"67") AND
((SYSTEM_PATIENT_VISITS.LAB_XRAY)="L") AND
((SYSTEM_PATIENT_VISITS.ACCESSION_SERVICE_DATE) Between #2/1/2007# And
#2/23/2007#))
GROUP BY Val([SYSTEM_PATIENT_VISITS]![GL_CODE]),
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]),
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd")
ORDER BY Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])
PIVOT Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]);

JH

KARL DEWEY said:
Post your crosstab SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi,
I have a cross tab query with hour of the day as column heading.
The result shows hours, 0,1,2,3……..23 as columns, a total of 24 columns, I
am trying to group hour 0 to 5 together as one group, and shows as one
column, so the result would shows 0 to 5 (one column), 6,7…….23.
Is this possible to do in Access? Can someone please help?

Thanks

JH
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for your help, I was hoping that I can have just one group of 0-5
hour, and the rest as each hour in one column, like 0-5, 6,7,8.....24. I am
just going to that little part in Excel.

Thanks again.

JH

KARL DEWEY said:
Better would be in 6 hour groups --
PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),0,24,6);

0 - 5
6 - 11
12 - 17
18 - 23
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
There is a problem in that zero thru five is six hours. In the partiton
function below if you use "0" instead of "1" then it will group 0-4 as lowest
group.

PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),1,24,5);

--
KARL DEWEY
Build a little - Test a little


JH said:
My SQL:

TRANSFORM Count([SYSTEM_PATIENT_VISITS]![ACCESSION_NUMBER]) AS PatientCount
SELECT Val([SYSTEM_PATIENT_VISITS]![GL_CODE]) AS GL_CODE,
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]) AS [Service Mth #],
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])) AS [Mth
Name], WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]))
AS [Day Name],
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd") AS
[Service Date]
FROM (SYSTEM_PATIENT_VISITS INNER JOIN SYSTEM_WORK_LOCATIONS ON
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE =
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_CODE) INNER JOIN
SYSTEM_ACCOUNTING_LOCATIONS ON SYSTEM_WORK_LOCATIONS.ACCOUNTING_LOCATION_CODE
= SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE
WHERE (((SYSTEM_PATIENT_VISITS.PAT_TYPE)="P") AND
((SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE)<>"67") AND
((SYSTEM_PATIENT_VISITS.LAB_XRAY)="L") AND
((SYSTEM_PATIENT_VISITS.ACCESSION_SERVICE_DATE) Between #2/1/2007# And
#2/23/2007#))
GROUP BY Val([SYSTEM_PATIENT_VISITS]![GL_CODE]),
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]),
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd")
ORDER BY Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])
PIVOT Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]);

JH

:

Post your crosstab SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi,
I have a cross tab query with hour of the day as column heading.
The result shows hours, 0,1,2,3……..23 as columns, a total of 24 columns, I
am trying to group hour 0 to 5 together as one group, and shows as one
column, so the result would shows 0 to 5 (one column), 6,7…….23.
Is this possible to do in Access? Can someone please help?

Thanks

JH
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I did not read it right - you can have your cake and eat it too.

PIVOT
IIF(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]) Between 0 AND 5,
"0 - 5", Right("0"&
Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),2));

--
KARL DEWEY
Build a little - Test a little


JH said:
Thanks for your help, I was hoping that I can have just one group of 0-5
hour, and the rest as each hour in one column, like 0-5, 6,7,8.....24. I am
just going to that little part in Excel.

Thanks again.

JH

KARL DEWEY said:
Better would be in 6 hour groups --
PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),0,24,6);

0 - 5
6 - 11
12 - 17
18 - 23
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
There is a problem in that zero thru five is six hours. In the partiton
function below if you use "0" instead of "1" then it will group 0-4 as lowest
group.

PIVOT
Partition(Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]),1,24,5);

--
KARL DEWEY
Build a little - Test a little


:

My SQL:

TRANSFORM Count([SYSTEM_PATIENT_VISITS]![ACCESSION_NUMBER]) AS PatientCount
SELECT Val([SYSTEM_PATIENT_VISITS]![GL_CODE]) AS GL_CODE,
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]) AS [Service Mth #],
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])) AS [Mth
Name], WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]))
AS [Day Name],
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd") AS
[Service Date]
FROM (SYSTEM_PATIENT_VISITS INNER JOIN SYSTEM_WORK_LOCATIONS ON
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE =
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_CODE) INNER JOIN
SYSTEM_ACCOUNTING_LOCATIONS ON SYSTEM_WORK_LOCATIONS.ACCOUNTING_LOCATION_CODE
= SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE
WHERE (((SYSTEM_PATIENT_VISITS.PAT_TYPE)="P") AND
((SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_CODE)<>"67") AND
((SYSTEM_PATIENT_VISITS.LAB_XRAY)="L") AND
((SYSTEM_PATIENT_VISITS.ACCESSION_SERVICE_DATE) Between #2/1/2007# And
#2/23/2007#))
GROUP BY Val([SYSTEM_PATIENT_VISITS]![GL_CODE]),
SYSTEM_ACCOUNTING_LOCATIONS.ACCOUNTING_LOCATION_NAME,
SYSTEM_PATIENT_VISITS.ACCESSION_PREFIX_CODE,
SYSTEM_WORK_LOCATIONS.WORK_LOCATION_NAME, SYSTEM_PATIENT_VISITS.SERVICE_YEAR,
Month([ACCESSION_SERVICE_DATE]),
MonthName(Month([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
WeekdayName(Weekday([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])),
Format([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE],"yyyymmdd")
ORDER BY Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE])
PIVOT Hour([SYSTEM_PATIENT_VISITS]![ACCESSION_SERVICE_DATE]);

JH

:

Post your crosstab SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi,
I have a cross tab query with hour of the day as column heading.
The result shows hours, 0,1,2,3……..23 as columns, a total of 24 columns, I
am trying to group hour 0 to 5 together as one group, and shows as one
column, so the result would shows 0 to 5 (one column), 6,7…….23.
Is this possible to do in Access? Can someone please help?

Thanks

JH
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cross-tab to look like Calendar 1
Crosstab Query Question 5
CrossTab Query How Do I change Column Headings... 2
Cross Tab Query...Help 4
Crosstab Query Column headings 4
Cross tab query solution 4
Cross tab heading update issue 7
CrossTab: Time Block/interval 2

Top