Hi all,
I have a table with a date/time and a name. If I have a record for example with John as the name and the time is 12:00, can I replace any further instances of John with N/A for the next hour? All I need is the 1st instance of each name for the following hour. I can then filter by excluding N/A to shrink the amount of records in the query.
12:00 John
12:01 Simon
12:03 John (needs to be replaced by N/A)
12:04 John (needs to be replaced by N/A)
12:05 Jack
13:03 John (This can remain as more than 1 hour since the previous instance of the name)
13:05 John (needs to be replaced by N/A as less than 1 hour since the one at 13:03)
The above query would then be 4 records in total instead of 7.
All I have done so far is being able to filter names that are immediately afterwards but not if there any record in the middle
MyName: IIf(DLookUp("[Name1]","Name_Table", "[ID]=" & [ID]-1)=[Name1],"N/A",[Name1])
This works ok but it obviously does not filter all the records I need. Would appreciate if anyone has any suggestions if this is possible.
Thanks in advance.
Gareth
15 1412 twinnyfo 3,653
Recognized Expert Moderator Specialist
Gareth,
It perhaps would be helpful if we understood what you are trying to do in the bigger scheme of things. I think there are ways of doing what you want to do, but there may be better ways of doing this if we understood why you needed such a grouping. There may be better ways of doing what you are trying to do, besides changing the names to "N/A".
Thanks for replying.
Its essentially to do with Overtime. If someone has been called for a problem, it is classed as a callout. If they get a few more calls within an hour of this initial call, then it is classed as the same problem/callout. If any further calls are received after an hour, the process starts again and the first of these new calls is classed as a callout.
The hour figure is not set in stone, however once the functionality works, this figure can always be changed.
Thanks
twinnyfo 3,653
Recognized Expert Moderator Specialist
One method for dealing with this situation, might be to check the status of the technician's most recent call out and see if it has been more than an hour since. If not, then don't add a new record. However, from a management standpoint, I would think you would want to keep track of the number of calls, as well as the actual time.
I have some ideas on how to work this, but I would also need to know how this information is going to be used, such as, is it just going to be printed on a report. Do you need to know about the multiple calls, but just calcluate pay based on the time span? The reason I ask, is that there may be value in maintaining the name of the technician in your Table, but identifying the records you want to work from in a different way. Remember, by changing the name to "N/A", in your example, they are all "John", but could just as well be Jack or Simon.
Forgive me for asking more questions, but I want to make sure we guide you down a good path, not just one that will get you to a solution.
I appreciate you taking the time to look into this. All the calls are kept in the master table so for the purpose of this query, they can be filtered out completely. For every name, whether its John or Simon etc, the same rule applies, only display the first callout, filter the next hour's worth out and then start the process again.
Thanks
twinnyfo 3,653
Recognized Expert Moderator Specialist
Gareth,
This will do what you want it to do.... My assumptions were a Table namees tblCallOutHisto ry with two fields: TimeStamp And Technician. - Public Function UpdateCallOuts()
-
On Error GoTo EH:
-
Dim db As Database
-
Dim rstNames As Recordset
-
Dim rstCallOuts As Recordset
-
Dim strSQL As String
-
Dim dtmFirstCallout As Date
-
Set db = CurrentDb()
-
strSQL = "SELECT Technician " & _
-
"FROM tblCallOutHistory " & _
-
"GROUP BY Technician " & _
-
"HAVING Technician <> 'N/A';"
-
Set rstNames = db.OpenRecordset(strSQL, dbOpenDynaset)
-
If Not rstNames.RecordCount = 0 Then
-
rstNames.MoveFirst
-
Do While Not rstNames.EOF
-
strSQL = "SELECT * " & _
-
"FROM tblCallOutHistory " & _
-
"WHERE Technician = '" & rstNames!Technician & "' " & _
-
"ORDER BY TimeStamp;"
-
Set rstCallOuts = db.OpenRecordset(strSQL, dbOpenDynaset)
-
If Not rstCallOuts.RecordCount = 0 Then
-
rstCallOuts.MoveFirst
-
dtmFirstCallout = rstCallOuts!TimeStamp
-
rstCallOuts.MoveNext
-
Do While Not rstCallOuts.EOF
-
If DateAdd("n", -60, rstCallOuts!TimeStamp) > dtmFirstCallout Then
-
dtmFirstCallout = rstCallOuts!TimeStamp
-
Else
-
With rstCallOuts
-
.Edit
-
!Technician = "N/A"
-
.Update
-
End With
-
End If
-
rstCallOuts.MoveNext
-
Loop
-
End If
-
rstNames.MoveNext
-
Loop
-
End If
-
Exit Function
-
EH:
-
MsgBox Err.Number & ": " & Err.Description
-
Exit Function
-
End Function
I made this a public function so I could test it. If you would rather delete the call outs that are all within one hour of the first call out, then replace lines 30-34 with the following: - With rstCallOuts
-
.Delete
-
End With
Some things to think about : While this code will work and do what you want it to do, you should be aware that this will cycle through all records in your table, every time you want to check the call out times. This is not a big deal if you only have a few records, but if you keep records for several years, this could become a burden. Then you might want to add a new field, perhaps "Verified" as a Yes/No Field which would get updated when the code is run, then you change the Select Statement to filter out any verified records, then it only cycles through the records it needs to.
Second, if your DB is properly normalized, which is should be if it is not already, then remember that the SELECT Statements referring to the technician should be pointing to an index in another table (tblTechnicians ). This is a good practice to get into, especially in the long run as you continue to develop and expand your project (we always do....). For some initial details about DB normalization, look here.
Hope this helps, but keep in mind, there may be better ways to manage what you are trying to do. However, based on what you want and how you want to do it, this solution may be helpful.
Rabbit 12,516
Recognized Expert Moderator MVP
Here is a SQL only solution. It was created in SQL Server but is portable to Access. - SELECT
-
uname,
-
MIN(dt) AS dt
-
-
FROM
-
(
-
SELECT
-
t1.uname,
-
t1.dt,
-
MIN(t2.dt) AS mdt
-
-
FROM
-
#t t1
-
-
LEFT JOIN #t t2
-
ON t1.uname = t2.uname AND
-
t1.dt > t2.dt
-
-
WHERE
-
DATEDIFF(MINUTE, t2.dt, t1.dt) >= 60 OR
-
t2.dt IS NULL
-
-
GROUP BY
-
t1.uname,
-
t1.dt
-
) t
-
-
GROUP BY
-
uname,
-
mdt
-
-
ORDER BY
-
dt
twinnyfo 3,653
Recognized Expert Moderator Specialist
So, Rabbit, as usual, you amaze me! Could you help us mere mortals understand the nature of your SQL statement, particularly as it pertains to the "t", "#t", "t1" and "t2" stuff?
I think, I kinda mostly understand the rest of your solution, but it is meaningless without understanding how you got there. Obviously the aliens abducted you several years ago and imparted some special knowledge to you....
;-)
Rabbit 12,516
Recognized Expert Moderator MVP
Don't be amazed just yet lol. I just did some more testing and realized it only works with the given dataset. It stops working as soon as you add in another hour.
Thats brilliant thanks but for some reason I am getting no results back at the moment. Is it to be used in the criteria for the Technician field as in:
WHERE (((tblCallOuts. technician)=Upd ateCallOuts()))
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: CrystalDBA |
last post by:
I am using SQL Server 2000. I need to query my database for all the
contracts that came in during a certain time frame (user is prompted
for reportingperiodid).
Table - Periods
Fields - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime
|
by: cjm |
last post by:
I need to group records and assign a setid to the group. I have a
table with data that looks like this
ColA ColB
94015 01065
94016 01065
94015 01085
94015 01086
33383 00912
32601 00912
|
by: Dave |
last post by:
Hi
I am hoping someone might be able to help me out with this.
I am writing a helpdesk system which records agents logging in and out
of the system.
I need to write a stored procedure which will show the peak number of
agents logged in concurrently during a specified time period. Within
the time period, the person viewing the report...
|
by: MLH |
last post by:
I have a challenge...
In a table I call tblStuff4Letters with 3 fields:
, and . I have
a report called rptOutboundCorrespondence that feeds
off this table. Sorting & grouping is turned on in
that report - sorted first on (ascending)
then on (ascending). Of course,
there are more supporting fields in the table. The
above 3 are the...
|
by: CH |
last post by:
Hi,
I recently encountered a problem while trying to create a report
grouped by certain time frames. However, Access only allows grouping in
Minutes or Hours. For example, the first time frame is from 5:30am to
6:20am, if I have record 1 with time = 5:30am, record 2 with time =
6:00am and record 3 with time = 6:30am, the report will group...
| |
by: craig.buchinski |
last post by:
Ok, i have a Access Database (which is used with my ESRI Arcmap) and i
have written a query for a report to pull out our streetlights and
group them by billing wattage. The problem is that it is counting the
number of lights and summing them like i have asked, but in some cases
there are more then one row with a different sum but the same...
|
by: dianatokatlidis |
last post by:
Hello. I have 2 separate date fields in a query. One is called Effective_Date and the other is called Expiry_Date.
I want to compare the time period between these 2 fields and select any records that fall in the month of January-2008.
For example if the Effective_Date = Dec 1, 2007 and Expiry_Date = Feb 5, 2008, then I would keep this...
|
by: Charming12 |
last post by:
Hi All,
I am facing quite a unique problem here with DayLight saving time.
I know Windows handles Daylight saving time internally to sync PC Clock .
But now i need to pass the Information of Daylight Saving period i.e, From When To When it is applied .
For e.g. In US it is From 2nd Sunday in March to 1st Sunday in November.
I need to...
|
by: sasasasa |
last post by:
How can I loop through each date in a given time period so that I can add those dates as a column name in a table.
I want to create datatable with the person's name and hours he worked in a given time period. The user will select the dates and I want to show each day/date in that table as a column name. I have no idea how to do it. Please help.
|
by: jdsbuddy |
last post by:
I'm an intermediate user of Access with a little SQL knowledge. I have a query I need and I'm not sure how to create it.
I've one field that has 3 choices ... Approved, Denied, Pending. The query I need will need to pull the Approved & Denied for a specific time period. This is prety easy and I have the query doing this already.
The...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |