Calculating Time for two week period

[Breanna]

First off sorry it is soo long! There are alot of important details
I am using access 2013 and have an issue creating a query with employee hours for a two week period.

For starters I have a table with the following:

Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)

My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10. (I hope that makes sense)

At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.

In my query (to Start) I need to have the following with it set up per pay period.

Employee Number Rate Code Regular Hours Overtime Hours

I have no idea how to set it up so that it calculates the hours based per rate code with the given pay period. This is above my skill (as is everything on access lol) and I think it would require some advanced VBA code which I would rather avoid.

Any ideas would help to get me started thank you.

 

[plog]

First, your table isn't structured properly.

-Date is a reserved word and shouldn't be used as a field name. Instead you should prefix it with what the date represents (e.g. WorkDate)

-You shouldn't even have a Date field, this value should be in your 2 time fields. Time In and Time Out should be Date/Time field types, and you should be capturing the date in there.

- my guess Week End is redundant. Weekend most likely can be deduced by looking at Date and/or Pay period. I'd have to see data or hear an explanation of what it holds to be sure.

With that said. We don't know your over time rules. Can you provide data to demonstrate? Include starting data from your table (lumps and all) and then what you expect as resulting data. Be sure to include enough data to cover all cases you need to account for.

 

[Breanna]

First, your table isn't structured properly.

-Date is a reserved word and shouldn't be used as a field name. Instead you should prefix it with what the date represents (e.g. WorkDate)

-You shouldn't even have a Date field, this value should be in your 2 time fields. Time In and Time Out should be Date/Time field types, and you should be capturing the date in there.

- my guess Week End is redundant. Weekend most likely can be deduced by looking at Date and/or Pay period. I'd have to see data or hear an explanation of what it holds to be sure.

With that said. We don't know your over time rules. Can you provide data to demonstrate? Include starting data from your table (lumps and all) and then what you expect as resulting data. Be sure to include enough data to cover all cases you need to account for.

-You are right about "Date" as a reserved word. I an very new and always forget that, thank you for reminder

-I do not know how I would put date and time in in one column because I get the data for that table by importing it from an excel spreadsheet. (the guys use the sheet to fill out their times then email them to me and I import them using the external data tab in the ribbon.) this is easier for the employees because they do not come into the office everyweek nor do they all have access to our server which is why I have not tried to set up a form on access for them to use instead of excel.

-Weekend does seem very redundant to me (as well as pay period) but I thought I needed some kind of identifier in order to filter out what I needed as well as use that to calculate Overtime pay

Our overtime policy is paid out at 1.5 for anything over 40hours within one week.
Below I pasted some sample data (I hope it posts in a readable format)

EmplyNum	Date	TimeIn1	TimeOut1	SiteName	Descrition	RateCode	WeekEnd	PayPeriod
5678	12/22/2014	6:00 AM	8:30 AM		ground	0608-04	12/28/14	Dec 22- Jan 4
5678	12/22/2014	8:30 AM	12:00 PM		ground	0608-04	12/28/14	Dec 22- Jan 4
5678	12/22/2014	12:30 PM	3:00 PM		Ground	0608-04	12/28/14	Dec 22- Jan 4
5678	12/22/2014						12/28/14	Dec 22- Jan 4
5678	12/23/2014	6:00 AM	8:30 AM		office	4909-00	12/28/14	Dec 22- Jan 4
5678	12/23/2014	8:30 AM	10:00 AM	SE05051A	office	4909-00	12/28/14	Dec 22- Jan 4
5678	12/23/2014						12/28/14	Dec 22- Jan 4
5678	12/23/2014						12/28/14	Dec 22- Jan 4
5678	12/24/2014	12:15 AM	8:15 AM		vacation		12/28/14	Dec 22- Jan 4
5678	12/24/2014						12/28/14	Dec 22- Jan 4
5678	12/24/2014						12/28/14	Dec 22- Jan 4
5678	12/24/2014						12/28/14	Dec 22- Jan 4
5678	12/25/2014	12:15 AM	8:15 AM		Holiday		12/28/14	Dec 22- Jan 4
5678	12/25/2014						12/28/14	Dec 22- Jan 4
5678	12/25/2014						12/28/14	Dec 22- Jan 4
5678	12/25/2014						12/28/14	Dec 22- Jan 4
5678	12/26/2014	12:15 AM	8:15 AM		Vacation		12/28/14	Dec 22- Jan 4

Eventually I need to be able to generate a report based off of the payperiod that shows each employee, the rate code, regular hours per rate code, overtime hours per rate code, vacation hours, holiday hours. Right now I am just focusing on understanding how to calculate the total hours and OT hours in a query. I know this is complicated. (at least for me!) Thank You so far for the help.

 

[Breanna]

I apologize for the messy data in my table example. I am not sure how to fix it so it all flows together in a more readable format... :/

 

[plog]

You can use CDate(http://www.techonthenet.com/access/functions/datatype/cdate.php) to merge your Date and Time data together. As for Weekend/PayPeriod, I can't parse what you have so I can't say.

Can you make a spreadsheet to demonstrate what you have/need? Include 2 tabs--

On one tab, put sample data from your table.
On the second tab, put what you expect as a result of the first tab.

Be sure to include enough data to cover all cases, table name and field names.

 

[Breanna]

Ok. So attached is an excel spread sheet with a table tab showing example data for from my table in access and a report tab showing what and how I need the information.

in the long run I will need to be able to generate a report that has that layout but right now I am trying to focus on understanding how to even create a query with those calculations based on times in the table.

Thank you for reading.

 

[plog]

Nope. You have me #1, but not #2. I need sample starting data, which you provided on the Table tab. I also need what data you expect out of the query based on the data on the Table tab.

So provide me another tab which has what data you want out of the query based on you feeding it the data in the Table tab. You don't get to use any formatting at all, just tell me the field names and values each should have.

 

[Breanna]

Nope. You have me #1, but not #2. I need sample starting data, which you provided on the Table tab. I also need what data you expect out of the query based on the data on the Table tab.

So provide me another tab which has what data you want out of the query based on you feeding it the data in the Table tab. You don't get to use any formatting at all, just tell me the field names and values each should have.

You made me laugh with your "nope" Sorry if I am being frustrating. I attached a spreadsheet with the added tab. I hope it is what you are asking for.

 

[plog]

Nope again. I need 2 sets of data, not instructions. Show me exactly what you want as resulting data from your starting data.

 

[Breanna]

3rd times the charm :/

 

[plog]

I don't think so. On your table tab you have 1 record for EmplyNum=5678, RateCode=0608-04 (row 10). His TimeIn/TimeOut yields 4 hours, however on the query tab the reg Hours you expect is 20. Where did those other 16 hours come from. That's not even mentioning the 5 overtime hours you expect.

Your data doesn't jive. I think I'm done.

 

[Breanna]

I don't think so. On your table tab you have 1 record for EmplyNum=5678, RateCode=0608-04 (row 10). His TimeIn/TimeOut yields 4 hours, however on the query tab the reg Hours you expect is 20. Where did those other 16 hours come from. That's not even mentioning the 5 overtime hours you expect.

Your data doesn't jive. I think I'm done.

If you dont want to help I totally understand. For someone as new as me you have not been that specific in your request data. I did not realize you wanted me to calculate that myself. I put in numbers as sample data because the hours in the table do not have overtime. If I wanted to use excel I would set up the calculations in excel. What I need help with is how to get the query to calculate what I need. Sorry if I did not know that in order to accomplish this I already had to know the correct total hours.