Absence Calendar (1 Viewer)

moodhi · Dec 11, 2018

OK I have a table to record all employee absence (Events) e.g. Annual Leave, Sickness, Maternity Leave, .... etc. which are in a table with EventID and EventName.

The table which records the above has:
EmployeeNo, EventID, DateStart, DateEnd......etc.

Now I need to produce a daily calendar (for whole year) starting from Current month for each day showing as follows:

Current Month: December 2018
Emp Name 1 2 3 4 5 6 7 8 9 10 11 12 13
Name 01 AL AL
Name 02 Sick Sick
Name 03 AL AL

Current Month: January 2019
Emp Name 1 2 3 4 5 6 7 8 9 10 11 12 13
Name 01 AL ALL
Name 02 AL AL
Name 03 ML ML

Is there a simple Query or a combination of Queries to produce the above?
or if its VBA what is the best approach?

Ranman256 · Dec 11, 2018

I have a table of every day in the calendar, tCalendar.
make Q1 ,with tCalendar and tAbsence
outer join on date, to show All records in tCalendar, some in tAbsence

then make a crosstab query (use the wizard) using Q1 to show days as columns.
in Q1 make a date field as: yyyymmdd , so the crosstabl will sort them correctly.

moodhi · Dec 11, 2018

Yes but the dates in tAbsence are a range i.e. DateStart to DateEnd. So if the outer join is on DateStart and DateEnd then what about the dates in between?

isladogs · Dec 11, 2018

You might be interested in this employee attendance tracker app.
See https://www.access-programmers.co.uk/forums/showpost.php?p=1326285&postcount=1

There is a newer version as well for which MajP can supply the link

moodhi · Dec 11, 2018

I think I am stuck because my Absence table is designed as:
EmployeeNo, EventID, DateStart, DateEnd......etc.

Where even though I am looping through all records trying to spread them into basket of daily storage but it is becoming painful to deal with a situation where a date range falls between 2 months e.g. 29 Oct 2018 to 4th Nov 2018.

Would you say that having a table of the above design is wrong?

Is there any way I could tackle the date range above?

June7 · Dec 11, 2018

I didn't look at isladogs db but here is another. Absence records use date range, code manipulates this info to generate records in a 'temp' table that is used as the report RecordSource.

isladogs · Dec 11, 2018

Did you look at the example i mentioned? For info, its by oxicottin and not my work.

Here's another by Peter Hibbs with a Gantt chart display that may be very suitable for your needs. Both should at the very least help you deal with your issues

Your table design sounds fine. At least those four fields do.
However it depends what other fields you have as well.

EDIT: Oops - forgot the link to Peter Hibbs example.
Here's two of his apps - both excellent:
https://www.utteraccess.com/forum/index.php?showtopic=2001188&hl=Holiday+planner

https://www.utteraccess.com/forum/index.php?showtopic=1969978&hl=Holiday%20planner&st=0

MajP · Dec 11, 2018

Yes but the dates in tAbsence are a range i.e. DateStart to DateEnd.

If you want to use a range, relook at Ranman256 suggestion. That will give you a record for each date in the range. You just need a large table of dates to cover all possible days.

June7 · Dec 11, 2018

@isladogs, forgot the link to Peter Hibbs

Example in post 6 uses a table with date range fields.

moodhi · Dec 11, 2018

Example (Gantt Chart sample database) in post 6 is exactly what I am after and its how my tables are structured BUT the example does not seem to be working!!! Its not showing all the range in the chart. I am really confused and can't find what can be wrong with the Crosstab query. Any Idea?

isladogs · Dec 11, 2018

June
Thanks for the prompt. I've added 2 relevant links to post #7 now (both for Peter Hibbs)

June7 · Dec 11, 2018

Don't know, it works perfect for me.

moodhi · Dec 12, 2018

Yes, you are right it works perfect in fact really great. I am honestly very grateful and thankful to this great help and the App, it worked absolutely as I needed.

The difference was in the Report Form - the code with hashes around the date did not work for me. I changed them to Format function and it all worked.

You guys are fantastic....

moodhi · Dec 12, 2018

Thanks Guys

Absence Calendar (1 Viewer)

moodhi

Registered User.

Ranman256

Well-known member

moodhi

Registered User.

isladogs

MVP / VIP

moodhi

Registered User.

June7

AWF VIP

Attachments

isladogs

MVP / VIP

MajP

You've got your good things, and you've got mine.

June7

AWF VIP

moodhi

Registered User.

isladogs

MVP / VIP

June7

AWF VIP

moodhi

Registered User.

moodhi

Registered User.

Similar threads

Users who are viewing this thread