Solved Create a calendar to record work inputs and outputs

[zelarra821]

Good night.

I have searched this forum for examples of calendars, and I have found this attached majP that more or less adapts to what I am looking for.

What it lacks is that it can add the hours that I record each day when I enter and leave work, in such a way that it later allows me to operate with them to make the weekly and monthly calculation.

I attach the example that serves as a base.

Does anyone know how I can do it?

Thanks.

 

[June7]

How do you want to define a week? Should it be 7 full days even when crossing months/years? Calculate a field that returns date of first day of week or month for a date falls into and do aggregate calculation by grouping on that calculated value.

Sounds like you want a timekeeping app, not really a calendar. This is a fairly common topic. Can spend a lot of time and effort to build from scratch or buy one.

Even if you just want to enter a single record for total hours each day and not track start and stop times for breaks/lunch or budget coding, this app as long way from accomplishing what you want. I don't see any attempt to adapt to your requirements. For a start, add a number field for Hours.

 

[zelarra821]

I explain. I am a civil servant, and the Administration and I have a working day of 37.5 hours per week, but the calculation is made monthly by the Administration. Therefore, I have to, as you say, time the hours I dedicate to work, which an internal application tells me when I clock in and out. In addition, I want to know the weekly hours to know that I am fulfilling. I want to see all this on a calendar. If you've seen the Excel, that's it.

Therefore, I have thought that this calendar, with a few modifications, can achieve what I want. To do this, you should be able to enter the hours that you record each day, and then do the weekly and monthly calculations. Anyway, I am open to new suggestions.

Thank you very much for responding so quickly.

 

[June7]

I suggest you spend a solid week studying an introductory tutorial book on Access to learn basics of relational database concepts, Access functionality, and programming (macro or VBA). As it is, you are asking someone to build a product for you, something that would normally be done for mucho $.

If you want to show hours instead of descriptive text, delete records and change InputText to a number integer type field (changing field name will require edits to query/form/report/code). That would be a start. Then learn how to build queries to do aggregate calculations and how to design reports to present output.

Example of a simple query:

SELECT Sum(tblInput.InputText) AS SumHrs, Year([InputDate]) AS Yr, DatePart("ww",[InputDate]) AS Wk
FROM tblInput
GROUP BY Year([InputDate]), DatePart("ww",[InputDate]);


Can delete the ConversionErrors table.

I am a retired civil servant and learned Access when assigned task of building laboratory database. I am not a professional developer.

 

[MajP]

I think if I was going to build this I would not use that calendar, but the Peter Hibbs calendar in that thread

Date Pickers, Calendar Controls, and Calendar resources

There has been some recent interest in a replacement for the MS calendar control that existed prior to 2010. I have been collecting date pickers, calendar controls, and calendars for a long time and thought it would be a good idea to have one thread with lots of good examples. Some of these...

www.access-programmers.co.uk

The peter hibbs calendar has the ability to store events with start and end times and to include multiple events per day. So the structure already exists.

The problem with Appointments is that you have an appointment start and stop time. That may be fine. This would mean instead of logging "Start Work" and "Stop Work" you would enter a single event "Work" with a start and stop. That may or may not make sense. If it does you basically have a working solution. You could add the times to the display.

Another way would be to modify the input form or make another custom input form. If you made a custom input form you could Select from a pull down "Log In" and then provide a time. Since an appointment has both a start and end time simply make it a 1 minute appointment. Have the ability to select "Log Out" and create a log out appointment. Then calculate time between log in and log out for each day in a query.

 

[baderms]

As a civil servant, doesn't your agency have timekeeping software in place? You didn't mention what level of government you work at but at the federal level all agencies have time keeping software. I would start by checking with other agencies and find out what they use because depending on what level you work at there are specific things and safeguards that must be in place for auditing to try to insure there is no fraud.

 

[zelarra821]

The peter hibbs calendar has the ability to store events with start and end times and to include multiple events per day. So the structure already exists.

@MajP , where can I download this calendar? I can't find the post you shared and I can't find it on the Internet either. I don't care if I have to buy it, but I'm interested.

As a civil servant, doesn't your agency have timekeeping software in place? You didn't mention what level of government you work at but at the federal level all agencies have time keeping software. I would start by checking with other agencies and find out what they use because depending on what level you work at there are specific things and safeguards that must be in place for auditing to try to insure there is no fraud.

They do have an application to record time, but it does not allow you to plan your day to find out if you are meeting the goal of weekly and monthly hours, or, for example, plan a specific month with a view to knowing when to take vacations. People have made one in Excel, which I have attached, but it doesn't save the values, so I'm trying to improve it.

 

[MajP]

Date Pickers, Calendar Controls, and Calendar resources

There has been some recent interest in a replacement for the MS calendar control that existed prior to 2010. I have been collecting date pickers, calendar controls, and calendars for a long time and thought it would be a good idea to have one thread with lots of good examples. Some of these...

www.access-programmers.co.uk

It is in that thread with all the calendars

https://www.access-programmers.co.uk/forums/attachments/peterhibbs_with_majp_yearview-v4-accdb.87932/

 

[MajP]

The Peter Hibbs calendar is very good. The big issue is that it only handles one person or one calendar. I started to add to it so you could have multiple "Calendars". So multiple people could log in and their appointments would have a UserID. Then the views would load their appointments. I started this but never finished. It is a matter of adding a foreign key to the appointment table and adding filters to the views.

 

[zelarra821]

Now, I have seen how it works and at first glance it seems easy to adapt it, because what I will do is simplify the appointments. What I don't know is if I'll have problems with the code, that there's something I don't understand.

Beyond that, I don't think it's necessary, because this will be used by a single person to manage their time.

 

[MajP]

What I don't know is if I'll have problems with the code, that there's something I don't understand.

I dug through the code extensively so I can help. I modified the code so I could Categorize appointments by type and added my Year View to it. However, this should be very easy to do. You should only need to build a custom appointment form. You should not have to add any fields because I already added a Category field and a way to categorize appointments. So I am guessing you just need two categories of appointments: Check-In, and Check-Out if you plan to enter two events. Or a single "Work" event with a start and end time. Either way the only form that gets modified is the Add/Edit Appointment form.

If you go with two events then you will need some checks to ensure you only have one Check-In appointment per day and it occurs before a Check-out Appointment. If you go with the two event idea then you might need to concatenate the display with the time to get something like.
8 Mar
Check-In 07:00
Check-Out 16:45

If you go with a single event for Work, then in the Day view it will show the time.
The single event ID is by far easier. There is really no code modification. It is simply a calendar with one event per day and you could simply default the Appointment title to "Work Hours", but the utility might not be good. This does not force you to actually log-in and log-out. At the end of the day you would have to enter you event and remember when you started.

 

[zelarra821]

Ok. I take note. I cannot put a check so that there is only one entrance, because we have to make a half-hour stop and we have to clock in when we go out for breakfast and come back.

That is to say:

7:30 Entrance
11:30 Departure for breakfast
12:00 Entrance after breakfast
14:00 Departure

And one day a week:

15:00 Entrance
18:00 Departure

 

[MajP]

7:30 Entrance
11:30 Departure for breakfast
12:00 Entrance after breakfast
14:00 Departure

If you make a custom for for entry then you may want to do the following
on a given day
the first appointment should default to Category "Entrance" and not allow a category "Departure" without the previous record being an "entrance"

If the tricky part will be to give these events a Start and Stop time, but you only really will ever use the Start Time for calculation
7:30 Entrance is really 7:30 to 7:31
and
11:30 Departure is really 11:30 to 11:31

You then are finding the elapsed time of 11:30 - 7:30

 

[zelarra821]

Thank you very much for your suggestions.

I find it interesting to prevent an output without an input in the first place.

What I see unnecessary is to give a start time and an end time. It seems to complicate the life of the user, in addition to saving information that I am not going to use at all, increasing the size of the database.

 

[June7]

Did you try the simple changes suggested in post 4?

Might find this of interest https://www.accessforums.net/showthread.php?t=18459

 

[MajP]

I find it interesting to prevent an output without an input in the first place.

What I see unnecessary is to give a start time and an end time. It seems to complicate the life of the user, in addition to saving information that I am not going to use at all, increasing the size of the database.

Remember that the original database was designed to store "appointments" that are not related to any other appointment. An appointment has a duration and the database fields are designed to do that. A check in and check out does not really have a duration but you are trying to fit this into the existing structure. The user does not have to see an event Stop Time, you can default that in code, but if you do not put something in that field you may get some problems even if you do not use it. The database is going to look for a time when trying to display the event.

If you are going to attempt to calculate times then you will need some way for every day to sum the times between each Exit and Entrance. That means you have to ensure the data is always in synch. (entrance -- exit -- entrance --- exit --....). If you miss one of these then the whole calculation is out. (i.e. entrance -- entrance -- exit)

 

[zelarra821]

Did you try the simple changes suggested in post 4?

Might find this of interest https://www.accessforums.net/showthread.php?t=18459

I saw your comment, but then I discovered the database that @MajP proposes, and I think it is more suited to what I'm looking for.

 

[zelarra821]

Remember that the original database was designed to store "appointments" that are not related to any other appointment. An appointment has a duration and the database fields are designed to do that. A check in and check out does not really have a duration but you are trying to fit this into the existing structure. The user does not have to see an event Stop Time, you can default that in code, but if you do not put something in that field you may get some problems even if you do not use it. The database is going to look for a time when trying to display the event.

If you are going to attempt to calculate times then you will need some way for every day to sum the times between each Exit and Entrance. That means you have to ensure the data is always in synch. (entrance -- exit -- entrance --- exit --....). If you miss one of these then the whole calculation is out. (i.e. entrance -- entrance -- exit)

Yes, but my idea is to simplify it as much as possible. I mean, I would remove the weekly and daily view, which is useless in my case, so I would no longer need to store two dates for each record.

 

[MajP]

If you get rid of daily view then you just have to concatenate the Appt Title and Start Time.
Look even if you have no need for the stop time, DO NOT REMOVE that field from the table. This code is pretty robust and could have serious down stream effects. I think it would be a lot safer to make the stop time the same as the start time plus a small duration like 1 minuet. You can do that in code and the user will never know, but without digging through the code I have no idea what an appointment without a stop time will do. It may work, but would be simpler and safer to just default it to the start time + 1 minute.

In the Add/Edit form you can use as is with some modification
1. In the Category table you can have two types of Events "Log-in" and "Log-Out" (entrance, exit). This will now need to be a required field, because that information will be used to do the calculations between log-in and log-out events. It is not required now.
2 Log-in and log-out does not have a Stop date different from start date. So you can hide that control but default to start date (this already happens). You may be able to delete the control but would have to verify Stop Date is not called in code
3. Stop time can be hidden and can be defaulted to start time plus some increment
4. When saving a new appointment add additional code
Verify Category is either Log-In or Log-Out
If Log-Out verify previous record for that day is a category Log-In (If you log-in and log-out in different days then verify the previous record was a logout)

 

[zelarra821]

Ok. I take note. I'll tell you about the results.