Crosstab query

[RDKDCRL]

Hello everyone

This website has been a very valuable resource for me over the years, helping me learn a great deal about access.

Now I have come across something that has me totally stuck....:banghead:

I need to subtotal days of absence, by absence Type with start dates and end dates..for example, the raw data looks like this:

Name Date AbsenceType
Smith 01 Jan 2016 Holiday
Smith 02 Jan 2016 Holiday
Smith 03 Jan 2016 PublicHoliday
Smith 04 Jan 2016 Holiday
Smith 05 Jan 2016 Sick
......Weekend, therefore no data entry............
......Weekend, therefore no data entry............
Smith 08 Jan 2016 Sick
Smith 09 Jan 2016 Sick
Smith 10 Jan 2016 Sick
......Was at work, therefore no data entry.......
Smith 12 Jan 2016 Sick

and what I need as a result is:


Name StartDate EndDate AbsenceType DaysHoliday DaysPublicHoliday DaysSick
Smith 01 Jan 2016 02 Jan 2016 Holiday 2
Smith 03 Jan 2016 03 Jan 2016 PublicHoliday 1
Smith 04 Jan 2016 04 Jan 2016 Holiday 1
Smith 05 Jan 2016 05 Jan 2016 Sick 1
Smith 08 Jan 2016 10 Jan 2016 Sick 3
Smith 12 Jan 2016 12 Jan 2016 Sick 1

I've got First and Last and IIf's running around my head

Any help with this is appreciated

 

[RDKDCRL]

Anyone - even pointers may help

 

[arnelgp]

you need a function to perform that. and saved in temp table in current db or external db.

 

[boerbende]

I got also questions about creating absence reports, with a lot of wishes and specific rules.
My question back was: why so difficult?

I got something similar which took me multiple days to get a reliable report with code and queries. An operator printed a period report when clicking on a button. Then a operator asked me: why do I have to push on the button. Isn't it possible to get it automatically with each measurement, this is much simpler for me... And actually, this was much simpler for me as well.

What you can do is accumulate your report when entering the data.
Smith 01 Jan 2016 Holiday gives Smith 01 Jan 2016 01 Jan 2016 Holiday 1
second entry:
Smith 02 Jan 2016 Holiday gives Smith 01 Jan 2016 02 Jan 2016 Holiday 2
Third entry:
Smith 03 Jan 2016 PublicHoliday . This is not equal to last record "Holiday" for smith so you start a new record...
etc...
Off course I assume that you enter data chronologically, which we did in my example...

At the end you just print out the records you have created...

 

[RDKDCRL]

Thanks for the help. I eventually managed to get round this using a recordset and vba to push this into a temp table. Was a little labour intensive with alot of head scratching, but in the end it worked