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 Replies


ADVERTISEMENT

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

Queries :: Duplicate Count From Group By Query

Jul 17, 2014

I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:

SELECT Count([summary].Key) AS CountKey
FROM Summary, lookup
WHERE ([Region])) Between [Region 1] And [Region 2]));

View 4 Replies View Related

Queries :: Running Count Over Group In Query

Feb 2, 2015

I have an access query named "leaveapp" and I want a running count as below:

EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1

and i need a column in the right with running count like below

EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1

View 3 Replies View Related

Queries :: Select Count And Group Query

Sep 19, 2014

I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".

Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.

only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.

View 14 Replies View Related

Queries :: Running Count Over Group In Query

May 11, 2014

I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..

So far this thread has been very useful: [URL] ....

It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).

In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").

The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])

So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.

Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.

View 5 Replies View Related

Queries :: Running Count In A Query By Group - Error In Ranking Column Result

Mar 16, 2013

Query is based on 1 table" tblTimeCnv_AgeGroups

Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4

See attachment

My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])

In the Ranking column the result is: #error in the first 3 rows then zeroes

View 3 Replies View Related

Queries :: Query To Count Number Of Different Records?

May 28, 2014

If I have a table called "Calls" with an autonumber and another field with names for stores like this:

1 Hobby Mart
2 Peter's Store
3 Hobby lobby
4 Hobby Mart
5 Peter's Store

How can I build a query to count the number of different store names?

For example, in this case I need the query to return the number 3.

View 7 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 :: Query Between Dates But With Count Grouped By Model Number?

Mar 18, 2013

I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.

View 14 Replies View Related

Queries :: Run A Query That Count Number Of Digits In Each Field / How Many Of Each Exist

May 8, 2013

The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist

Ie

4 digits 3412
5 digits 5000

View 4 Replies View Related

Queries :: Auto Number In Append Query Every Time

Oct 19, 2013

I have a sample database created by self. It has a table name"List_Member"

I am using delete query to delete all data in that table and append query for append new data from master table name"Allmember

When i append data to "List_member", it should start with S.No.1,every time

View 8 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

Queries :: Running Count Within Group

Sep 15, 2014

I have data like so:

Code:
SubjectID VisitID
1 5
1 5
2 7
2 7
2 9
2 9
etc

And need to return a running count for the number of visits per subject, so:

Code:
SubjectID VisitID VisitCount
1 5 1
1 5 1
2 7 1
2 7 1
2 9 2
2 9 2
etc.

I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:

VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])

View 4 Replies View Related

Queries :: One To Many Table Group And Count

Jul 6, 2013

I have 2 tables. tblOrders and tblOrdersItems. tblOrdersItems is the child and tbl Orders is the parent. the linking field is OrderID.

I am wanting to count all the OrderID in tblOrders. the criteria i need is EmployeeListID which is stored in tblOrdersItems.

When I put these tables into query design, add the criteria, grouping(see below)

Code:
SELECT tblOrders.OrderID
FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeList.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;

I get (record count at bottom of query screen) 87 records. then i add a field to give me the count so i can then use it i get many more.

How to get the count working. i have uploaded the relevant tbl and my attempted query.

View 9 Replies View Related

Queries :: Percentage Of A Count By Group

May 6, 2015

I have a very simple query which brings back a count of records. I would like to add a percentage column to this which shows a percentage by ClinicCode. I've attached some sample data and what the inteded outcome should be.

View 1 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

Forms :: Count Number Of Sales In Set Time Period

Oct 23, 2013

I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.

View 1 Replies View Related

Queries :: Group And Count On Multiple Criteria

Jun 4, 2015

I have a table similar to the following

area q30 q40 q60
A 2 1 3
B 1 1 3
A 2 1 3
A 1 1 3
C 1 1 3
C 1 1 3
A 1 1 3
A 1 1 3
B 1 1 3
C 1 2 3
A 1 1 3
B 1 2 3

Grouping by the area and using the criteria

(Q30 = 1 AND Q40 = 1 AND (Q60 = 1 OR 2 OR 3))

I would like to see a table similar to the one below counting those which fulfils the criteria mentioned above

Area COUNT
A 4
B 2
C 2

View 2 Replies View Related

Queries :: Count Unique Values Without GROUP BY

Sep 15, 2013

I am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.

SELECT t1.*
FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date])
WHERE t2.OverrideDescription IS NULL;

However I also need to be able to do a unique count of the [OverrideDescription] field.

I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".

Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.

View 1 Replies View Related

Reports :: Count What Is Sorted - Number Of Time Any Item Appears

Feb 28, 2014

I have a report that i can order through buttons on report or openargs. Is it possible to get a field that would count the number of time an item appears i.e., if I sort it by employee, it would count how many times each employee is on the report, if i sort it by TypeOfMalfunction, it would count how many times each appears.

View 4 Replies View Related

Queries :: Query / Count And Display Based On Date?

Dec 18, 2014

I am trying to run query on a months worth of dates, have it count based on each day and then display the date and the number?

Table:

field1 - field2 - field 3 - Date

I can run a query one day at a time but would like to run it for the month and get this

12/01/2014 - 15
12/02/2014 - 32
12/03/2014 - 0
12/04/2014 - 12

View 6 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 :: 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

SQL Query, Count And Group

Sep 6, 2005

Hi,
i am new using SQL statements,

i have a simple Table something like this from a test station:
--------------------------------
Serial_Number | Status |
--------------------------------
A_____________Pass
B_____________Fail
C_____________Pass
A_____________Fail
B_____________Fail
A_____________Pass

and i need to get a query that separate 2 colums from the Status and count the records for each Serial_Number, some thing like this,

----------------------------------------
Serial_Number | Pass | Fail |
---------------------------------------
A_____________2______1
B_____________0______ 2
C_____________1______ 0
----------------------------------------

does anyone has an idea how to do this in Access?

View 1 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







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