travel and absence calendar

[laura]

I wonder if anyone can offer me a few suggestions on how I might go about
designing a travel and absence list/calendar. Perhaps someone has already
written a program to do this and can let me have some code to work on, or if
anyone knows of software out there that might do what I need.

Basically I want to write a program that keeps track of about 200 employees,
where they are when travelling or what days they take as holidays.

I want it to look a bit like a spreadsheet.

I want to be able to show just Monday through to Friday, not weekends.

I want the top of the spreadsheet column titles to be the date, i.e., as an
example, for this week in November:-

"Employee Name" "Monday 15" "Tuesday 16" "Wednesday 17" .etc.
J. Smith holiday
M. Jones Paris Munich
P. Brown holiday holiday

The way I want to be able to display the information, which will be set out
as above, is on a monthly basis where they can pick any month of any year
and display the grid.

Data entry will probably be via a form where they can pick the Employee from
a list and select their name, then enter the trip or holiday date, and trip
destination.

So far I have come up with two small tables,
tblEmployee with EmplID, Fname, Sname
tblEvent with EventID, EmplID, Event, Date. (Event of course will either
be the destination or 'holiday').

It should be fairly simple, but from those two small tables, I cannot figure
out a way of creating a "Calendar" which is going to be the top row of my
Spreadsheet/grid (as illustrated above), which is going to be infinite - in
other words, this year, next year, the year after.. etc.. whatever year they
wish into the future. Sort of like a timeline - although I think I would
like to limit them to viewing one month at a time rather than being able to
scroll infinitely left and right.

I hope this is not too confusing and that someone might have a bright idea
to push me forward - I have being laying awake at night wondering how to do
it. I do not know if it should be run from a query or whether I need to
create a table with every day of every year preset - or whether it should
all be somehow "virtual".

Thanks
Laura TD

 

[Alex Dybenko]

i think you solve this making a crosstab query for one week (depending on
selected week), with fixed column names like day1, day2
and show this on access form.

But probably better idea can be to use some aciveX control, for example
Schedule from here:
http://dbi-tech.com/show_products.asp?id_prod=9

--
Best regards,
___________
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

 

[laura]

Hi Alex,

Many thanks for your suggestion - I've spent the day looking at "crosstab
queries" and from what I understand, they "count" or "sum" data. I don't
want to do either, I just want to display data in a grid. Headings would be
the days of the week for any given Month, i.e., for November =

Monday 01 Tuesday 02 Wednesday 03 etc...

The rows would be Employee Name ("grouped by", I guess) followed by the
event, i.e., the country they are visiting or if it's a holiday then the
word "holiday". No calculations necessary - just data display.

Thanks for your help
Laura TD

 

[Alex Dybenko]

Hi Laura,
yes, crosstab queries also "count" or "sum", but what is most interesting
for you - is that you can transpose data using it, so if you have a table
like:

John 11/1/04 holiday
Mary 11/2/04 bahamas
Laura 11/3/04 whatever

you can transform it to a view like:

Mon Tue Wed
John holiday
Mary bahamas
Laura whatever

which you can easy show on access form

--
Best regards,
___________
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

 

[laura]

Alex,

Thanks for staying with me on this - it's the first time I have looked at or
understood Crosstab Queries and I can see how I might be able to adapt to
what I need. I could not figure it out on my own as it seemed to keep
insisting I did a count or sum - so eventually I ran the Crosstab Query
Wizard and whoopee.. I got the list I wanted (sort of). It show, like your
example below - exactly. Now I have to figure out a way of showing such a
"list" but also showing days of the week where there are actually no events
for continuity, not just the days of the week where there ARE events, if you
understand what I mean. I have to have a full month of days of the week as
my headings going across the top, even if there are no events (holidays or
business trips) taking place on any particular day.

Someone on another forum offered to help me with this, but was going to
charge me money, which I thought was rather defeating the purpose of this
kind of forum. I felt rather let down as we are all in the same boat,
learning from one another for no financial gain - at least that is how I
understood it.

Anyway, many thanks - I hope you see this.

Laura TD

 

[Alex Dybenko]

Hi Laura,

i think you can do it as following:

1. make a table with 7 records:

Monday
Tuesday
....
Sunday

2. then make a query which returns records for seleceted week

3. make new query which left joins table from #1 with query from #2, you can
join by date number for example

so you will get 7 records:

day peson absence
1 John holiday
2
3 Mary bahamas
4
5 Laura whatever
6
7

4. Now you can transpose it - so you will always have 7 columns for days

Hope this clear, else feel free to ask

--
Best regards,
___________
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

 

[laura]

Dear Alex,

Once again, many thanks for your help. From what I can see I think the
crosstab is going to do the job for me, I just need to learn a bit more
about it and how to make it work as there are still some things I cannot
quite get my head around, but need to do my own homework before I come back
with more questions - and am busy doing that just now. I would love to let
you know how I get on but realise that this thread is getting 'old' and a
bit long too.

Thank you again.
Laura TD