Attendance by days name of week and correspond date/Month (1 Viewer)

[smtazulislam]

Hi, I have an Excel sheet for recording attendance, and I would like to replicate format in MS Access.

However, I'm not sure how to proceed with creating it. Essentially, I want to be able to select the year and month, and then have the days of the week and corresponding dates automatically populate, accounting for leap years.

Additionally, I need to be able to mark holidays where no absences should be recorded. Herein attached my excel data for your refers.

Any guidance on how to achieve this would be greatly appreciated.

 

[June7]

What you have is a 'flat file' spreadsheet. A normalized relational database would not store data in this structure. Look at the MS TimeCard template at https://support.microsoft.com/en-us...emplates-e14f25e4-78b6-41de-8278-1afcfc91a9cb

Excel may be best tool to produce a paper timesheet to mark up for attendance then use Access to store data. How do you gather attendance info? Do employees complete individual timesheets?

 

[smtazulislam]

Hi June,
Thank you very much for your response.
I'm actually quite inspired by how both an appointment One and Two operated, especially, in terms of their appointment scheduling functionality. I havve attempted to apply and in-corporate some ideas from both databases.

In this database, I have included EmployeeID, YearID, MonthID, and WorkplaceID, with columns number 01 through 37 columns for counting the days. These columns are ideal for displaying data for the 12 months. I applied/tried from 2021 to 2024.

However, I have encountered difficulty in saving the fixed date. Please see the refer to the attached screenshot.
Therefore, I'm seeking another idea or approach to store and manage dates, beginning from the start date (1) to the end date.

 

[June7]

As I said, this is not optimal schema for a relational database. Did you look at the referenced template?

What difficulty? What have you tried?

You could attach database for analysis.

Why would there be 37 fields for days?

 

[GPGeorge]

Hi June,
Thank you very much for your response.
I'm actually quite inspired by how both an appointment One and Two operated, especially, in terms of their appointment scheduling functionality. I havve attempted to apply and in-corporate some ideas from both databases.

In this database, I have included EmployeeID, YearID, MonthID, and WorkplaceID, with columns number 01 through 37 columns for counting the days. These columns are ideal for displaying data for the 12 months. I applied/tried from 2021 to 2024.

However, I have encountered difficulty in saving the fixed date. Please see the refer to the attached screenshot.
Therefore, I'm seeking another idea or approach to store and manage dates, beginning from the start date (1) to the end date.

You cannot import a spreadsheet into Access and use it that way without monstrous problems, problems you are already encountering just trying to get started. They'll get worse.

Invest time in learning what Normalization is and how relational database applications must be designed to be useful, effective and reliable.

Here are some relatively easy to follow videos that should help you get started.

If you do want to retain the spreadsheet, keep in it Excel and use it there.

 

[smtazulislam]

You cannot import a spreadsheet into Access and use it that way without monstrous problems, problems you are already encountering just trying to get started. They'll get worse.

Invest time in learning what Normalization is and how relational database applications must be designed to be useful, effective and reliable.

Here are some relatively easy to follow videos that should help you get started.

If you do want to retain the spreadsheet, keep in it Excel and use it there.

any ways i can uploading spreadsheet in ms access permenantly by using a button.
In a Worksheet I have 12 months recored.
Give me some Idea.

Second Idea,
I can allocate 31 columns for days. when I will selecting a year and month, I need a method to adjust the column count accordingly.
For instance, if I choose February 2024, it should expand to 29 out of 31 days.
Similarly, if I pick March 2024, it should extend to 30 out of 31 days.

Its not linked or any relations with another table, no need to use index, its based on Year/Month recored.

How can I do that ?

 

[smtazulislam]

As I said, this is not optimal schema for a relational database. Did you look at the referenced template?

I test it, It is not matched with my requirement idea.

What difficulty? What have you tried?

I need a way to efficiently record attendance for over 1500 employees and 35+ suppliers in our system, I means, our employee work within 35 suppliers. I have using 25 to the end of the month completed the recored by menually.

You could attach database for analysis.

Why would there be 37 fields for days?

If I have 37 columns then it was display full year.
I prefer to start recording from the beginning of each month as such which is represented by day 01 column. But its was starting some of month and others within 2, 3, 4, 5, 6 Column to started, It is work perfectly with this issue.
see my previously post picture with table data recored where I hightlighted to mentioned.

 

[GPGeorge]

Spoiler:

any ways i can uploading spreadsheet in ms access permenantly by using a button.
In a Worksheet I have 12 months recored.
Give me some Idea.

Second Idea,
I can allocate 31 columns for days. when I will selecting a year and month, I need a method to adjust the column count accordingly.
For instance, if I choose February 2024, it should expand to 29 out of 31 days.
Similarly, if I pick March 2024, it should extend to 30 out of 31 days.

Its not linked or any relations with another table, no need to use index, its based on Year/Month recored.

How can I do that ?

Spoiler:

I test it, It is not matched with my requirement idea.

I need a way to efficiently record attendance for over 1500 employees and 35+ suppliers in our system, I means, our employee work within 35 suppliers. I have using 25 to the end of the month completed the recored by menually.

If I have 37 columns then it was display full year.
I prefer to start recording from the beginning of each month as such which is represented by day 01 column. But its was starting some of month and others within 2, 3, 4, 5, 6 Column to started, It is work perfectly with this issue.
see my previously post picture with table data recored where I hightlighted to mentioned.

You write that you need a way to efficiently record attendance for over 1500 employees and 35+ suppliers?
Create a proper, normalized relational table design.

You do not want to do that. You want to use a spreadsheet instead.

So, it seems like you need to decide which is more important to you.

A. If you want an efficient application, you can't retain the spreadsheet design.

B. If you want to retain the spreadsheet design, you can't design an efficient application in Access.

Choose A or B and move forward.

Good luck with the project.

 

[MajP]

This will show you how to properly normalize attendance data and then build a form that allows you to show all attendance and easily click on a day to add attendance.
Now I did not make the view you show with employees visible and then a horizontal calendar.
My view is the opposite showing an employee and then the whole year. But it would be trivial to make your view, all the necessary code to do it is already there.

So what you see is each month is an instance of the same subform. You pick a year it loads the correct day labels for each subform. Then it fills the textboxes for each.

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

To make your view I would create a "grid" table. In the table you need these fields
tblGrid
--employeeID
--Day1
--Day2
--Day48

Why 48 not 31? Because these represent a Grid location not the day of the week or month.
Assume you took this calendar and made all the days horizontal. You would need to have potentially 47 locations

Every visible day on this grid is a cell. If your headers will be Sun,Mon...Sat Sun,Mon... Sat you will see that you have to repeat is 6 times.
At the top of your form you simply have 6 repeting groups of labels Sun, Mon, Tues... Sat
Below that you have Six empty labels lbl1 to lbl48
Then you simply fill the day labels

So for this month it would look like
Sun Mon Tue Wed Thur Fri Sat Sun Mon... ............ Sat
25 26 1 2 6

lbl1 lbl 48

Below these labels in the detail section is my controls from the table
day1 ....... Day48

You read the normalized data and then push the values into the temp table. Now you have your view. You click on any cell and assign the attendance. If you download the file all the code to do this properly is there.

https://www.access-programmers.co.uk/forums/attachments/yearview_attendance_with_oxicotin-v10-accdb.87109/

 

[smtazulislam]

Hi @MajP,
Thank you for your response and the detailed explanation. I must admit, your dynamic design approach has truly inspired me. That's why I decided to replicate it using MS Access, especially after relying on spreadsheets for almost eight years. This transition will undoubtedly streamline my workflow and make my tasks much easier to manage.

Creating a horizontal yearly view for the calendar is beneficial for analyzing of attendance records. Your calender, there is not feature that It allows for quick view of multiple employees in a one-to-many format as I requirement, And I have 35 suppliers with separeted make attendance sheet.

I tried to create your subform instance by month instead yearly, and the issue I facing with the record saved "STARTDAYS" displaying as numbers like 3, 4, 5, 6 instead of the actual dates. This could be due to how the data is being stored in the database. See the POST #3 .

Alhadullilah, I have successfully implemented this feature based on the Year and month. For months with 31 days, I have allocated 31 columns for days. The data is dynamically appended for each new month based on the calendar days, accounting with for leap years.

I'm encountering an issue where, for example, when I command February 2024, it expands to 29 days out of the 31-day columns, leaving the remaining 3 or 2 columns not visible. See the refered picture.

Me.sfrmAttendanceAppendData.Form.controls("n" & Format(i, "00")).Visible = False

For that, I have set the properties "can Shrink = Yes" and "can Grow = Yes", but the columns are not shrinking as I expected.

I believe I may need to use some VBA code to address this issue, but I have no sense of the specific code required.
Can anyone assist me with this problem?

 

[MajP]

Can you post what you have?

 

[June7]

Shrink and Grow only applies to height, not width, on a report. Controls do not shrink and grow on form, that's why form has scroll bars.

 

[Poppa Smurf]

First I would keep using your excel spreadsheet.
At the same time define your requirements for the database then build your your database requirements. This will save you a lot of time.
Attendance are you recording it daily or monthl?

 

[smtazulislam]

Can you post what you have?

Okay, I will try to seperate the attendance Table and Form.

 

[smtazulislam]

Shrink and Grow only applies to height, not width, on a report. Controls do not shrink and grow on form, that's why form has scroll bars.

Ah, I understand now. Thank you very much for providing this information; I truly appreciate it. I have gained some valuable insights.
So, to address this issue, I utilize the code.

Me.Width = Me.Width - 100

This code is success in another database, but I encountered difficulties within my current project. any other options available.

 

[MajP]

See complete properly designed solution.

Attendance tracker

In response to https://www.access-programmers.co.uk/forums/threads/attendance-by-days-name-of-week-and-correspond-date-month.330514/ Here is a properly designed attendance tracker with a few bells and whistles. It allows you to color code one or more attendance codes for a given dates. This...

www.access-programmers.co.uk