Group By More Than 1 Hour
Jul 25, 2005
i have a table of time and dates and a value after it inn which i want to do a query which groups these values by every 3 hours e.g
my table
12/02/2001 00:00 6
12/02/2001 01:00 4
12/02/2001 02:00 8
12/02/2001 03:00 12
12/02/2001 04:00 7
12/02/2001 05:00 6
i want the query to do this
12/02/2001 02:00 18
12/02/2001 05:00 25
i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged
cheers
View Replies
ADVERTISEMENT
Apr 24, 2006
is there a way to count - in one query - the number of records that fall between specific times:
Time CountOfRecords
0000-0100 7
0100-0200 2
etc
2300-2400 4or do i have to do separate grouping queries then separate counting queries for every hour of the day? a crosstab? something else perhaps? feels like i'm missing something obvious (i hope...).
View 2 Replies
View Related
Feb 11, 2008
Hi, I have a main form with a subform inside it. The subform contains two records but only one is shown during form view, is there any way to show both records at the same time?
I am using continuous forms view for my subforms as i have to.
View 3 Replies
View Related
Jul 1, 2006
I've searched the forum and can't quite find what I need so I'm asking for further guidance form all you Access masters out there!
I have a field 'Time' in the format of 'dd/mm/yyyy hh:mm' which has a default value of =DMax("Time ","[Monitoring]")+1.
This increments 1 on the 'dd' bit when each new record is created, however what I want is for the hour (hh) bit to increment 1 on each new record.
Not sure if I'm being niave but I thought it was some sort of formatting issue, so I've dabbled with a few but to little effect unfortunately.
View 6 Replies
View Related
Jan 2, 2006
Hello Everybody...
I have a Question.
How do I convert a number (26) to 26:00 Hours.
The thing is.
I have a database that calculates the hours of overtime.
for example.
I work from 7:00 to 19:00.
Then I get 12:00 hours of total time.
The above is no problem.
But no I want to deduct the number 2 from 12:00 hours.
I hope to hear from u guys soon
View 2 Replies
View Related
Jun 14, 2007
Hi,
ive searched the forums for this but cant find anywhere.
how can i add one hour onto a time stamp please.
many thanks:confused:
View 4 Replies
View Related
Nov 16, 2005
Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?
For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.
Any help would be appreciated.
Paul.
View 2 Replies
View Related
Jun 3, 2006
Hello!!!
I've a trouble:
I have an Access DataBase with two tables:
1--Workers with fields (ID, Name and $/hour)
2--Marks with fields(ID, WorkerID, Datetime of mark)
Well, I need to generate a report with the following structure:
Name Worker / Number of days worked / Total Cost in $
---------------------------------------------------------------------------
Worker 1 / 19 / 13000$
Worker 2 / 20 / 21354$
Worker 3 / 12 / 12000$
...
Example of Workers Table Data:
ID / Name / $/hour
-------------------------------------------------------------------------
21367 / Worker 1 / 21,34$
54883 / Worker 2 / 54,87$
87364 / Worker 3 / 5,38$
Example of Marks Table Data:
ID / IDWorker / DateTime-Mark
-------------------------------------------------------------------------
6754-54 / 21267 / 19/05/2006 8:05:32
4327-12 / 54883 / 19/05/2006 8:07:12
7645-98 / 87364 / 19/05/2006 8:13:54
5432-99 / 21267 / 19/05/2006 14:05:32
7584-23 / 54883 / 19/05/2006 14:07:12
9485-23 / 87364 / 19/05/2006 14:13:54
... / 21267 / 19/05/2006 16:05:32
... / 54883 / 19/05/2006 16:07:12
... / 87364 / 19/05/2006 16:13:54
... / 21267 / 19/05/2006 20:05:32
... / 54883 / 19/05/2006 20:07:12
... / 87364 / 19/05/2006 20:13:54
... / 21267 / 20/05/2006 8:05:32
... / 54883 / 20/05/2006 8:07:12
... / 87364 / 20/05/2006 8:13:54
... / 21267 / 20/05/2006 14:05:32
... / 54883 / 20/05/2006 14:07:12
... / 87364 / 20/05/2006 14:13:54
... / 21267 / 20/05/2006 16:05:32
... / 54883 / 20/05/2006 16:07:12
... / 87364 / 20/05/2006 16:13:54
... / 21267 / 20/05/2006 20:05:32
... / 54883 / 20/05/2006 20:07:12
... / 87364 / 20/05/2006 20:13:54
The condition to increment number of days is:
YEAR(date-mark) != YEAR(date-mark) OR
MONTH(date-mark) != MONTH(date-mark) OR
DAY(date-mark) != DAY(date-mark)
for example:
1)
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
19/05/1982...time-mark // Worker 1
-----------------------------------> 1 days worked
2)
20/05/1982...time-mark // Worker 1
20/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
21/05/1982...time-mark // Worker 1
-----------------------------------> 2 days worked
Do you understand it?
Well, I've tried several SQL sentences, but I can't make the definitive sentence. Can you help me, please?
Note --> each Mark-Datetime represents an instant of time!!!
View 1 Replies
View Related
Mar 31, 2008
Okay,
Missing something simple here and can't figure it out. I want to display a time in military 24-hour format but can't get it to show the first "0" between the times 0000 - 0959.
Current statement is:
datTripTime = Format(datArr, "HH:mm")
What am I missing please???
Thanks
View 3 Replies
View Related
Jul 28, 2006
I have a very large dataset with over 11 million data records in three different databases! Currently the data is collected on average every minute, which is much too detailed! I would like to pull data for every site on the hour, or somewhere near the hour as some data was collected at different time intervals. (this latter part is a completely different issue, I would be happy just to get my first query working, then deal with data not collected exactly on the hour)
I wrote a query and tested it out on a very small portion of the data. It seems to be working, but for some reason won't pull data for the 2 pm, 5 pm, or 8 pm hours...even though the data is there in the table. Any reason why this may be? Even if I simplify the query to only pull out those hours, and not all 24 at once, the data still doesn't get selected. I really can't think of why it's not working. Any suggestions?
I've currently set up the Criteria in the time column of the query to look like this:
#1:00:00 AM# Or #2:00:00 AM# Or #3:00:00 AM# Or #4:00:00 AM# Or #5:00:00 AM# Or #6:00:00 AM# Or #7:00:00 AM# Or #8:00:00 AM# Or #9:00:00 AM# Or #10:00:00 AM# Or #11:00:00 AM# Or #12:00:00 PM# Or #1:00:00 PM# Or #2:00:00 PM# Or #3:00:00 PM# Or #4:00:00 PM# Or #5:00:00 PM# Or #6:00:00 PM# Or #7:00:00 PM# Or #8:00:00 PM# Or #9:00:00 PM# Or #10:00:00 PM# Or #11:00:00 PM# Or #12:00:00 AM#
Is there a better way to go about this?
Thanks!
Ok, I am still interested in knowing why the above query wasn't working should anyone know. But while thinking of how to deal with my other data not on the hour...seemed to have made a few queries that do the trick! Access is great...but sometimes very slow to get what you want done when you're still learning!
View 1 Replies
View Related
Jul 16, 2005
MY QUESTION:
I’m using Access 2002.
I’m trying to create a query that will COUNT the number of values within 1 hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that I need to have every hour bin represented whether there is any value in it or not. I believe that I need to create a separate table of the bins that I want (include bin start-value & stop-values) and then bring this table into my query and join it to the original table.
I was able to construct the make table query below to COUNT the values in hour bins for which there were records, but I also need rows for all of the zero values;
SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS [TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins]
FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter
GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI Detections.Date]), [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time])
HAVING ((([All NWHI Detections].Species)="tiger"))
ORDER BY [All NWHI Detections].Transmitter;
Q1? Creating the hour bins table
This table will be very large because it will need to have 4 years worth of hour bins for each transmitter (N=15) and location (N=12). This results in 6,307,200 hour bins! How can I write a query to create this table, or is there a better way of doing this?
Q2? Bringing the hour bins table into the main query.
How do I include the hour bins table in the main query to get my final result.
Any help would be greatly appreciated! Thank you.
View 1 Replies
View Related
Sep 18, 2006
Hi
I have made a create-tabel query that I want to run every 2. hour. How can I set Access up to do this automatically?
eroness
View 1 Replies
View Related
Jul 13, 2015
I need to know about "Need to query result from access2007 find only "one maximum number" in hour per day every cell ID"
How I use sql command?
See my file at ...
View 5 Replies
View Related
May 23, 2014
I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.
View 3 Replies
View Related
Jul 22, 2007
I have a form in continuous forms mode...
Each entry is something the user is supposed to check on once and hour and update.
I have a "Last updated" field.
Now() goes into that field every time the record is updated.
I have a querry that will show the users records that are over an hour old.
How can I get Access to pop-up a window or something to alert the user they need to look at something? It would be really nice if Aspect would start blinking in the task bar or something.
Thanks!
View 5 Replies
View Related
Jan 8, 2014
What is wrong with this query?
=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))
What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.
View 11 Replies
View Related
Sep 29, 2014
I am trying to display on a form the average number of lines/records per hour that a person is adding to the database.
Trying to work with Dfirst and DLast I'd subtract the time of the most recent record from the time of the first record entered and then divide the number of records to display the average per hour in a text box.
View 14 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
Jul 25, 2013
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
View 1 Replies
View Related
Mar 12, 2007
I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?
p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???
View 5 Replies
View Related
Mar 22, 2008
I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...
I think maybe I should upload the database, let me know.
Here are the names:
Table name: PPM Data
Fields:
Company
PPM Month
PPM Amount
There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.
I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.
View 6 Replies
View Related
Sep 13, 2005
Hi,
i have a combo box that query a tabel to get the date's i chose from but she repeat the samedate if i have a 2 records with date 12-9-2005 i see 2 in the combo box ho i can join them to see only one date fron them
View 1 Replies
View Related
Jan 18, 2006
I have a field on a table that is a memo field. I am trying to write a query to include this field but get the "Can't group on memo or OLE object. Any ideas on why and how can I query with this memo field included?
View 2 Replies
View Related
Feb 11, 2006
I'm trying to view the last two valuations for each property so that they can be compared. I can pull out the last valuation for each property and can pull out the top two records but not for each property, just shows me the top 2 of all records.
Any suggestions?
View 5 Replies
View Related
Feb 28, 2005
Hi, I have just made my maiden voyage on the good ship "Security Wizard" and the water has been a little rough but as yet I still have my sea-legs intact!
Slight problem though... I want users to be able to add data to a table but not to be able to view the whole table if they try and open it in the database window. Now I know reasonable measures would just hide the DB window but I have the threat of Data Protection Auditors looming over me becuase management "forgot" to tell me security was required and it has already been breached before I used the wizard.
Anyway, I did some research on here and I found a method whereby I can set the table so only I have permission to view/add data, then I can get users to add data via a query with permissions set to Owners.
I'm really hoping that someone will come to my rescue and tell me that there's a much easier way than that...
Please....
Anyone....
Thanks, Tom.
View 1 Replies
View Related
