View Full Version : Pulling dates and Summing and avaatge the totals;

01-18-2005, 07:10 PM
I'm worling on a small project the keeps time keeping for my staff. I have gave my staff a specific id's and set-up the relationships in an access database.

The problem I have is I can't work out the reporting . I need help in puling a date range and taking a total of how mins late thye are and on who average or late they are. I have been playing about with scripts for while but know I'm way off.

Can any help?

01-19-2005, 09:38 AM
I'm not sure what your asking.

Are you wanting a way to report on who has been the latetest?
Are you wanting to give an average of how late your employees are?

What type of report are we looking for? What is the end result we desire?
when we look at the report, what do we want to learn from it?

01-19-2005, 05:47 PM
I need to pull a weekly report on who was late. And it would be good to see on avargae who was late monthly.

If I get some help on how to pull this info, i think i can work out the rest myself. I managed to work out this

<!--#INCLUDE file="pull_drop_down_name.asp"-->
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsReport 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("database.mdb")

NameID= Clng(request.QueryString("NameID"))

'Create an ADO recordset object
Set rsReport = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database

'strSQL = "SELECT * FROM Data WHERE NameID=" & NameID & " ORDER BY Date_Late"

strSQL = "SELECT SUM(Late) as DisplayTotal FROM Data WHERE NameID=" & NameID

'Open the recordset with the SQL query
rsReport.Open strSQL, adoCon

Total Mins Late: <% = rsReport("DisplayTotal")%>

'Reset server objects
Set rsReport = Nothing
Set adoCon = Nothing


01-19-2005, 09:41 PM
How you report it really depends on how you have your database designed.

I may select rows where login>date+08:00:00

You could DateDiff() the login time with that mornings actual employee login.

Do they clock in and out of this system, or it it typed in later?

Forgive me if I sound like I'm grilling you. It just seems serendipitous that I start a system like this tomorrow. I thought I might gather a different perspective on how it may be done.

01-21-2005, 08:15 PM
The time keeping in manually ipputed by me. I pull the info the a call management system call "Ava" formally called Lucent