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.

View Replies


ADVERTISEMENT

Queries :: Count Number Of Tasks - Group By Date / Time In A Query

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

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 4 Replies View Related

Queries :: Getting Date / Time Range - Date And Time Are Separate Fields

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

Display Shift Based On Time

Nov 5, 2004

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?

Thanks so much for all your help
Dave

View 1 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

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

Queries :: How To Group Rankings By Date

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

Queries :: MAX Date Of Records - Add Another Field But Don't Want It To Group On It

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

Queries :: Group Results By Criteria Date Range

Sep 23, 2014

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.

View 4 Replies View Related

Queries :: Date Field With Time - Query For Date Only And Get All Records

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

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

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

Queries :: Extract Max Date And Time - How To Get Corresponding ID

Jan 2, 2014

I have a (simplified) table with

ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%

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.

View 5 Replies View Related

Queries :: Date / Time Search

Oct 24, 2013

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.

View 2 Replies View Related

Queries :: MS Query With Date And Time

May 20, 2014

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

View 3 Replies View Related

Queries :: Date Time Calculation From Strings

Oct 9, 2014

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!

View 2 Replies View Related

Queries :: Date And Time Range For A Report

Jan 10, 2014

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

[code]....

View 3 Replies View Related

Queries :: Append Time Onto Short Date

May 14, 2013

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.

View 2 Replies View Related

Queries :: Change Format Of Date / Time?

Aug 5, 2013

I am struggling to change the format of 2013-07-29 11:33:03.6160000 to show dd/mm/yyyy

View 4 Replies View Related

Queries :: Display Date And Time User Log On And Log Off

May 21, 2015

I would like to make a query that count simultaneous connection.

Here is my tables
Users (ID_User, Name)
Date_logon (Id_on, Date_logon, time_logon, Num_user#)
Date_logoff (Id_logoff, Date_logoff, time_logoff, Num_user#)

Here is a part of a query that display users date and time of log on and log off.

Users Date_logon Time_logon Date_logoff Time_logoff
Utilisateur1 13/05/2015 17:38:42 13/05/2015 18:52:15
Utilisateur2 13/05/2015 17:12:21 13/05/2015 17:30:24
Utilisateur3 13/05/2015 16:59:08 13/05/2015 17:16:06
Utilisateur4 13/05/2015 16:54:14 13/05/2015 18:32:13

Then I put that result in a pivot table in Excel. In line we have time logon by date and in column we have time logoff by date.

Here is the issue : In Excel for the 4 lines above, it counts 2 logon at 4PM and 2 logon at 5PM. We should have 4.

View 14 Replies View Related

Queries :: Display Date / Time Based On Weekday

Jul 7, 2015

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.

View 7 Replies View Related

Queries :: Finding Records In A Query Where Date / Time Has Been Used?

Aug 15, 2013

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.

View 5 Replies View Related

Queries :: Convert EST To BST - Using SWITCH To Return A Date / Time

Nov 14, 2014

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?...)

View 1 Replies View Related

Queries :: Categorize Time Between Shipping And Repair Date

Apr 2, 2013

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.

View 1 Replies View Related

Queries :: Changing Date / Time Format Using Query

Jun 26, 2013

I am having difficulties changing a date/time field like '22/04/2013 09:14' to make it look like '2013-04-22 09:14' using a query.

View 3 Replies View Related

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

Oct 26, 2014

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.

View 4 Replies View Related

Queries :: How To Get A List Of ALL Equipment Showing Most Recent Date And Time

Mar 23, 2013

I've got three tables:

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.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved