Order Query By TIME HELP?

  • Thread starter Thread starter philip260
  • Start date Start date
P

philip260

Im running a crosstab query that is pullling data from a table that has
records on a 30min basis. i wanted to pull the data and format the
query to give me the result of how many contracts are received for the
day in the totals column, and then have time columns from 8:00 AM to
7:30PM and show how many contracts are received for each 30 min
intervals in their respective columns. The result of the query pulls in
the data that im looking for but not in the order that im looking for.
Instead of starting with 8:00:00AM column it starts with 1:00:00PM. It
looks like its running on the string and not on the AM/PM time format.
Any help? Im looking for a way to order it correctly starting from
8:00:00AM to 7:30:00PM.


Please see my query below

TRANSFORM Sum(forecast.ORIG_CONTACTS_RCVD) AS Calls
SELECT contacttype.CT_NAME AS Expr1, Sum(forecast.ORIG_CONTACTS_RCVD)
AS Total
FROM contacttype INNER JOIN forecast ON contacttype.CT_ID =
forecast.CT_ID
WHERE (((contacttype.CT_NAME)="Pensions") AND
((Format([DATE_TIME],"Short Date"))=#10/9/2006#) AND
((Format([DATE_TIME],"Long Time")) In (#12/30/1899 8:0:0#,#12/30/1899
8:30:0#,#12/30/1899 9:0:0#,#12/30/1899 9:30:0#,#12/30/1899
10:0:0#,#12/30/1899 10:30:0#,#12/30/1899 11:0:0#,#12/30/1899
11:30:0#,#12/30/1899 12:0:0#,#12/30/1899 12:30:0#,#12/30/1899
13:0:0#,#12/30/1899 13:30:0#,#12/30/1899 14:0:0#,#12/30/1899
14:30:0#,#12/30/1899 15:0:0#,#12/30/1899 15:30:0#,#12/30/1899
16:0:0#,#12/30/1899 16:30:0#,#12/30/1899 17:0:0#,#12/30/1899
17:30:0#,#12/30/1899 18:0:0#,#12/30/1899 18:30:0#,#12/30/1899
19:0:0#,#12/30/1899 19:30:0#)))
GROUP BY contacttype.CT_NAME
PIVOT Format([DATE_TIME],"Long Time");

this is what it currently looks like to give you guys a picture.

Expr1 Total 1:00:00 PM 1:30:00 PM 8:00:00 AM 8:30:00AM
Pensions 50 2 3
4 5


this is what i want it to look like.

Expr1 Total 8:00:00 AM 8:30:00 AM 1:00:00 PM 1:30:00PM
Pensions 50 4 5
2 3

Any ideas? help would be awesome.

-Phil
 
One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'8:00:00AM','8:30:00AM','9:00:00AM','9:30:00AM', and so on until '7:30:00PM'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want. You can also make data not show up by
taking out a column. For example, remove 9:30:00AM and its data won't show.
Also it will create an empty column even if there isn't any data for
'8:30:00AM'.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Im running a crosstab query that is pullling data from a table that has
records on a 30min basis. i wanted to pull the data and format the
query to give me the result of how many contracts are received for the
day in the totals column, and then have time columns from 8:00 AM to
7:30PM and show how many contracts are received for each 30 min
intervals in their respective columns. The result of the query pulls in
the data that im looking for but not in the order that im looking for.
Instead of starting with 8:00:00AM column it starts with 1:00:00PM. It
looks like its running on the string and not on the AM/PM time format.
Any help? Im looking for a way to order it correctly starting from
8:00:00AM to 7:30:00PM.


Please see my query below

TRANSFORM Sum(forecast.ORIG_CONTACTS_RCVD) AS Calls
SELECT contacttype.CT_NAME AS Expr1, Sum(forecast.ORIG_CONTACTS_RCVD)
AS Total
FROM contacttype INNER JOIN forecast ON contacttype.CT_ID =
forecast.CT_ID
WHERE (((contacttype.CT_NAME)="Pensions") AND
((Format([DATE_TIME],"Short Date"))=#10/9/2006#) AND
((Format([DATE_TIME],"Long Time")) In (#12/30/1899 8:0:0#,#12/30/1899
8:30:0#,#12/30/1899 9:0:0#,#12/30/1899 9:30:0#,#12/30/1899
10:0:0#,#12/30/1899 10:30:0#,#12/30/1899 11:0:0#,#12/30/1899
11:30:0#,#12/30/1899 12:0:0#,#12/30/1899 12:30:0#,#12/30/1899
13:0:0#,#12/30/1899 13:30:0#,#12/30/1899 14:0:0#,#12/30/1899
14:30:0#,#12/30/1899 15:0:0#,#12/30/1899 15:30:0#,#12/30/1899
16:0:0#,#12/30/1899 16:30:0#,#12/30/1899 17:0:0#,#12/30/1899
17:30:0#,#12/30/1899 18:0:0#,#12/30/1899 18:30:0#,#12/30/1899
19:0:0#,#12/30/1899 19:30:0#)))
GROUP BY contacttype.CT_NAME
PIVOT Format([DATE_TIME],"Long Time");

this is what it currently looks like to give you guys a picture.

Expr1 Total 1:00:00 PM 1:30:00 PM 8:00:00 AM 8:30:00AM
Pensions 50 2 3
4 5


this is what i want it to look like.

Expr1 Total 8:00:00 AM 8:30:00 AM 1:00:00 PM 1:30:00PM
Pensions 50 4 5
2 3

Any ideas? help would be awesome.

-Phil
Click to expand...
 
One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'8:00:00AM','8:30:00AM','9:00:00AM','9:30:00AM', and so on until '7:30:00PM'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want. You can also make data not show up by
taking out a column. For example, remove 9:30:00AM and its data won't show.
Also it will create an empty column even if there isn't any data for
'8:30:00AM'.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Im running a crosstab query that is pullling data from a table that has
records on a 30min basis. i wanted to pull the data and format the
query to give me the result of how many contracts are received for the
day in the totals column, and then have time columns from 8:00 AM to
7:30PM and show how many contracts are received for each 30 min
intervals in their respective columns. The result of the query pulls in
the data that im looking for but not in the order that im looking for.
Instead of starting with 8:00:00AM column it starts with 1:00:00PM. It
looks like its running on the string and not on the AM/PM time format.
Any help? Im looking for a way to order it correctly starting from
8:00:00AM to 7:30:00PM.


Please see my query below

TRANSFORM Sum(forecast.ORIG_CONTACTS_RCVD) AS Calls
SELECT contacttype.CT_NAME AS Expr1, Sum(forecast.ORIG_CONTACTS_RCVD)
AS Total
FROM contacttype INNER JOIN forecast ON contacttype.CT_ID =
forecast.CT_ID
WHERE (((contacttype.CT_NAME)="Pensions") AND
((Format([DATE_TIME],"Short Date"))=#10/9/2006#) AND
((Format([DATE_TIME],"Long Time")) In (#12/30/1899 8:0:0#,#12/30/1899
8:30:0#,#12/30/1899 9:0:0#,#12/30/1899 9:30:0#,#12/30/1899
10:0:0#,#12/30/1899 10:30:0#,#12/30/1899 11:0:0#,#12/30/1899
11:30:0#,#12/30/1899 12:0:0#,#12/30/1899 12:30:0#,#12/30/1899
13:0:0#,#12/30/1899 13:30:0#,#12/30/1899 14:0:0#,#12/30/1899
14:30:0#,#12/30/1899 15:0:0#,#12/30/1899 15:30:0#,#12/30/1899
16:0:0#,#12/30/1899 16:30:0#,#12/30/1899 17:0:0#,#12/30/1899
17:30:0#,#12/30/1899 18:0:0#,#12/30/1899 18:30:0#,#12/30/1899
19:0:0#,#12/30/1899 19:30:0#)))
GROUP BY contacttype.CT_NAME
PIVOT Format([DATE_TIME],"Long Time");

this is what it currently looks like to give you guys a picture.

Expr1 Total 1:00:00 PM 1:30:00 PM 8:00:00 AM 8:30:00AM
Pensions 50 2 3
4 5


this is what i want it to look like.

Expr1 Total 8:00:00 AM 8:30:00 AM 1:00:00 PM 1:30:00PM
Pensions 50 4 5
2 3

Any ideas? help would be awesome.

-Phil
Click to expand...
 
One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'8:00:00AM','8:30:00AM','9:00:00AM','9:30:00AM', and so on until '7:30:00PM'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want. You can also make data not show up by
taking out a column. For example, remove 9:30:00AM and its data won't show.
Also it will create an empty column even if there isn't any data for
'8:30:00AM'.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
Im running a crosstab query that is pullling data from a table that has
records on a 30min basis. i wanted to pull the data and format the
query to give me the result of how many contracts are received for the
day in the totals column, and then have time columns from 8:00 AM to
7:30PM and show how many contracts are received for each 30 min
intervals in their respective columns. The result of the query pulls in
the data that im looking for but not in the order that im looking for.
Instead of starting with 8:00:00AM column it starts with 1:00:00PM. It
looks like its running on the string and not on the AM/PM time format.
Any help? Im looking for a way to order it correctly starting from
8:00:00AM to 7:30:00PM.


Please see my query below

TRANSFORM Sum(forecast.ORIG_CONTACTS_RCVD) AS Calls
SELECT contacttype.CT_NAME AS Expr1, Sum(forecast.ORIG_CONTACTS_RCVD)
AS Total
FROM contacttype INNER JOIN forecast ON contacttype.CT_ID =
forecast.CT_ID
WHERE (((contacttype.CT_NAME)="Pensions") AND
((Format([DATE_TIME],"Short Date"))=#10/9/2006#) AND
((Format([DATE_TIME],"Long Time")) In (#12/30/1899 8:0:0#,#12/30/1899
8:30:0#,#12/30/1899 9:0:0#,#12/30/1899 9:30:0#,#12/30/1899
10:0:0#,#12/30/1899 10:30:0#,#12/30/1899 11:0:0#,#12/30/1899
11:30:0#,#12/30/1899 12:0:0#,#12/30/1899 12:30:0#,#12/30/1899
13:0:0#,#12/30/1899 13:30:0#,#12/30/1899 14:0:0#,#12/30/1899
14:30:0#,#12/30/1899 15:0:0#,#12/30/1899 15:30:0#,#12/30/1899
16:0:0#,#12/30/1899 16:30:0#,#12/30/1899 17:0:0#,#12/30/1899
17:30:0#,#12/30/1899 18:0:0#,#12/30/1899 18:30:0#,#12/30/1899
19:0:0#,#12/30/1899 19:30:0#)))
GROUP BY contacttype.CT_NAME
PIVOT Format([DATE_TIME],"Long Time");

this is what it currently looks like to give you guys a picture.

Expr1 Total 1:00:00 PM 1:30:00 PM 8:00:00 AM 8:30:00AM
Pensions 50 2 3
4 5


this is what i want it to look like.

Expr1 Total 8:00:00 AM 8:30:00 AM 1:00:00 PM 1:30:00PM
Pensions 50 4 5
2 3

Any ideas? help would be awesome.

-Phil
Click to expand...
 
Back
Top