Crosstabs with between..

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,

On 10.03.2010 13:09, MorganJ wrote:
trying to make a crosstab with data who tells me how many cars goes where
to, (which city), in which period (Time of day)

Can you help me?
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"MorganJ" wrote:

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?

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" wrote:

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" wrote:

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?

pppppjjhhh

"MorganJ" a écrit dans le message de
groupe de discussion : ...
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?