Non-Agregate Crosstab query.

[grnzbra]

I have a database which records the user ID and login time into a two column table:

User
DateTime

which drives a query

User
DateTime
DayOfWeek

I would like to create a crosstab query which shows day of week across top and user down the side and DateTime in the Values section. The problem is that if someone logs in more than once in one day, I can only get the first or last login. I would like to get each login.

Is there any way to do this

 

[Matty]

In the intersection between the user and day of week, would you like the value to be something like "8:00am, 1:00pm"?

If that's the case, I'd make a query before your crosstab to make a string expression for each user that concatenates the login times. Your field list would then be:

User
DateTime
DayOfWeek
LoginTimes (or whatever you name that expression)

You'd then use the LoginTimes expression in your values section of the crosstab.

 

[grnzbra]

Thanks. Cool idea. However, that would string the login times horizontally. I need them stacked veritcally with the first logins for each user to be strung horizontally

What I need is

User..........Mon..........Tue..........Wed..........Thur..........Fri
a.......10:52:03....09:33:21...10:30:47....10:01:53...10:12:19
........................14:22:31..............................................
b 09:10:25...09:19:11....................09:45:19..............

What I get is

User..........Mon..........Tue..........Wed..........Thur..........Fri
a.......10:52:03..............................................................
........................09:33:21..............................................
.......................................10:30:47....10:01:53...10:12:19
.......................................14:22:31...............................
b 09:10:25.............................................................
........................09:19:11.............................................
.......................................................09:45:19..............

 

[Matty]

What I'm thinking you could do would be to somehow number each login time and save it as an expression. So for user A, 10:52:03, 09:33:21, 10:30:47, 10:01:53, and 10:12:19 would all be "Login1" and 14:22.31 would be "Login2." You then would have this "LoginNumber" expression as a row heading, and that'd group your data properly. So your data would look like:

User....LoginNumber..........Mon..........Tue..........Wed....... ...Thur..........Fri
a........Login1..............10:52:03....09:33:21...10:30:47....10:01:5 3...10:12:19
.........Login2...............14:22:31.................. ............................

I'm not entirely sure how you'd figure out the LoginNumber, but maybe this will give you a push in the right direction.

 

[grnzbra]

Thanks Matty. That definitely got the wheels turning.