Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Employee Time Sheet

Posted on 2004-10-08
12
Medium Priority
?
445 Views
Last Modified: 2012-06-21
I have a worksheet set up to track employees sign-in and sign-out times from the start of their shifts including the lunch breaks to the end of thier shift.
But my problem is that this sheet is disigned to track 12+ employees, but in order for me to to that I have to create 12+ sheets - which is a no-no given all that scrolling and switching bettween sheets.
I want to be able to create a drop-down list with all the employees there, that when I click on a specific employee the worksheet will change all its values based on this particular employee's work hours.
To be more especific when I enter data on "Mike" it will stay that way only for him even if I click on "John" to check his work hours.
If you need a copy of my worksheet, just tell me how to paste it here, or give me an e-mail to send it to you...
If anybody know of a template or already have this type of sheet, can you please help?

Thanks in advance!

-Sarcigon
0
Comment
Question by:Sarcigon
  • 5
  • 3
  • 2
10 Comments
 
LVL 1

Expert Comment

by:perennial
ID: 12266746
Are you in the right section? Sounds like you need help in Excel. If in Access, how is your table structure?
0
 

Author Comment

by:Sarcigon
ID: 12266865
Yes, I was in EXCEL, but someone suggested me to this forum instead, I think it's kind of dificult to do in Excel. I want to explore all possibilities.
Below there's a link to my sheet.
Thanks
-Sarcigon

http://www.geocities.com/sarcigon/EmployeeTimeSheet.xls

0
 
LVL 1

Accepted Solution

by:
perennial earned 100 total points
ID: 12266985
Assuming that you will type in the hours manually.

Create two tables:

Table 1: tblEmpInfo

EmpID >>(Primary)This can be auto number
LName
FName
SSNo
...So on as you need

Table 2: tblClockInOut

EmpID
WorkDate >>Date record..could have it default to current date
ClockIn
LunchOut
LunchIn
ClockOut

...add fields as needed

Create a relationship from tblEmpInfo to tblClockInOut as one-to-many.

Design a form, use tblEmpInfo as your main form and use tblClockInOut as your subform.

Let me know if you need more details.

perennial

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Sarcigon
ID: 12288433
Perennial, How do I create a reltionship between those tables?
That question might sound stupid, but I'm that good on Access, the little knowledge I have is in Excel.
So bear with me, because I'm novice in this Application Development thing.
Your understanding is greatly appreciated.

-Elvin
0
 

Author Comment

by:Sarcigon
ID: 12310160
This is what I have created so far...
But I can't get it to add the times and to roll the days like in Excel.
I get errors everytime I tried to put in a formula.
Below ther's a copy of my sheet, is done but is dead.
Can anyone help!!!!!!!?????????


http://www.geocities.com/sarcigon/EmployeeTimeSheet.mdb
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12329219
Hmm, daunting task.
You'll have to drop "thinking in excel sheet format" and start designing tables.
In this case you'll need a tblPersonnell and a table (tblTimes) with the recorded times.
Before doing so you'll need to be sure that there's no recording needed for overwork. The present form doesn't allow that and also working on saturdays and sundays can't be recorded.

Nic;o)
0
 

Author Comment

by:Sarcigon
ID: 12329326
I already have two tables done, do I have to delete them and make new ones?
I know I'm kind of thinking in Excel mode, sorry.
But when I was sent to this forum from Excel I actually thought it was gonna be easier.
Did you get the sheet I created?
Do I have to re-create them?
I'll take the overwork out like you suggest.
When you create a table does it have to be with the prefix "tbl"..?
0
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 100 total points
ID: 12329415
Prefixing is easy when you're also having queries with a prefix "qry" you can keep them easily separated later when coding.

Time in a table can be done in several ways.
Having a fixed number of timeslots allows a table like:
PersonID(*), DateRecording(*), StartTime1, EndTime1, StartTime2, EndTime2, etc

The most flexible and "normalized" way would however be:
PersonID(*), DateRecording(*), StartTime(*), EndTime1

The (*) indicates the fields defining the unique key of the row.
The flexible way is the best as it won't "ruin" your application when the company decides to go to work in multiple shifts.

On the form you can use now a mainform bound to the person data and a combobox to allow the choice of a certain week.
These fields can be used to link a subform with to show the rows for that person and the selected week.
Getting a total hours can be done now with a field in the footer in the "excel" mode like:
=SUM(fieldname)

Nic;o)
0
 

Author Comment

by:Sarcigon
ID: 12366651
How do I show the day only in in the access date format, I can't figure that out.
Access only aloww me to use short, medium and long date.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12367591
Use the format property "input mask" under the Data tab and/or the Format() function.

Nic;o)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question