Attendance database report - summary report

[Jeromenew]

Dear all

We have an existing database, supplied by the "door access" software vender.
It also has two fingerprint scanners [one indoor and one outdoor]

One of the tables in the database has
user ID
date with time
IN or OUT flag

Now, we need to prepare a report
1. Weekly summary report – Which shows 7 days records in 7 columns as landscape format with each column has two sub columns IN – OUT.
a. As a normal practice, users used to go out for some reason and will return during working hours and most of the times they use the scanner to enter the OUT and IN when they leave the office and return to office.
b. But sometimes they forget to scan or if scanner did not recognize the scan, as a result, they our record will not be exactly for every IN there is an OUT or every OUT there is an IN.
c. Normal working hours are 8AM to 5PM.
d. Format in each column [date] as 08:05 – 17:10 for the employees who came at 08:00AM and left 17:10 PM who did not leave the office during office hours
e. Format in each column [date] as (line -1) 08:05 – 11:24; (line -2) 12:46 – 13:15; (line -3) 14:00 – NULL ; (line -4) 14:34 – 15:11; (line -5) NULL – 17:00. The NULL values are the places where employee did not enter the scan.
f. At the bottom of each column, the total hours IN and total hours OUT of the office.
I need to design the query for the report, please assist me.

Thank you.
Jerome

 

[spikepl]

Show your efforts so far.

 

[Jeromenew]

Input forms designed with date range and user selections.
I tried many combinations of query, but I am unable to get the proper results.
perhaps I developed some other detailed report, but I need the summary report as mensioned.

 

[highandwild]

I have some ideas on how this can be done but I need some more information from you.

An example of the data would be handy, exclude identifiable data, A large number of records representing all different scenarios would be good. CSV, Access or Excel format OK.

A draft picture of what you expect the report to look like would be handy. A mock up in Word will suffice.

Are you expecting this to be accomplished without using VBA or can you write some VBA if required?

The total hours IN and OUT of the office is not a summary of the IN and OUT columns. Surely you just need to know, for each of the 7 days, how many hours thay have spent in the office. Do you really need to know how long they have spent out of the office?

Instances when you have an IN record and not an OUT record and vice-versa should be picked up as soon as possible on an exception report and corrected before this report has a real meaning. What should the report sum for the day if either of these is the case? Reporting using records that do not exist is possible but often tricky and maybe inadvisable.