Table like a crosstab query but not

[CodLiverOil]

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:

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:

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

[CJ_London]

I suspect you will need to combine records with a union query - create two crosstabs, one for the 'No of' row and one for the 'average' row.

If you have multiple rows, to ensure they appear in the right order you may also need to create an 'order' column

[CodLiverOil]

Thanks for the reply. Won't a UNION query just give me the same results as my second query? i.e. a single record entry with the aggregated results to the data grouped by month.

[CJ_London]

not if the first query is based on a count or sum and the second is based on an average.

I thought you were asking how to put the data together to display in a form or report i.e.

The table I want to create would look like:

[CodLiverOil]

Yes, I am looking to put it into a table on a report. Maybe I have misunderstood how a UNION query would work.

Based on your previous reply, I created a crosstab query for each row of data (No Of Lates, No Of Earlies, Average Late Time, Average Early Time etc...) and manually created a report with all those queries as the data source and put each field manually into the report. It works but not as I would like (for example, it shows each month even if there is no data in that month yet)

[CJ_London]

suggest post a couple of the crosstab queries you are using, or a db with some test data in it

but assuming your crosstabs have the same number of columns you would have say

SELECT * FROM Xtab1
UNION SELECT * FROM Xtab2
UNION SELECT * FROM Xtab3
UNION SELECT * FROM Xtab4