Hi, I'm probably overlooking the obvious here so please point it out!
I have a table of data with the following fields; Name, Date, DueTime, ActualTime
I have a query to summarise the data that I have been using, brings in the fields from the table and adds; IsLate, HowLate, IsEarly, HowEarly - these are used to workout if the client was late or early, and by how much, so from this query a typical record would look like:
Code:
NAME DATE DUETIME ACTUALTIME ISLATE HOWLATE ISEARLY HOWEARLY
BOB 01/01/2015 08:00 08:15 1 00:15 0
All working fine and as I would expect.
Now I want to create a report that will give a sort of cross-reference, but I'm stuck on how to get the data out. I have looked at using a crosstab query but it's not giving me the results I need. The report I want looks a bit like a pivot table, but again I have tried designing one that does what I need, but with no luck.
I have reacted another query that takes the first query as it's source, and adds grouping on the Name and the Month from Date and does SUM on the IsLate and IsEarly and AVG on the HowLate and HowEarly
When the query is run, I supply the name so it only ever comes back with a single record matching name. The table I want to create would look like:
Code:
NAME
| JAN 15 | FEB 15 | MAR 15 | APR 15 | MAY 15 | JUN 15 | JLY 15 ....
--------------|--------|--------|--------|--------|--------|--------|---------
No Of Lates | 5 | 0 | 6 | 3 | 0 | 2 |
--------------|--------|--------|--------|--------|--------|--------|---------
Average Late | 00:25 | 00:00 | 00:15 | 00:19 | 00:00 | 00:35 |
--------------|--------|--------|--------|--------|--------|--------|---------
So I already have all the data in the second query, filtered by name and grouped by month, but how can I get that into a report that looks like that??!
Many thanks in advance