Start and End times - same date

[vianda via AccessMonster.com]

I need to audit a form checking the times for the same date. I need to create
a report showing this info.

Date Time In Time Out
4/12 10:15 AM 11:15 AM
4/12 12:30 AM 1:00 PM

4/13 9:00 AM 9:30 AM
4/13 9:15 AM 10:15 AM overlap

4/14 10:00 AM 10:30 AM
4/14 10:30 AM 11:00 AM no travel time

I'm having a hard time to figure out how to substract the second time in from
the first time out. There are in different columns and rows. If somebody
can help i will appreciate it.

Viviana

 

[golfinray]

Try:
Datediff("h",[timein],[timeout])
That should give you the number of hours.

 

[KARL DEWEY]

If this is all there is to your data then it is simple, but if you have more
than two entries per day then you have to sequentially number them first to
do it.

In design view put your table in the space above the grid area twice.
Access will add a sufix of '_1' to the second instance.

Join the two table on the date field by clicking and dragging.

Pull down all fields from the first table.

Pull down the 'Time In' field from the second.

In the Criteria row under second table 'Time In' field type this --
<[FirstTable].[Time Out]

This will pull all records that have 'Time In' before 'Time Out'.

For datasets that have multiple entries per day you also need criteria on
the numbering to make sure you are comparing the following record. That
would be [FirstTable].[Number]+1 compared to [SecondTable].[Number].

 

[vianda via AccessMonster.com]

Karl I numbered all the entries in the table, I joined both tables on the
number field, date field didn't work. In the number field for both tables I
added the criteria you suggested no results. I'm substracting Time In (Table1)
from Time In (Table2) that works, but only between the same record not
between 2 records.
I don't know what I'm doing wrong - obviously I'm not understanding.

If this is all there is to your data then it is simple, but if you have more
than two entries per day then you have to sequentially number them first to
do it.

In design view put your table in the space above the grid area twice.
Access will add a sufix of '_1' to the second instance.

Join the two table on the date field by clicking and dragging.

Pull down all fields from the first table.

Pull down the 'Time In' field from the second.

In the Criteria row under second table 'Time In' field type this --
<[FirstTable].[Time Out]

This will pull all records that have 'Time In' before 'Time Out'.

For datasets that have multiple entries per day you also need criteria on
the numbering to make sure you are comparing the following record. That
would be [FirstTable].[Number]+1 compared to [SecondTable].[Number].

I need to audit a form checking the times for the same date. I need to create
a report showing this info.

[quoted text clipped - 14 lines]

Viviana

 

[vianda via AccessMonster.com]

Karl I numbered all the entries in the table, I joined both tables on the
number field, date field didn't work. In the number field for both tables I
added the criteria you suggested no results. I'm substracting Time In (Table1)
from Time In (Table2) that works, but only between the same record not
between 2 records.
I don't know what I'm doing wrong - obviously I'm not understanding.

If this is all there is to your data then it is simple, but if you have more
than two entries per day then you have to sequentially number them first to
do it.

In design view put your table in the space above the grid area twice.
Access will add a sufix of '_1' to the second instance.

Join the two table on the date field by clicking and dragging.

Pull down all fields from the first table.

Pull down the 'Time In' field from the second.

In the Criteria row under second table 'Time In' field type this --
<[FirstTable].[Time Out]

This will pull all records that have 'Time In' before 'Time Out'.

For datasets that have multiple entries per day you also need criteria on
the numbering to make sure you are comparing the following record. That
would be [FirstTable].[Number]+1 compared to [SecondTable].[Number].

I need to audit a form checking the times for the same date. I need to create
a report showing this info.

[quoted text clipped - 14 lines]

Viviana

 

[KARL DEWEY]

The join has to be on the date and not record numbering.

Post the SQL of your query with the date join.

Karl I numbered all the entries in the table, I joined both tables on the
number field, date field didn't work. In the number field for both tables I
added the criteria you suggested no results. I'm substracting Time In (Table1)
from Time In (Table2) that works, but only between the same record not
between 2 records.
I don't know what I'm doing wrong - obviously I'm not understanding.

If this is all there is to your data then it is simple, but if you have more
than two entries per day then you have to sequentially number them first to
do it.

In design view put your table in the space above the grid area twice.
Access will add a sufix of '_1' to the second instance.

Join the two table on the date field by clicking and dragging.

Pull down all fields from the first table.

Pull down the 'Time In' field from the second.

In the Criteria row under second table 'Time In' field type this --
<[FirstTable].[Time Out]

This will pull all records that have 'Time In' before 'Time Out'.

For datasets that have multiple entries per day you also need criteria on
the numbering to make sure you are comparing the following record. That
would be [FirstTable].[Number]+1 compared to [SecondTable].[Number].

I need to audit a form checking the times for the same date. I need to create
a report showing this info.

[quoted text clipped - 14 lines]

Viviana

 

[vianda via AccessMonster.com]

Here is the SQL:

SELECT Audit.Number, Audit.[Provider'sName], Audit.[Child'sName], Audit.
[Start Date], Audit.[Time In], Audit.[Time Out], Audit.Discipline, Audit.Type,
Audit.Disc, Audit.County, Audit_1.[Time In], Audit_1.Number, DateDiff("n",
Audit.[Time Out],Audit_1.[Time In]) AS Error
FROM Audit INNER JOIN Audit AS Audit_1 ON Audit.[Start Date] = Audit_1.[Start
Date]
WHERE (((Audit.Number)=[Audit].[Number]+1) AND ((Audit_1.Number)=[Audit_1].
[Number]))
ORDER BY Audit.[Provider'sName], Audit.[Start Date], Audit.[Time In], Audit_1.
[Time In];

Thanks Karl

The join has to be on the date and not record numbering.

Post the SQL of your query with the date join.

Karl I numbered all the entries in the table, I joined both tables on the
number field, date field didn't work. In the number field for both tables I

[quoted text clipped - 30 lines]

 

[KARL DEWEY]

This should work for you --
SELECT Audit.Number, Audit.[Provider'sName], Audit.[Child'sName],
Audit.[Start Date], Audit.[Time In], Audit.[Time Out], Audit_1.[Time In] AS
Second_Time_In, Audit.Discipline, Audit.Type, Audit.Disc, Audit.County,
IIf(DateDiff("n",[Audit].[Time Out],[Audit_1].[Time In])<0,"Error","Ok") AS
Error
FROM Audit INNER JOIN Audit AS Audit_1 ON (Audit.[Child'sName] =
Audit_1.[Child'sName]) AND (Audit.[Provider'sName] =
Audit_1.[Provider'sName]) AND (Audit.[Start Date] = Audit_1.[Start Date])
WHERE (((Audit_1.Number)=[Audit].[Number]+1));

This just pulls the errors --
SELECT Audit.Number, Audit.[Provider'sName], Audit.[Child'sName],
Audit.[Start Date], Audit.[Time In], Audit.[Time Out], Audit_1.[Time In] AS
Second_Time_In, Audit.Discipline, Audit.Type, Audit.Disc, Audit.County
FROM Audit INNER JOIN Audit AS Audit_1 ON (Audit.[Child'sName] =
Audit_1.[Child'sName]) AND (Audit.[Provider'sName] =
Audit_1.[Provider'sName]) AND (Audit.[Start Date] = Audit_1.[Start Date])
WHERE (((DateDiff("n",[Audit].[Time Out],[Audit_1].[Time In]))<0) AND
((Audit_1.Number)=[Audit].[Number]+1));

Here is the SQL:

SELECT Audit.Number, Audit.[Provider'sName], Audit.[Child'sName], Audit.
[Start Date], Audit.[Time In], Audit.[Time Out], Audit.Discipline, Audit.Type,
Audit.Disc, Audit.County, Audit_1.[Time In], Audit_1.Number, DateDiff("n",
Audit.[Time Out],Audit_1.[Time In]) AS Error
FROM Audit INNER JOIN Audit AS Audit_1 ON Audit.[Start Date] = Audit_1.[Start
Date]
WHERE (((Audit.Number)=[Audit].[Number]+1) AND ((Audit_1.Number)=[Audit_1].
[Number]))
ORDER BY Audit.[Provider'sName], Audit.[Start Date], Audit.[Time In], Audit_1.
[Time In];

Thanks Karl

The join has to be on the date and not record numbering.

Post the SQL of your query with the date join.

Karl I numbered all the entries in the table, I joined both tables on the
number field, date field didn't work. In the number field for both tables I

[quoted text clipped - 30 lines]

Viviana

 

[vianda via AccessMonster.com]

Karl thanks very much....it works! I really appreciate the time you took in
write the code ... thanks again?

This should work for you --
SELECT Audit.Number, Audit.[Provider'sName], Audit.[Child'sName],
Audit.[Start Date], Audit.[Time In], Audit.[Time Out], Audit_1.[Time In] AS
Second_Time_In, Audit.Discipline, Audit.Type, Audit.Disc, Audit.County,
IIf(DateDiff("n",[Audit].[Time Out],[Audit_1].[Time In])<0,"Error","Ok") AS
Error
FROM Audit INNER JOIN Audit AS Audit_1 ON (Audit.[Child'sName] =
Audit_1.[Child'sName]) AND (Audit.[Provider'sName] =
Audit_1.[Provider'sName]) AND (Audit.[Start Date] = Audit_1.[Start Date])
WHERE (((Audit_1.Number)=[Audit].[Number]+1));

This just pulls the errors --
SELECT Audit.Number, Audit.[Provider'sName], Audit.[Child'sName],
Audit.[Start Date], Audit.[Time In], Audit.[Time Out], Audit_1.[Time In] AS
Second_Time_In, Audit.Discipline, Audit.Type, Audit.Disc, Audit.County
FROM Audit INNER JOIN Audit AS Audit_1 ON (Audit.[Child'sName] =
Audit_1.[Child'sName]) AND (Audit.[Provider'sName] =
Audit_1.[Provider'sName]) AND (Audit.[Start Date] = Audit_1.[Start Date])
WHERE (((DateDiff("n",[Audit].[Time Out],[Audit_1].[Time In]))<0) AND
((Audit_1.Number)=[Audit].[Number]+1));

Here is the SQL:

[quoted text clipped - 20 lines]