Pivotable Timeslots

[Irwell]

Hi guys... I have a table of records for multiple days which include identifiers for each person, a few other identifying characteristics, the date the record refers to and start and end times on that day. I am trying to get to a set of data which can be pivoted in Excel into the format:

            08:00   09:00   10:00   11:00   etc.
Date 1      1       2       5       11
Date 2      2       3       6       15
Date 3      1       3       6       15
Date 4      2       2       4       13
etc.

This is so that I can filter down on categories and get to a list of how many people in that category fall within that timeslot based on their start and end times.

I have managed to do this, but it seems very cumbersome. I have a query for each timeslot which filters the main table based on the start and end times. I then have a further query which joins all these queries together into one long list so I have a record for every person for every timeslot in every day. Due to the processing load this creates I make this query export the data into a table, which as you can imagine makes for a very large database.

Can anyone think of a more efficient way of making the data pivotable into timeslots in the format above without having to produce a table with records for every single person/date/timeslot combination? I feel like there is something quite simple I'm overlooking.

Please note that whilst I am using Windows 8 and Office 2013, I have limitations that require compatibility with XP and Office 2003.

[June7]

So you need 24 time slot fields, 1 for each hour of day? Post a sample of source data for analysis and testing.

[Irwell]

The number of timeslots varies from application to application. I am looking at less than a day, but sometimes have to break it down into other time periods, for example half hour timeslots. I simply adjust my queries manually to allow me to do that. I attach an example of how I am currently doing it. I've removed unnecessary timeslots and fields as I think they simply serve to complicate things. The data is obviously anonymous.

As you can imagine, with say 10 additional fields per record, a year's worth of dates and 30 timeslots for say 5,000 people I would end up with a very big database. I'd be starting with 1,825,000 rows and would end up with 54,750,000. I'm sure I should be able to get the data to pivot into the format I want without having to increase the number of records that much.

Edit: The format of the data table cannot be changed. It's exported daily by an off-the-shelf application. I've also not got any of my indexes in this file as I just built it as a quick example.

[June7]

Think I understand. The time slots are overlapping so each person can be captured in multiple time slots. Mr. A is in all but the 13th slot. So the original set of 8 records becomes 38.

Why the Extract query? The Merge query can be source for a CROSSTAB.

Only alternatives I can envision are:

1. a query that emulates the CROSSTAB by creating fields with expressions, like:
SELECT Day, Sum(IIf([Start]<#8:30:00 AM# AND End>=#7:30:00 AM#, 1, Null)) As [8 AM], ...{repeat expression for each time slot} FROM data GROUP BY Day;

2. VBA procedure that writes records to a temp table in the pivoted structure

[Irwell]

The only reason I extract the data is so that the query only has to run once as it takes quite a while for it to flow through all the queries for a large number of records. I then pivot from the extracted data instead of the original query. I'm not sure I can emulate the pivot as I need to be able to filter down on the data within Excel and for the appropriate values to then appear, for example showing only the numbers for people in the UK or in Division A (or indeed people in the UK and Division A).

[June7]

You can include whatever fields you need to group by in the emulated crosstab.

SELECT Country, Division, Day, ...{Sum expressions}... FROM data GROUP BY Country, Division, Day;

[Irwell]

Unfortunately doing it the crosstab way means the data isn't properly pivotable from Excel. I did try going down that route originally. I think I might be stuck with the large number of records so I may have to come up with some queries to normalise my table of data in order to reduce the file sizes a bit.