General :: Spreadsheet For Attendance Hours / Employee
Nov 3, 2013
I have:
- tbl_company (containing company info)
- tbl_employee (containing employees info)
- tbl_cim (containing working contracts info, related with both tbl_company and tbl_empployee)
In the tbl_cim i have starting date of the contract, working time per day (in hors, according to the contract), ending date of the contract, etc.
I need:
- once a month i have to generate a "excel-like" sheet containing employees (rows) on a selected company, days of the month (1-31) i choose (columns) and hors worked on each day (according to the working_time from tbl_cim). The working hours i have to be able to modify.
- the days before start date of the contract have to be empty; the days after end date, also.
- the saturdays and sundays have to be marked ina different color (cell background).
I ask:
- what tables i have to create additionally?
- how can i create a form for this infos?
- is there a way to do this with less vba as possible?
In my country, Romania, this type of "spreadsheet" is called a "pontaj" and companies have to do it every month. I would like to step from Excel to Access with it.
I am facing one problem while calculating the number of hours an employee spent in the office. Each employee will have a swipe card and the swipe data is stored in an .mdb file. Some employees will have different shifts and may come after 6pm and will go next day 5 am , while calculating their spent hours getting the problem. The swipe data is stored in IOData table , I wrote a simple query below to get the data., it is calculating correctly for the employees who come in between 11AM to 8PM but who are coming after 6PM and going next day , facing problem. Time is storing in 24 hour format in table with Status as Entry,Exit . There may be any no.of exits and entries , so i took min and max of the time in the below query.
I am attaching the mdb file data in an excel sheet(Master_Data.xls) and also the data of the below query(Swipe_Data.xls) and highlighting the employee for which we got problem.
SELECT HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus, min(IO.IOTime), max(IO.IOTime), DateDiff("n", min(IO.IOTime), max(IO.IOTime)) AS Minutes, [Minutes] 60 & Format([Minutes] Mod 60, ":00") FROM HolderData AS HD, IOData AS IO WHERE HD.HolderNo = IO.HolderNo and HD.DepartmentNo IN ('0008', '0009') and IO.IODate between #01/20/2014# and #01/24/2014# GROUP BY HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus;
I am trying to make a database for my employees to count their attendance according to the days of the month and then get their salary. How to create a form that show the days of the month and then i can add the names of the attending employees.
We are trying to find a way to have a query figure out when the hours of an employee reach 40 hours (Overtime) and figure the hours correctly. The Data Entry table contains the basic fields:
[Workdate].....The date the work was done [WEDate].......The Week Ending Date. Always on Sunday, and calculated like this "[WorkDate]-Weekday([WorkDate]-1)+7" [Employee].....Employee Name [Qty].............# of hours worked that day [RegHours].....Blank, to be updated by query [OTHours]......Blank, to be updated by query
The problem we are running into is getting it to update the daily running totals (using the [WEDate] & [employee] as the "Group Bys"). We'd like use an update query to fill in the to blank fields with the correct calculations.
Example - Working 6 day week, at 9 Hours per day in the [Qty] field
I'm creating an attendance tracking system, the attendance system needs to : Allow an instructor to enroll students into one or more classes, and then add attendance by date to each class(es). The problem that I'm having is, attendance is tracked by the number of hours that a student attends class. For instance, if class1 is in session from 8a.m. - 12p.m. student1 may only stay 2.50 hours of that class and student2 may stay 3 hours. The teacher needs to be able to add attendance to class1 for July 12, 2013 and add hours for each student enrolled in this class session who attended on that day. I have the following so far, minus the attendance:
Students -This is just the basic demographic information that instructors will need to enter on students. StudentID (PK) StudentLastName StudentFirstName DateOfEnrollment LevelOfStudy DateOfBirth
Location - These are the locations in which our students can attend classes LocationID (PK) LocationName County City LocationPhoneNumber LocationFaxNumber
Classes - These are the classes that our students can attend. CRNNo (PK) ClassName LocationID InstructorID
Instructor - These are the instructors that teach our classes. InstructorID (PK) InstructorLastName InstructorFirstName InstructorEmailAddress
Enrollment - This table enrolls the student into a class StudentID(PK) CRNNo (PK) LocationID (PK)
I run an after-school youth program for high school students. My issue is an accurate way to track attendance of students. We have MS ACCESS 2010 on our computers at work.
On any given day we may see between 30 to 50 students, so this is a small operation - - and the reason I'm adverse to buying fancy "custom" software and hardware "systems". Because students come and go and vary over time, we may see 100 kids in a given week, but in the past 2 years of operation, we have had well over 250 students come through our program, with some returning at various times.
What I would like is to be able to
1) Make a simple ID card with student photos and a unique ID # that can be ...
2) Scanned by a simple barcode system.
2b) Actually two barcode swipes ... one swipe at the front door / main entrance, and a 2nd swipe by a hand held bar code in the various four classrooms to be sure the student is not only in the building, but also went to the correct class.
3) That the input of that information dumps into our ACCESS database so we can track student attendance, and ...
3b) provide reports out to school counselors / principals of the numerous highs schools that our students come from, to let them know which of their kids are actively attending our program.
My questions are ...
A. Can this be done with ACCESS?
B. Would it be better to do this with EXCEL?
C. If ACCESS can handle it, which types of bar code devices would be the best choice for us to purchase? (We would need a total of five barcode scanners. One at the front desk, and one in each of our four classrooms.)
The system would need to be expandable seeing that this could easily be doubled in terms of classrooms and students within the next year or so. We are currently just 2 years old.
its been a while since i was last on here but i have run into some difficulty with a query
i would like to append some data to a tmp tbl.
i want to get the first appointment of the day for each employee. i have attached the relevant tables and a query that shows the information i need but it also shows more information. i have tried the nim function on the time and was successfull when i only had a few columns but when i added the colum [Items].[tblItems] it showed all the appointments for the day.
what i want to show is the first appointment for each employee.
I have a main table which records employee,date,record employee is related to tble employee. i want to be able to delete an employee however keep the referenced records with the name, date, record, is this possible?
I'm able to pull users that are logged into our network but our company uses a name that has their employee number as well. So deponding on when they were hired it could be a 5 or 6 digit number. so an example would be JSmith123456 or it could be JSmith12345 so is there a way to drop the numbers at the end? Meaning if they are entering data into a form that pulls their name can I drop off those last digits in the name field?
I was wondering if it is possible to create custom security permissions in access. For example I have created an employee database, with security. I would like to have it when a manager logs on, it will only display his employee's information and no other departments. Is this possible?? In one of the tables is a field for the department the employee works on, can it based off of a table field?
Just a question, I am developing a database for the company I work for and being new to Access, I have successfully made a login menu when the database starts up. The employee selects there name and begins data entry. Is there any way to log the activity of each employee, which records they inputted, date and time and so on?
I have a form, "Update Employee Information" that allows me to navigate through records and change data like name, adress, etc. I would like to know how to create list (or autofill) so that it won't take long before finding an employee.For example: I have a Employee ID and would like to search on that so the form can fill in the rest of my information. I have thought of two methods that I like:
(1) Click on a list "Lookup Employee", select ID and then the form will fill in the rest
(2) Without clicking a link, typing in the ID in the form and the rest of the data gets filled in.
I have a table with the start and end of their shifts.
We have four possible locations and four weeks.
So I have four tables for each location.
I have a module that can work out hours and deductions based on time in and out. I built that into a seperate databse working on a one time in and one time out setup.
I want to incorporate the two so I can get the hours worked over four weeks at all locations and divide that by 4 to get the average weekly hours for a staff member.
The front end of the database has a form for each table. Once you create an Audit in table 1 you can create multiple Processes underneath this one audit. Underneath a process you can create multiple Risks...... hence a hierarchy.
I am trying to append data to table 4 - controls. I have copied the format exactly into excel. I have also prepopulated the Audit, Process and Risk data in the required tables.
The reason I want this functionality is to enable team members to populate control information (oftentimes up to 25) in excel and then upload them easily into the database.
The macro I have created will work when created a new table. but when I point it at the existing table 4 - Controls I get the Key value violation error.
Need to get these into Access from an excel spreadsheet (located on sharepoint). I'm using the spreadsheet fields to create the table fields in Access.
The spreadsheet I've attached contains a ton of calculated cells and linked data. The people who created and utilize it aren't necessarily interested in changing the way they use it or populate it each quarter. That being said, my task is to somehow create a way that it can be sent to/seen by each person differently. First I'll point out that the 3 columns that are blank normally contain names, I deleted them for obvious reasons. The first of those columns, B, contains unique names for each agent in the firm, these people need to ONLY see their own data. The second column of names are basically managers, they need to see the records for each agent that falls under them. The 3rd column of names is irrelevant for these purposes.
Now if all this data were in access, and everyone had access to it I could easily just make records visible or invisible based on user name and I wouldn't be here right now. But they can't and that's not an option. I honestly don't know if this should be done solely in Excel, in Access or a bit of both. Currently this spreadsheet gets emailed to each manager and they have to review the data with each agent.
The first tab, worksheet, is basically instructions. It would be nice if those were images on the page and then all of the data were below that so that the agents could see them together and understand it better. As I'm sure you'll see it's a pretty complicated system so they have a hard time understanding it.
Note: There are actually about 3,000 records, I've deleted most of them for size purposes.
I have an Access crosstab query that I have exported to an Excel Spreadsheet. I have the spreadsheet formatted using conditional formatting and I'd rather not have to reset it every morning. It's a single spreadsheet (the columns/rows will not deviate greatly day to day) and should be very simple, but I'm not getting it for some reason.
So if I have "Test.accdb" and it contains "qryX" as my crosstab and "Sheet1.xls" is my formatted Excel spreadsheet, how do I code for the latest "QryX" to go in and replace the old "QryX" data in "Sheet1.xls" ?
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate John Smith$15$10$15$30$8 Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I have a task of creating a database to track committee members attendance at meetings. Members can be on more than one committee. I have created tables for committees, committee population and committee members.
The meetings would take place once a month and do not need an exact date. A Yes/No would suffice for record of attendance. My question is what would be the best way to set up the table to track this since members can be on more than one committee?
I'm thinking that having a separate meeting table for each committee would be a bad idea. But haven't come up with any other ideas. Any suggestions???
for some days i've a problem with an Access-Query and up to now i didn't succeed to find an answer, i'm not even sure if there is one.
So, how to explain my problem. I'm trying to compile a Database for my small department to organize our computer-short-course students (application, grades, attendance...)
And with the grades i have a problem as well as with the attendance. Up to now we are just using paper sheets for grades and attendance. So i just thought that it would be the easiest thing to create a similiar-looking form, drawing of planned Query-output-layout is attached.
The attendance should be set with a checkbox. New days are set with a small subform. For the attendance i've this table-layout:
tblAttendance aSID aCID aDate aPresent
When a new day is set, every student gets a new record at the tblAttendance, which shall be compiled together for the later Attendance Form.
So now the big question comes, is it possible to create a query that fits my needs (variable amount of days and students and the possibility to set my records)? It is important that i've a big sheet with all the days, students of a course at the same time.
Any suggestions - i'm completly lost. Thanks in advance, protos
