time&attendance 1

[tezzyr]

Hi guys,
I'm writing an application using crIX and a ms access database. I'm new to CR so please bear with me and forgive any obvious mistakes

The report I am writing will show how long a person has been in work, ie total number of hours, minutes seconds between them logging in at the start of the day and logging out at the end of the day.

what would be the record selection formulae to use to display this kind of information on a crystal report?

Events are stored in one access database table called 'Accessevent' which is set up as follows:-

Eventid (PK)
Employee name (text)
Eventtime (datetime)
Eventtype(number)

when an employee logs in, a record is created with an eventtype of 1. when they log out, another record is created but with an eventtype of 2. Date and time of each event is stored in each record in the Eventtime field.

I want to be able to display the employee's name, the time they logged in and the time they logged out, with a total time field showing how long they were logged in.

What would be the best way to achieve this bearing in mind that the login and logout events are in two different records? how can i link the two events together so that a log in eventtime is linked to the correct log out eventtime for that day?
What would the report selection formula be?

Thanks in advance for all your help.

 

[Madawc]

Group by employee, and use Report > Record Sort Expert to put them in date-time order.

For each type 2, have something like this:

if Previous({Eventtype}) <> 1 or Previous({Employee name}) <> {Employee name}
then "Log-in missing"
else "Minutes logged in " & 
ToText(DateDiff("m", {Eventtime}, Previous({Eventtime})

You probably don't want just minutes, but get this working and then refine it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[tezzyr]

Thanks for the help Madawc.

The report will need to show the time in and time out for each employee as well as the total time.
so, my report will look like the following:-

name time in time out total
Fred bloggs 01/01/2007 09:00:00 01/01/2007 11:00:00 2:00:00

but how do i display a time out? how can i match a time out event to a time in event for this employee.

at the moment I see the following:-

name time in time out total
Fred bloggs 01/01/2007 09:00:00 01/01/2007 09:00:00

Fred bloggs 01/01/2007 11:00:00 01/01/2007 11:00:00

so each record in the accessevent database table is displayed on its own line, rather than combining two records (one for logging in and one for logging out) on the same line of the report

Thanks for your help and patience so far and I'm really sorry if I'm making any obvious errors!

 

[MrBillSC]

Is there any common field that ties the time-in and time-out records such as date or shift etc?

MrBill

 

[lbass]

If there is one eventtype = 1 and one eventtype = 2 per event ID, then you can insert a group on employee and then a second group on event ID. Then create two formulas:
//{@in}:
if {table.eventtype} = 1 then {event.date}

//{@out}:
if {table.eventtype} = 2 then {event.date}

Then create a formula {@total time} for the eventID group footer:

datediff("n", maximum({@in},{table.eventID}),maximum({@out},{table.eventID})/60

This would give you the time in hours. You can convert this to a string display if you wish.

To display the in and out times, right click on each formula in the detail section and insert maximums.

-LB

 

[tezzyr]

Thanks for all the help so far guys!
I've grouped by employee but because the events are in two seperate database records, the log in event is displayed on one line and the log out event is displayed on the line below.

I've used a formula that will only display the login time if the eventtype is 1. this works fine. I've also created a formula and placed it on the report to only display the time if the eventtype is 2, this also works.

However, the data is being displayed on two different lines like this:-

name time in time out
Fred bloggs 01/01/2007 09:00:00
Fred bloggs 01/01/2007 11:00:00

How can i display the log out event on the same line as the log in event seeing as they are in two different database records?

thanks everyone!

 

[lbass]

I already explained that in my most. Insert maximums on these formulas and then suppress the detail section.

-LB

 

[tezzyr]

I'm still only seeing one event per line, either a login event or a logout event.

formulae for the date/time events are:-
INevent formula:

IF ({Accessevent.Eventtype}=1) Then {Accessevent.Eventtime}

Outevent formula:

IF ({Accessevent.Eventtype}=2) Then {Accessevent.Eventtime}

each record in the database only has one eventtype.
when someone logs into my application, a record is created in the database 'accessevent' table which stores the employee name, time of the event and an eventtype of 1.

when someone logs out of the application, another record is created in the same database table with the same employee name but this record will have an eventtype of 2 to show that it is a logging out event.

I'm trying to create a report that shows an employee's log in time and their log out time, but as this information is not stored in the same database record the report is showing them as two seperate lines.
If i group by employee, i get the employee name and then the log in time.
on the line below i get the log out time.

eventid is just used as the primary key for the accessevent table and wont be displayed.

Thanks for the help and I'm sorry If it appears as though I'm ignoring your kind advice.... I'm not, I've just never used cr before.

Thanks again

 

[lbass]

As I said, group on employee and then on event ID. Place the formulas in the detail section and insert maximums on them at the event ID level and suppress the detail sections. The point of using the event ID is so that you have in/out times related to one date.

Please try this. You can drag the employee name into the event ID footer and remove the event ID groupname, if you wish.

-LB