Problem With GROUP BY [Date Time]
Aug 30, 2007
Hi there,
I have a table called tblTransactions with columns called (among others) [Date Time], Ticker, Quantity, Price and Commission. Column [Date Time] contains both a date and a time.
I want to determine the commission paid per day. If I use the following code I still get all single trades on a day as the times at which the trades were exucuted are all different. Is it possible to group by full days only without having to change my database in order to determine the daily commission paid?
SELECT T.[Date Time], sum(Commission) AS TotalCommission
tblTransactions AS T
WHERE T.[Date Time]<=#04/17/07#
GROUP BY T.[Date Time];
Best regards,
JapanFreak
View Replies
ADVERTISEMENT
Oct 24, 2014
I am collecting data every hour on the hour and I would like to group this data according to Shift (A, B and C) by Date. The only problem I am having is- how can I keep the overnight shift together as one date? 10pm - 6am
I was able to do a simple query where I was able to assign an A, B, or C depending on the field with the Short Time format using nested IIF statements. From there I was able to group by shift (A, B, or C) per date. However the C Shift is split up.
Example. Can Sunday October 5th 10pm to Monday October 6th 6am be grouped as one date.
View 2 Replies
View Related
Aug 14, 2013
I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
View 7 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
Mar 13, 2014
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.
View 4 Replies
View Related
Apr 11, 2007
Hello,
I'm trying to calculate some running sum queries in preparation for some charts on a report.
I have:
tbl: Spencerdaily
fld1:datein
fld2:milesdriven
fld3:vehiclenumberdaily
I am trying to calculate the running sum of milesdriven for each year per vehicle. If I use the following I get the same set of sums repeated for each vehicle:
SELECT spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year]) AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
I tried this but it didn't work:
SELECT spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily AS vehiclealias, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year] & " AND [vehiclenumberdaily]<=" & [vehiclealias] & " ") AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
What am I doing wrong?
Thanks,
Gifford
View 2 Replies
View Related
Jun 21, 2007
I am attempting to set up a query which will perform aggregate functions on records from two fields (Speed, Volume) and group them by a time field (SensorTime). This should essentially result in several months of data being combined into a succint 24-hr time period, divided into 288 five-minute intervals.
The problem is that Access will group some of the time values together, but not others. After some detective work I discovered that many of the times that APPEAR to be equal are actually different values when carried out to the 15th decimal point. I learned that this is a result of rounding error within Excel, from whence I originally imported the data.
My question is: can I apply a ROUND function to times in the original table, thereby solving the problem in all the resulting queries (and if so, how)? Or will I need to round the time values in each individual query (this would take some time)? Or is there a better way to equalize these time values that I'm not aware of? I know Excel has a "Precision as displayed" option but I couldn't find anything similar in Access.
Eagerly awaiting advice!
-Bratlien
View 5 Replies
View Related
Oct 18, 2006
I am new to Access and would be very grateful for some adivce on setting up a query.
The data in the table below comes from a photon counter. Both fields are numbers although TIME actually represents seconds.
I need to be able to group the DATA field by arbitrary TIME periods. e.g. to divide the table up into bins of, say, 8.7 seconds length, showing the total accumulated in the DATA field for each successive period of 8.7 seconds.
The tables are about 300,000 records long.
Any help would be much appreciated.
Thanks,
TIME - DATA
285748175.864557 - 100
285748175.874556 - 100
285748175.884556 - 0
285748175.894557 - 100
285748175.904557 - 0
285748175.914556 - 200
285748175.924556 - 300
285748175.934556 - 0
View 7 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Nov 8, 2013
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
View 3 Replies
View Related
Apr 3, 2014
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
View 4 Replies
View Related
Aug 29, 2013
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
View 4 Replies
View Related
Apr 10, 2015
so i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
View 5 Replies
View Related
Mar 6, 2007
Hi,
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
Many thanks
Chris
View 2 Replies
View Related
Mar 19, 2014
I have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
data looks like this:
8/22/13 4:23 PM
1/29/14 12:21 PM
1/28/14 3:27 PM
8/26/13 4:27 PM
[code]....
this is what I have come up with to extract the time component of data set so that I can then later, sort it by the time in a query.
JustTime: TimeValue([YourField])
JustTime: ("hh:mm",([DateTaken])) and or ("hh:mm",[DateTaken])
I get either invalid operator or invalid syntax errors trying both of these.
View 5 Replies
View Related
Feb 27, 2008
How do I get just 41 records, each site, plus just the most recent event for that site?
Table 1, Sites (41 Records)
Table 2, Event (42 Records, 1 event for 40 sites, plus 2 events for 1 of the sites)
Totals Query 1, Group on SiteID from Sites, Select Max Date from Event. Good Result. (I get 41 records, 1 for each site, and the site with 2 events shows only the later event)
Query 2: Pull in Query 1, multiple fields from either the Sites Table or the Event Table. Good Result (41 records)
Query 3: Pull in Query 1 and both Tables. Everytime I get 1641 records. I've tried every possible join combination, changing query 1 from Dynaset to Snapshot, etc.
In a help screen I noticed that the sample shows a join with an infinity sign at one end, but I never get that graphic with my joins. Is that an old graphic, or am I missing something?
View 4 Replies
View Related
Oct 26, 2005
I wondered if anyone new how to return the following
Table of Data
ID|ID2|Date
----------------
1 | 1 | 20/01/05
2 | 1 | 21/01/05
3 | 1 | 19/01/05
4 | 2 | 20/01/05
5 | 2 | 25/01/05
I want to return the minimum date grouped by ID2, but with the corresponding ID i.e.
ID|ID2|Date
----------------
3 | 1 | 19/01/05
4 | 2 | 20/01/05
cheers for your help...
View 4 Replies
View Related
Dec 5, 2005
hi guys,I want to group a field in my query within a certain period of time, say 1 week. (e.g. between #12/5/2005# and #12/11/2005 23:00:00#) however I need to run this query every week.so I don't want to have to change the dates every week just to run the query.Is there a way to use the system's calendar date so that the values will be input automatically (i.e. to group between today and today-6 23:00:00)?? maybe using date() function or something??:confused: heaps n heaps of thx!!:D
View 2 Replies
View Related
Mar 12, 2014
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code:
Private Sub Update_DOD()
Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset
Dim DOD As String
rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code:
CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
View 5 Replies
View Related
Apr 26, 2013
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies
View Related
Feb 18, 2014
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
View 13 Replies
View Related
Jul 5, 2005
Hi All.
In a query I grouped DATE by month Date:Format([Date],"yyyy mmm") to calculate visits in each month. My problem is: How to create dialog where user can enter date based only on YEAR and MONTH and enter in this dialog start_date and end_date?
Thanks.
View 1 Replies
View Related
Nov 25, 2014
I have used Allen Browne's suggested method of ranking to rank by the date each client settled... Now I need to group the rankings by the referring company as below... so it ranks each client referred by referral company from earliest to latest - like so:
ReferrerID___ClientID___TotalValue___BeatenBy (Ranking)
Intro1_______CatCo_____6/11/14________4
Intro1_______DogCo_____8/3/14_________1
Intro1_______FishCo_____17/5/14________3
Intro2_______AntCo_____11/1/14________2
Intro1_______PigCo______2/4/14_________2
Intro2_______BirdCo_____12/8/14________3
Intro2_______FrogCo_____6/1/14________1
My current SQL view of my query looks like this:
SELECT qryDates.ClientID,
qryDates.TotalValue,
(SELECT Count([ClientID])
AS HowMany
FROM qryDates AS Dupe
[Code] ....
View 5 Replies
View Related
Dec 11, 2006
Hi,
I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.
Any help would be greatly appreciated!!!!
Thanks
Mikeco555
View 3 Replies
View Related
Dec 9, 2014
How can I convert a Date/Time field into a Date field?
View 4 Replies
View Related
Oct 28, 2014
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator
FROM MatterContactsMade
GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator
HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator]))
ORDER BY MatterContactsMade.MatterId;
View 1 Replies
View Related
