Queries :: Group Overnight Shift Time Together For One Date
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.
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?
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];
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.
I'm making a dispatch call database that will be sorted at the end of the month based on call types and which shift that call was originated on. Our shifts are 6a-2p, 2p-10p, 10p-6a.
I am figuring that the time would be converted to minutes and then the display would be figured based on which times the minutes occured between. IE: the call origintaed at 0100hrs = 60 minutes. It would figure that 60minutes translates to "Night Shift".
How would I convert time into minutes and how would the if/then be established?
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"
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:
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;
Below is a simplified example of what I'm trying to achieve with a parameter query.
The source table for the query contains two fields:
Reading_Date (short date) and Use_value (integer)
The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:
SELECT Sum(Table1.Use_Value) AS SumOfUse_Value FROM Table1 WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));
This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.
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.
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
I try to find the ID corresponding to the maximum date+time grouped by Machine. In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine FROM tbl_TexControl GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
I have a query that contains a complete_date including time (this is automatically datestamped when the user presses a button)At the end of the day, I want to show how many records where processed that day but cannot figure out how to display records between 06:00am and 08:00pm on todays date.
Also, the same applies to selecting a range of dates (eg. the full week), How do I display the records that fall between Monday - Friday including the time.
I have a table with the following records date/time,id,barcode.id is related to another table to retrieve fname and lname and barcode is related to another table to retrieve packageid
so my query looks like date/time,fname,lname,packageid
what i need is the ability to retrieve all records for yesterday from 00:01 to 23:59 ..im sure there will be an easy way fo doing this - i also need the ability to change 23:59 to possibly 02:00 into the following day for longer shifts
I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:
datetime2 - datetime1.
I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!
I have operators that enter equipment downtime information into the database. I need to create a report that pulls all records that have a start date and time based on a start date (field name FromDate on form) at 7:00 AM to an end date (field name ToDate on form) at 6:59 AM and shows the total duration in hours for each record.I'm having difficulty getting my query to display the correct results for the date and time range. Duration calculations work perfectly.Here is my main table only using fields pertinent to this post:
Field Name Data Type Description Exception_Number AutoNumber Primary Key ExceptStartDate Date/Time Start Date formatted as Short Date ExceptStartTime Date/Time Start Time formatted as Medium Time ExceptEndDate Date/Time End Date formatted as Short Date ExceptEndTime Date/Time End Time formatted as Medium Time
I have a totals query that displays the sum of what products we ship each day. A process in the system automatically assigns the date/time to a ShippingDate field when an item is marked as despatched.
The totals query allows users to view what products are shipped between a period specified by the user.
The problem I am getting is that when the user inputs dates into fields [txtStart] and [txtEnd], the query fires up but will not display any records as a start time 00:00:00 and end time 23:59:59 was not input.
I don't want users to have to do this but cannot think of a way around it. My criteria code in the query for the shipping dates is
Code: Between [Forms]![Switchboard]![txtStart] And [Forms]![Switchboard]![txtEnd]
Can I append the start / end time onto the code above somehow or is there another method I can utilise that is probably so glaringly obvious I have missed.
Usually a query allows me manipulate a field property sheet but in this case there is nothing obvious to select like format.
I have a query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
Trying to import some data from a linked Excel spreadsheet into a local table. One of the fields is a Date/Time type and is recorded in EST (Eastern Standard Time). I want to keep this field for posterity but also add a separate field with the corresponding time as per BST
For clarity, daylight savings time comes into effect this year on 26th Oct in the UK and 2nd Nov in the US. So generally, there is a 5 hour difference between the two time zones, apart from the period between these two dates, when it is only 4 hours.Here is my query - I am using a SWITCH function to create the BST field
Code: INSERT INTO tblTransactions SELECT ltbPayments.ID AS Reference, ltbPayments.VALUEDATE AS ValueDate, ltbPayments.LOCALAMOUNT AS Amount, ltbPayments.USDAMOUNT AS AmountUSD, tblAccounts.AccountID AS AccountID, ltbPayments.TRANSACTIONTIME AS TransactionTimeEST, SWITCH(DateValue(ltbPayments.TRANSACTIONTIME) < DateSerial(2014,10,26) Or DateValue(ltbPayments.TRANSACTIONTIME) >= DateSerial(2014,11,2),
[code]....
So - how do I explicitly specify the output of the SWITCH function to be in Date/Time format (I presume, by default, it's returning Text, which contradicts the table properties of tblTransactions & the TransactionTimeBST field?...)
I have a repairs db that records [serial number] and [date in for repair], and other info.
I've been asked to make a report to summarize the number of units [serial number] that come back for repair by time interval between [ship date] and the first [date in for repair].
the [serial number] and [ship date] are in a linked excel file table which contains unique [serial number] entries.
The [serial number] and [date in for repair] are in the access table, there may be multiple [serial number]s entered with different [date in for repair] as in repaired multiple times.
1st query: I want to list all the [serial numbers] in the access table with only the earliest [date in for repair] returned.
2nd query: then I need to compare the 1st query results list of [serial numbers] and [date in for repair]to the excel table list of [serial numbers] and [ship dates],
I've found the =DateDiff('d'[ship date],[date in for repair])
I haven't done a query with two data sources before and not sure how to go about getting the DateDiff for each serial number
Once I get results from the DateDiff, for each serial number, I am comfortable handling the Sum(IIf statements to summarize the results and do the serial number range select criteria in the form.
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
Code: tblequipment equipmentid equipmentnumber (user defined ID)
tblrentals rentalid rentaldate rentaltime fromparty (c for customer, e for employee, o for other) frompartyid (foriegn key to either customer, employee or other) toparty (same as from) topartyid (same as from)
tblrentaldetails detailid rentalid equipmentid
How can I get a list of ALL equipment showing the most recent date and time, also showing the respective toparty and topartyid? I can get it fairly easily, except for including toparty and topartyid.
