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.
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!
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.
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.
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.
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.
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?
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?
Sign in to post your reply or Sign up for a free account.
Similar topics |
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
|
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...
|
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...
|
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
|
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
| |
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...
|
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...
|
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...
|
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...
|
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:
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,...
| |
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...
|
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: 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...
|
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: 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...
| |