Order Query By TIME HELP?

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
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 

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

Between on Date field? 1
Need some query help 14
Trouble with "BETWEEN #time# AND #time#" 4
Need Help with Totals Query 24
SQL Time Query 1
DateAdd Function in Queries 2
Empty recordset query by time 1
Punch time query to calculate total work time 5

Top