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(DateDif f("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)="R egular"));
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 4 2031
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(DateDif f("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)="R egular"));
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)="R egular"));
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.
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(DateDif f("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)="R egular"));
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)="R egular"));
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.
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.goo glegroups.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(DateDif f("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)="R egular"));
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)="R egular"));
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,
Thank you for the response.
I actually have a function "sec2dur" that converts all the date/times
to numeric fields:
sec2dur()
//
Public Function SectoDur(second s 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.goo glegroups.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(DateDif f("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)="R egular"));
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)="R egular"));
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Chris Hall |
last post by:
The records in my database are displayed in a form as follows:
%>
<form action="report-ammend1.42.asp" method="post"name="form">
<table border=1>
<%
x = 1
|
by: mark |
last post by:
How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6 records for
each day. I need each weekday's records returned on only one detail page.
Instead I am getting a new table layout for each day of the week. I have tried
grouping on every record and combination I can, manipulating the Group
Properties but can't get it right. I have the entire report in the Detail
Section, with...
|
by: JC |
last post by:
Hi,
I have a database that imports 4 reports to my "data" table, each
report has its own identifier. Then I have a table with Analysts with
the report identifier that they are to be assigned to. What I need to
do is loop the two tables so that an Analyst is assigned to each
record in the "data" table based on the identifier. For example, I
have one analyst that needs to be assigned to the "H" report records,
one analyst that needs to...
|
by: Terri |
last post by:
I have a form with a multi-select combo. I dynamically build a SELECT
statement, open a report, and set the recordsource to my dynamic SELECT
statement. I count the records returned in the report by setting a text box
to =Count(*). This works fine.
Now I want to count the unique records in my report. I can dynamically
create a SELECT statement that counts unique records. My statement looks
like this:
SELECT DISTINCT...
|
by: itm |
last post by:
I have a mail out to send to a group of owners with multiple accounts.
I want to limit the number of accounts that print on the first page to
20. I want remaining accounts to print on a second page. Is there a
way to accomplish this?
Thanks so much for your help!
| |
by: melnhed |
last post by:
---Report the current filtered records from a Form---
Hello All,
I've seen this topic discussed before, but the solution described then
doesn't work in my particular case.
My Config: Access 2002 front-end using SQL Server 2000 (MSDE actually)
via ADP/ADE Access Data Project.
|
by: wvmbark |
last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months.
Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
|
by: dfw1417 |
last post by:
I have used a query and report filter to return records related to a specific account id. I want to print a report including only the latest 6 records out of the set returned by the record filter. I have set the sort order for the report on descending thus returning the latest records at the top of the report. However since there are well over 6 records related to the account number the report contains all the related records.
I have been...
|
by: Dave |
last post by:
On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.
I've tried using a macro with the code:
Macro Name: cmdGenerateReport : On Click
Action:...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |