Crosstabs with between..

  • Thread starter Thread starter MorganJ
  • Start date Start date
M

MorganJ

Hi
trying to make a crosstab with data who tells me how many cars goes where
to, (which city), in which period (Time of day)

Something like this:

TO 00:00-07:59 08:00-15:59 16:00-23:59
Oslo 2 3 6
London 3 6 12
Paris 1 0 0

Can you help me?
 
hi Morgan,

trying to make a crosstab with data who tells me how many cars goes where
to, (which city), in which period (Time of day)
Click to expand...
Can you help me?
Click to expand...
Sure, but a concise and complete example is necessary. I couldn't guess
your neither your table structure nor your sample data.



mfG
--> stefan <--
 
First create a query something like below so that the data will show with the
appropriate headers.

SELECT tblDateTimes.*, IIf(DatePart("h",[DateAndTime])
Between 0 And 7,"00:00-07:59",
IIf(DatePart("h",[DateAndTime]) Between 8 And 15,
"08:00-15:59","16:00-23:59")) AS [TheTimes]
FROM tblDateTimes;

Then use this first query as the record source for the crosstab.

Warning: The query above will display 16:00-23:59 for records in the date
time field. If you have any, you may want to trap the null first.
 
Meaant to type "for NULL records in the date"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jerry Whittle said:
First create a query something like below so that the data will show with the
appropriate headers.

SELECT tblDateTimes.*, IIf(DatePart("h",[DateAndTime])
Between 0 And 7,"00:00-07:59",
IIf(DatePart("h",[DateAndTime]) Between 8 And 15,
"08:00-15:59","16:00-23:59")) AS [TheTimes]
FROM tblDateTimes;

Then use this first query as the record source for the crosstab.

Warning: The query above will display 16:00-23:59 for records in the date
time field. If you have any, you may want to trap the null first.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


MorganJ said:
Hi
trying to make a crosstab with data who tells me how many cars goes where
to, (which city), in which period (Time of day)

Something like this:

TO 00:00-07:59 08:00-15:59 16:00-23:59
Oslo 2 3 6
London 3 6 12
Paris 1 0 0

Can you help me?
Click to expand...
Click to expand...
 
Back
Top