Double Records in Report

[Drum2001]

My question is in reference to the following closed thread.

http://groups.google.com/group/comp....29479ec733This
is my current query:

This is my current query:

SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[cl*ockin],[clockout]))-40*60*60,0)

AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1

HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));

Bob, last posted "I cannot see why that sql is returning two records,
unless data
in one of the group by fields is not identical."

I think the line "DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS
WeekOf " of my query may be causing the problem. These field does not
produce identical records.

By keeping this in there, does anyone have suggestions on how to make
this so I don't have multiple records?

Thank you!

Brad

[salad]

Drum2001 wrote:

My question is in reference to the following closed thread.

http://groups.google.com/group/comp....29479ec733This
is my current query:

This is my current query:

SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[cl*ockin],[clockout]))-40*60*60,0)

AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1

HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular")); Bob, last posted "I cannot see why that sql is returning two records,
unless data
in one of the group by fields is not identical."

I think the line "DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS
WeekOf " of my query may be causing the problem. These field does not
produce identical records.

By keeping this in there, does anyone have suggestions on how to make
this so I don't have multiple records?

Thank you!

Brad

If I were in your situation, I might first create a new SELECT query
that simply pulls in the detail info.

SELECT Employee, TimeTypeIn,
[clockin],[clockout],DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS WeekOf
FROM [DateFilter - Admin]
Where ((([DateFilter - Admin].TimeTypeIn)="Regular"));

I don't know if it's possibe for you to do a calc on clock-in/out here.
ANyway, save the query as query 1.

Now create query 2. Use Query1 as the table. Do your summing and
grouping in this query.

I'd sooner have 2 queries that I can follow easily than 1 query that I
bust my ass over it.

[Drum2001]

Thank you for the suggestion!

When I do that, it does not subtract the "regular time".

For example:

Total Hours = 85:35:55
Overtime = 45:35:55

So it is not subtracting the "40:00:00"

Any suggestions why?



salad wrote:

Drum2001 wrote:

My question is in reference to the following closed thread.

http://groups.google.com/group/comp....29479ec733This
is my current query:

This is my current query:

SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[cl*ockin],[clockout]))-40*60*60,0)

AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1

HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));

Bob, last posted "I cannot see why that sql is returning two records,
unless data
in one of the group by fields is not identical."

I think the line "DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS
WeekOf " of my query may be causing the problem. These field does not
produce identical records.

By keeping this in there, does anyone have suggestions on how to make
this so I don't have multiple records?

Thank you!

Brad

If I were in your situation, I might first create a new SELECT query
that simply pulls in the detail info.

SELECT Employee, TimeTypeIn,
[clockin],[clockout],DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS WeekOf
FROM [DateFilter - Admin]
Where ((([DateFilter - Admin].TimeTypeIn)="Regular"));

I don't know if it's possibe for you to do a calc on clock-in/out here.
ANyway, save the query as query 1.

Now create query 2. Use Query1 as the table. Do your summing and
grouping in this query.

I'd sooner have 2 queries that I can follow easily than 1 query that I
bust my ass over it.

[Larry Linson]

Are you trying to keep that time in a Date/Time Field or Variable? Those are
for identifying a point in time, that is, a Date and Time of Day. If you
want to do calculations on time, you'll need to convert to the smallest unit
of interest, calculate, and then separate the larger units -- in Numeric
Fields, not Date/Time Fields.

Larry Linson
Microsoft Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Thank you for the suggestion!

When I do that, it does not subtract the "regular time".

For example:

Total Hours = 85:35:55
Overtime = 45:35:55

So it is not subtracting the "40:00:00"

Any suggestions why?



salad wrote:

Drum2001 wrote:

My question is in reference to the following closed thread.

http://groups.google.com/group/comp....29479ec733This
is my current query:

This is my current query:

SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[cl*ockin],[clockout]))-40*60*60,0)

AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1

HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));

Bob, last posted "I cannot see why that sql is returning two records,
unless data
in one of the group by fields is not identical."

I think the line "DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS
WeekOf " of my query may be causing the problem. These field does not
produce identical records.

By keeping this in there, does anyone have suggestions on how to make
this so I don't have multiple records?

Thank you!

Brad

If I were in your situation, I might first create a new SELECT query
that simply pulls in the detail info.

SELECT Employee, TimeTypeIn,
[clockin],[clockout],DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS WeekOf
FROM [DateFilter - Admin]
Where ((([DateFilter - Admin].TimeTypeIn)="Regular"));

I don't know if it's possibe for you to do a calc on clock-in/out here.
ANyway, save the query as query 1.

Now create query 2. Use Query1 as the table. Do your summing and
grouping in this query.

I'd sooner have 2 queries that I can follow easily than 1 query that I
bust my ass over it.

[Drum2001]

Larry,

Thank you for the response.

I actually have a function "sec2dur" that converts all the date/times
to numeric fields:

sec2dur()
//
Public Function SectoDur(seconds As Long) As String
On Error Resume Next
Dim hrs As Long
Dim Mins As Integer
Dim secs As Integer
hrs = Int(seconds / 3600)
Mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + Mins * 60)
SectoDur = Format(hrs, "#,##0") _
& ":" & Format(Mins, "00") & ":" _
& Format(secs, "00")
//

If I do not "Filter" the results, the overtime is displayed correctly,
however, in to separate rows.

Larry Linson wrote:

Are you trying to keep that time in a Date/Time Field or Variable? Those are
for identifying a point in time, that is, a Date and Time of Day. If you
want to do calculations on time, you'll need to convert to the smallest unit
of interest, calculate, and then separate the larger units -- in Numeric
Fields, not Date/Time Fields.

Larry Linson
Microsoft Access MVP
"Drum2001" <dr******@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Thank you for the suggestion!

When I do that, it does not subtract the "regular time".

For example:

Total Hours = 85:35:55
Overtime = 45:35:55

So it is not subtracting the "40:00:00"

Any suggestions why?



salad wrote:

Drum2001 wrote:

My question is in reference to the following closed thread.

http://groups.google.com/group/comp....29479ec733This
is my current query:

This is my current query:

SELECT [DateFilter - Admin].Employee, [DateFilter - Admin].TimeTypeIn,
IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[cl*ockin],[clockout]))-40*60*60,0)

AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM [DateFilter - Admin]
GROUP BY [DateFilter - Admin].Employee, [DateFilter -
Admin].TimeTypeIn, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1

HAVING ((([DateFilter - Admin].TimeTypeIn)="Regular"));

Bob, last posted "I cannot see why that sql is returning two records,
unless data
in one of the group by fields is not identical."

I think the line "DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS
WeekOf " of my query may be causing the problem. These field does not
produce identical records.

By keeping this in there, does anyone have suggestions on how to make
this so I don't have multiple records?

Thank you!

Brad

If I were in your situation, I might first create a new SELECT query
that simply pulls in the detail info.

SELECT Employee, TimeTypeIn,
[clockin],[clockout],DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
AS WeekOf
FROM [DateFilter - Admin]
Where ((([DateFilter - Admin].TimeTypeIn)="Regular"));

I don't know if it's possibe for you to do a calc on clock-in/out here.
ANyway, save the query as query 1.

Now create query 2. Use Query1 as the table. Do your summing and
grouping in this query.

I'd sooner have 2 queries that I can follow easily than 1 query that I
bust my ass over it.