Crosstabs with between..

[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?

 

[Stefan Hoffmann]

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)

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

 

[Jerry Whittle]

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]

Meaant to type "for NULL records in the date"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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.

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?

 

[joelgeraldine]

pppppjjhhh