473,811 Members | 3,026 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Count of records matching ranges in 2+ fields

8,435 Recognized Expert Expert
Hi all.

I dabble with Access, but haven't done anything in-depth for a number of years. What I want to do now is probably a fairly simple JOIN or something, but I just can't recall how to go about it. Or the right terminology to do a proper search. Hope someone can help. Oh, this is in Access 2003, by the way.

I have a table which includes start-time and end-time fields (field type is date/time). By setting up a parameter query I can easily have it prompt me for a given time (let's call it "T1"), and show the count of records which span the hour starting at that time, with criteria something like "[StartTime] >= T1 AND [EndTime] <= (T1 + 1hr)". Sorry about the syntax, but I'm not currently at the PC with Access installed and my SQL's a bit rusty.

This works OK for a single hour. But I want to list a breakdown of the records per hour. In other words, I should be able to

open a query (or whatever) and come up with something like...


Date/Time Count
01/01/2006 01:00 10
01/01/2006 02:00 28
.
.
.

Hopefully I won't need to be spoonfed - a budge in the right direction should suffice. Also, this is just for quick & dirty

use, doesn't matter if it's messy. I've actually generated another table containing records with nothing but the start and end time for each hour I'm interested in, in the hope that would help, but still can't recall (or find) how to join them up

to get my counts.

Ciao, and TIA.
Oct 11 '06 #1
7 5394
PEB
1,418 Recognized Expert Top Contributor
Hi,

To obtain a group by date and hours you need The format function like:

Expr1: Format([Date_intr],"dd/mm/yyyy\ hh")

Best regards!
Oct 14 '06 #2
Killer42
8,435 Recognized Expert Expert
Hi,
To obtain a group by date and hours you need The format function like:
Expr1: Format([Date_intr],"dd/mm/yyyy\ hh")
Best regards!
Thanks for that.

However, it doesn't quite cover the issue. Probably because I have difficulty explaining clearly what the issue is. But I need to group records by a couple of fields "centred around" a specific date/time. I have managed to get something like the sort of output I'm after, by creating a JOIN in the GUI then modifying the SQL. Here's what I've got so far. I'm still hoping someone can help improve on it, as this takes hours to run, and that's only with a relatively small sample of my data.

I think I mentioned this before, but as part of experimenting I have created a table called DateTime2 with fields StartTime and EndTime. These hold the starting and ending values for each hour. This is the first draft SQL I have used to count the records from the main table ("Log") which span any part of each hour...

SELECT DateTime2.Start Time, Count(Log.JobNu m) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON (Log.Started <= DateTime2.EndTi me
AND Log.Ended >= DateTime2.Start Time)
GROUP BY DateTime2.Start Time;

I have not yet determined whether the results generated are correct - only that it did generate results, and at first glance they look reasonable.
Oct 16 '06 #3
MMcCarthy
14,534 Recognized Expert Moderator MVP
Your problem is in your join, this should be your criteria

SELECT DateTime2.Start Time, Count(Log.JobNu m) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON <You need an ID common to both tables>
WHERE (Log.Started <= DateTime2.EndTi me
AND Log.Ended >= DateTime2.Start Time)
GROUP BY DateTime2.Start Time;


Thanks for that.

However, it doesn't quite cover the issue. Probably because I have difficulty explaining clearly what the issue is. But I need to group records by a couple of fields "centred around" a specific date/time. I have managed to get something like the sort of output I'm after, by creating a JOIN in the GUI then modifying the SQL. Here's what I've got so far. I'm still hoping someone can help improve on it, as this takes hours to run, and that's only with a relatively small sample of my data.

I think I mentioned this before, but as part of experimenting I have created a table called DateTime2 with fields StartTime and EndTime. These hold the starting and ending values for each hour. This is the first draft SQL I have used to count the records from the main table ("Log") which span any part of each hour...

SELECT DateTime2.Start Time, Count(Log.JobNu m) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON (Log.Started <= DateTime2.EndTi me
AND Log.Ended >= DateTime2.Start Time)
GROUP BY DateTime2.Start Time;

I have not yet determined whether the results generated are correct - only that it did generate results, and at first glance they look reasonable.
Oct 16 '06 #4
Killer42
8,435 Recognized Expert Expert
Your problem is in your join, this should be your criteria

SELECT DateTime2.Start Time, Count(Log.JobNu m) AS CountOfJobs
FROM Log INNER JOIN DateTime2
ON <You need an ID common to both tables>
WHERE (Log.Started <= DateTime2.EndTi me
AND Log.Ended >= DateTime2.Start Time)
GROUP BY DateTime2.Start Time;
Sorry, a common ID is not the answer, I do know that much. A JOIN may even have been a mistake in the first place. You see, the whole idea of using JOIN with the second table was just one thing I have tried to resolve the problem. It is actually a single table I'm interested in. I am trying to get a count of the number of records which have two date/time fields which span a given date/time. Or in this particular case, span a given period of one hour. In other words, entries with a start date/time and end date/time which indicate something was active during a given period of time.

I'll try to explain with an example. Let's say we're dealing with phone calls, and for each one we track the starting and ending time in two date/time fields. For each hour of the day (7:00-7:59, 8:00-8:59, etc.) I want to generate a count of the number of calls which "touched" that period. That is, they may have started or ended during that period, or even been "active" over the entire period. Given one particular time, I can easily come up with a query which will ask Access to produce a count "...WHERE (StartTime >= Entered_Time) AND (EndTime < One_Hour_Later) ;".

This is fine if I am prompting for a time and producing one count. But what I need is to generate this sort of breakdown for the entire table, which has millions of entries. (Note, obviously records will often be counted more than once, since they will run over the end of a time period).

Sorry, but I just don't know how to express the problem any more clearly.
Oct 16 '06 #5
MMcCarthy
14,534 Recognized Expert Moderator MVP
I'm assuming started and Ended are numbers (doubles)

SELECT Count(JobNum) AS CountOfJobs
FROM Log
WHERE (Started <= [Forms]![FormName]![StartTimeContro lName]
AND Ended >= [Forms]![FormName]![StartTimeContro lName])
OR (Started BETWEEN [Forms]![FormName]![StartTimeContro lName] AND [Forms]![FormName]![StartTimeContro lName] +1)
OR (Ended BETWEEN [Forms]![FormName]![StartTimeContro lName] AND [Forms]![FormName]![StartTimeContro lName] +1));

To do what you want to do it would be best to have a form for the user to enter the start time.



Sorry, a common ID is not the answer, I do know that much. A JOIN may even have been a mistake in the first place. You see, the whole idea of using JOIN with the second table was just one thing I have tried to resolve the problem. It is actually a single table I'm interested in. I am trying to get a count of the number of records which have two date/time fields which span a given date/time. Or in this particular case, span a given period of one hour. In other words, entries with a start date/time and end date/time which indicate something was active during a given period of time.

I'll try to explain with an example. Let's say we're dealing with phone calls, and for each one we track the starting and ending time in two date/time fields. For each hour of the day (7:00-7:59, 8:00-8:59, etc.) I want to generate a count of the number of calls which "touched" that period. That is, they may have started or ended during that period, or even been "active" over the entire period. Given one particular time, I can easily come up with a query which will ask Access to produce a count "...WHERE (StartTime >= Entered_Time) AND (EndTime < One_Hour_Later) ;".

This is fine if I am prompting for a time and producing one count. But what I need is to generate this sort of breakdown for the entire table, which has millions of entries. (Note, obviously records will often be counted more than once, since they will run over the end of a time period).

Sorry, but I just don't know how to express the problem any more clearly.
Oct 16 '06 #6
Killer42
8,435 Recognized Expert Expert
I'm assuming started and Ended are numbers (doubles)

SELECT Count(JobNum) AS CountOfJobs
FROM Log
WHERE (Started <= [Forms]![FormName]![StartTimeContro lName]
AND Ended >= [Forms]![FormName]![StartTimeContro lName])
OR (Started BETWEEN [Forms]![FormName]![StartTimeContro lName] AND [Forms]![FormName]![StartTimeContro lName] +1)
OR (Ended BETWEEN [Forms]![FormName]![StartTimeContro lName] AND [Forms]![FormName]![StartTimeContro lName] +1));

To do what you want to do it would be best to have a form for the user to enter the start time.
You've identified the crux of the problem. As I stated in the original post, it is very easy to do this using a form to prompt for a date/time. What I need is to extend this to a breakdown of the entire table, so that I get a count for each hour (or month, minute, whatever) for the entire period.

Oh, and I'm dealing entirely with date/time fields/variables, not doubles. But the principle is about the same, I guess.

Incidentally, I believe your logic is more complex than required. For the sake of argument, let's say we have two fields S1 and E1. We want to catch any overlap with the range S2 to E2. If I read it correctly, your logic says something like...
WHERE ( ( (S1 <= S2) AND (E1 >= S2) )
OR (S1 BETWEEN S2 AND E2)
OR (E1 BETWEEN S2 AND E2) )
This makes sense, as you are checking for anything which spans the entire range(?), or starts within it, or ends within it. However, I'd suggest one need only check...
WHERE ( (S1 <= E2) AND (E1 >= S2) )

Oh, one other thing. I realise you were just illustrating a principle, but just out of curiosity, adding 1 to a date/time value would add what, a day?
Oct 17 '06 #7
MMcCarthy
14,534 Recognized Expert Moderator MVP
Yes adding 1 to a date would add a day

Hour(#09/22/2006 10:42:27#) would return 22 (10 in 24 hour clock)

Therefore

Hour(#09/22/2006 10:42:27#)+1 would return 23

So if you use something like this

SELECT Sum(IIf(Hour([DateFieldName])=1,1,0) As CountHour1,
Sum(IIf(Hour([DateFieldName])=2,1,0) As CountHour2,

<repeat for all 24 hours>

FROM TableName;

I've used sum instead of count as it adds 1 for each valid date in each category.


You've identified the crux of the problem. As I stated in the original post, it is very easy to do this using a form to prompt for a date/time. What I need is to extend this to a breakdown of the entire table, so that I get a count for each hour (or month, minute, whatever) for the entire period.

Oh, and I'm dealing entirely with date/time fields/variables, not doubles. But the principle is about the same, I guess.

Incidentally, I believe your logic is more complex than required. For the sake of argument, let's say we have two fields S1 and E1. We want to catch any overlap with the range S2 to E2. If I read it correctly, your logic says something like...
WHERE ( ( (S1 <= S2) AND (E1 >= S2) )
OR (S1 BETWEEN S2 AND E2)
OR (E1 BETWEEN S2 AND E2) )
This makes sense, as you are checking for anything which spans the entire range(?), or starts within it, or ends within it. However, I'd suggest one need only check...
WHERE ( (S1 <= E2) AND (E1 >= S2) )

Oh, one other thing. I realise you were just illustrating a principle, but just out of curiosity, adding 1 to a date/time value would add what, a day?
Oct 17 '06 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

5
1747
by: Jonathan Edwards | last post by:
The parser library module only records source line numbers for tokens. I need a parser that records ranges of line and character locations for each AST node, so I can map back to the source. Does anyone know of such a thing? Thanks Jonathan
6
4086
by: Matt K. | last post by:
Hi there, I have a form in an Access project that contains a subform which displays the results of a query of the style "select * from where = #a certain date#". In the main part of the form the user can change the date, which will force a requery in the subform to bring up records from the date selected. My question is this... The query in the subform is a very simple one, with only three fields being returned. In the interest of...
1
2997
by: snOOp | last post by:
I am trying to combine the data from two similar tables into one query, but I need for all of the records from both tables to show up and I want the ones that have matching 'emplid' to be combined into one record showing both the 'empstatus' and 'strole' fields. The following query works, but does not combine the matching records: SELECT givenname, sn, empstatus, emplid, ssn FROM dbo_EmpData WHERE empstatus='A' UNION ALL SELECT...
2
2299
by: S0ck3t | last post by:
Please could I have some help on matching records between tables. I want to return a check (true/false) stating whether the field combination in table 1 occurs in table 2. Obviously it's easy with just one field, but two is more problematic. I have the following Table1 -------- field1
22
12499
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source=" & msDbFilename moConn.Properties("Persist Security Info") = False moConn.ConnectionString = msConnString moConn.CursorLocation = adUseClient moConn.Mode = adModeReadWrite' or using default...same result
2
4847
by: jonathan184 | last post by:
Hi I am having a problme where the results of the sql count is not matching the results of the perl script sql count. The script was working fine up till Wed last week and after that the results are different on sql and on perl and the date and times are the same, I even copy and pasted the sql query from the perl script into the sql window and got different results. Here is part of the script where i am trying to get the count...
1
1309
by: mgoodman | last post by:
I have a main form w/ two subforms in Access. Based on their parent/child fields, the subforms call up existing matching records. My problem is: when there is no matching record in my subform it automatically creates a new record and populates it with whatever is entered in the parent field on the main form. I would like the user to be able to add and edit records on the main form regarless if there is a match on the subform or not. When...
2
2770
by: dmne05974 | last post by:
As a novice in Access i am currently writing a database to track certain financial information for a non-profit organisation. As part of the funding they have to monitor age ranges and ethnic minority, this is done with 4 yes/no fields controlled on a form with Bi-state check boxes (minor, adult, ethnicminor, ethnicadult). The database stores many purchase orders for each person as more money is awarded however each person can only be...
9
4639
by: warrior2009 | last post by:
I have 2 lists (unlinked and unrelated), where one can be called the parent and the other the child with one to many relationship between parent-child. The child table is really big, over a million records. The link between the two, is a set of 3 fields that must be equal between the two. So when the two match, there can be a number of records in the child field, because it is related to the usage history of the parent record. So here is my...
0
9727
marktang
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...
0
9605
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10647
Oralloy
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10133
tracyyun
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...
0
9204
agi2029
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7669
isladogs
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...
0
6889
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();...
0
5554
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...
3
3017
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.