Excel File to Access

fabian24

New member
Local time
Today, 07:03
Joined
Dec 20, 2008
Messages
7
I have an excel file that I have made for calculation of the overtime of employees.
Is it possible to convert this in to a access program.
how can we go about it ?
I am attaching the excel sheel so you guys can see what I am taking about.

Please please help me if u can...
 

Attachments

Well, a rather "different" time keeping spreadsheet.... Can you convert it... Yes, in a way... Should you? No.
I tried entering one day... first day entered "in" and "out"... worked fine... then entered OT hours.... Well.. First problem is it let me do that. And then you have a rather odd formula that adds time to "payable hours"... 1 became 1.25 ... Now the employees would LOVE this! But I don't think the boss would :)
Normally, baring any union contracts or other variables unknown to me, OT only comes into play (US law) after 40 hours a week. This in mind the OT should calculate itself.
But thats beside the point.....
I would do it all in Access.... Your Excel files would need to be saved under various names, get VERY lengthy, and hard to read. In Access you would have one DB, all dates, personnel, and any other info packed into a neat little DB. You could look up hours, people, by whatever criteria you need. You can also tie hours to jobs or projects... very handy when calculating job cost.
Look around...Lots of examples on doing time sheets in Access, then give it a try....You will like it a lot more then just using Excel.
 
I just had a quick look and it appears that all your calculations are either across the row or straight down the column. That is an easy one to go to Access.

Excel to Access gets tricky when there are groups of calculations on the worksheet and with reference between the groups. In other words when cell reference is diagonal.

And ditto what CEH said about the pros of having in Access. Just remember that for unbound textboxes and calculated query fields to use IIF as opposed to IF
 
Thanks guys for the inputs.
Let me start by first telling you, that I stay in United Arab Emirates.
The Labour law here, specifies tat every hour worked after normal working hour is multplied by 1.25 and on holidays this number become 1.5.

Also the other thing is hour between 21:00 to 4:00 are also calculated as 1.5 times. This is the tricky bit.

Doing this in access might be a little tricky thing !!!

Please help me convert it to Access, as it would drastically improve the quality of the work sheet.
 
My approach (others will be different) would be to first make a table and the fields would duplicate the cells you have for data entry.

In that table I would have field names that matched the column names in Excel, that is, I would have fields with names such as C, F, J etc

I would then make a query and drag in all the fields but insert columns between the fields for calculated fields. I would also give these calculated field names the same as the column names. Initially I would just have simple entries for the calculations such as field J would be J:2 or J:"J"

In other words I would basically tansfer a row across to Access but without the formulas

Your calculations will be similar except for IFF instead of IF and you won't need references such as J13 or C17 etc, it will be just J or C since the calculation in Access automatically applies to the row.

For example in Cell O, which will be the calculated field O in your query you have

=24*(IF(M10>N10,N10+1-M10,N10-M10)) which would become

O:24*(IF([M]>[N],[N]+1-[M],[N]-[M]))

Once you have the record working OK in Access (obviously solving any issues along the way) then start to look at optimising it for use in Access.

In other words, the best way to have that in Access might not be the same as the display you have in Excel. However, I think it will be much easier to optimise it for Access once you already have it basically working in Access.
 
To add my 2 cents more worth.... Adding to Mikes post....
It sounds like your basic setup is still going to be similar to one for the States...
With minor exceptions in your formulas to account for local labor laws.... "Hours after normal working hours" either 8 per day...40 per week..whatever.. would still be time and a quarter... apposed to time and a half here... Since your holidays may be very different I might think about using a checkbox to identify a day as a holiday... Then a simple If...then statement .... If [CheckboxHoliday] = true THEN [HoursToPay] = [Hours]*1.5 else (nothing)
You could also use an If...Then statement for your time ... If [Hours]>2100 and <0400 THEN.... "calculation"
You might also look into "Select case" statements... Sometimes using code, the "If....Then...or "Select Case" is a little easier then long IIF statements.
 

Users who are viewing this thread

Back
Top Bottom