Total Hours Calculations

[Laura1982]

How can I make Access perform a calculation of hours?
I have a table for called Hours Worked, which must contain EmplyeeID, Date, Start Time, Finish Time.

I have designed this table, however later on I have to design a query for the table that calculates the total number of hours worked and pay for each employee, sorted highest to lowest. Any suggestions?

I also have an Award table (classification, wage/perhour etc) and an employee list of who is what classification if it helps?

 

[Jack Cowley]

You will find your answer to calculating time in Access here. In your query you can sort ascending or decending so that should not be a problem.

Also, do not use the word Date as a field or control name as Date is a reserved word in Access. One more thing, do not use spaces in field names either. Start Time should be StartTime.... Spaces can give you more problems than you can imagine so get in the habit of leaving them out.

hth,
Jack

 

[Laura1982]

I'm afraid that the page doesn't make a whole lot of sense to me?
Is there a simpler way of explaining it ? We have only been given three weeks to learn Access, so I'm pretty much a virgin-access user.

 

[Jon K]

You can first build a Totals query (in query Design View, click on the Totals button on the toolbar to display the Total: row in the query grid) against the HoursWorked table to find the TotalHoursWorked Sum(DateDiff('s',[StartTime],[FinishTime])/(60*60)) for each EmployeeID GROUP BY EmplyeeID.


Then build another query (using the Employee table, the Award table and the query that you have just built) to calculate the WagesPaid to each Employee TotalHoursWorked*Wages and sort it as Descending.


I have attached a sample DB in Access 97 format. If you use Access 2000 or 2002, just choose Convert and save as a new name when it is opened for the first time.

Edit:
To make the WagesPaid in the second query easier to read, you can display the TotalHoursWorked and the Wages fields as well in the query.

 

[Laura1982]

Error message

The total hours went fine, thanks for the help.

However, I got the message "Cannot Join on Memo, OLE or Hyperlink Object ([Award Table].Classification=[Employee Table].Classification).

Where have I gone wrong?

 

[Jon K]

You can check the data type of the two Classification fields. If they are Memo fields, just change them to Text. Memo fields cannot be used for linking tables.