Count A Group Between Dates

Jan 25, 2006

here goes
im trying to count the number in a group between a time period. the query takes a time period from a form runs then reports who and how many heres what i get

SELECT tblEstimate.Client, Count(tblEstimate.Recieved) AS MyCount
FROM tblEstimate
GROUP BY tblEstimate.Client, tblEstimate.Recieved
HAVING (((tblEstimate.Recieved)>=[Forms]![frmEnquireFront]![EqfromDate] And (tblEstimate.Recieved)<=[Forms]![frmEnquireFront]![EnqtoDate]))
ORDER BY tblEstimate.Client;

Client MyCount

Amey Vectra Ltd 1
Amey Vectra Ltd 1
Ashworth Mairs Group1
Property Consortium1
Property Consortium1

heres what im trying to achieve

Client MyCount

Amey Vectra Ltd 2
Ashworth Mairs Group1
Property Consortium2

am i going about this the wrong way
is there an easier way
any help would be great

thanks in advance
john:confused:

View Replies

Queries :: Count Dates Between Dates In Two Tables

Jul 8, 2014

I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.

To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014

table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014

So the answer of the query would be 2,0,4.

Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2

View 2 Replies View Related

Group By Count

Mar 14, 2007

Hello

I have a problem with a SQL query I build. I have a complaints database and for a listbox I need the 5 costumers with the most complaints in the database and the total amount of complaints so far. To do so I tried the following SQL query:

SELECT TOP 5 [Costumername],Count(*) AS [Count] FROM [Complaints] WHERE [Costumername]Is Not Null GROUP BY [Count];

But I get the following error message if I try to run this query: �You tried to execute a query that does not include the specified expression �costumername� as part of an aggregate function.�

I noticed that a pie chart uses almost the same query (only sorted by name, and not the count) and this works, until I try to change that query to the one above. I hope someone can help me with this. Thanks!

View 1 Replies View Related

Count By Group

Jul 12, 2007

Hi,

I was wondering if there a method in Access to allow me to count the number of items by group?

For example, I have a set of data in a table like so:

SA
SA
SA
SP
SP
BM

I can choose Group By and the data will group them like SA, SP, BM; and I can choose Count to count the total number of records, but I would like for instance to be able to retrieve the following data:

SA: 3
SP: 2
BM: 1

Currently I'm exporting the list to Excel, and then using Subtotals. It does exactly what I want, but I'm pretty new to Access and wondered if there's a different way! :)

View 2 Replies View Related

Group By Having Count(*)

Jan 3, 2008

I would like to select data from a database using 'Select * " based on a value in a row (same column)being unique.
By that I mean that that data must not repeat again. Idealy I would like to set the number my self so rather then unique I could say
select the rows from the database only if the uniquevalue does not repeat more then x number of times.
eg
Value A
Value B
Value C
Value B
Value B
Value C

So if I wanted to set the uniquness to 1 then only row with Value A would be collected.
If I set the uniquenss to =<2 then I would get data from rows with value A and C so
3 rows returned.

I have this so far

SELECT *
FROM SingleS.mdb
GROUP BY Uniquevalue
HAVING count(*) = 1

View 1 Replies View Related

Help With Sum, Count And Group

Dec 3, 2004

Hey all!

I'm having issues trying to create a query that will take data from two tables and summarize it based on ID, Count(ID), Sum(Score), Sum(Xs). I don't have an issue doing the combining, just the summing and counting (actually, just the counting). I've tried playing directly with the SQL statement, but I keep getting errors.

This is what I have:

tblRoster
ID, Name, Sex, Youth

tblScores
ID, WeekNo, A1, A2, A3, A2X, A3X, B1, B2, B3, B2X, B3X

Sample Data for tblScores would look like this (scores actually are not the same, but you get the idea)

10, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 5, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7

What I would like to do is summarize the info in the following manner:

ID, Name, Sex, Youth, Count(WeekNo), Sum(A1+A2+A3+B1+B2+B3), Sum(A2X+A3X+B2X+B3X) such that I would get the following results:

10, John Smith, M, , 4, 2176, 84
23, Jane Doe, F, , 5, 2720, 105
67, Johnny Jones, M, Y, 3, 1632, 63

I have been able to get the summed part done, but my attempts to do the count have failed. I know that the resulting query will be straight forward, but right now I'm cross-eyed.

Thanks.

-BT.

View 5 Replies View Related

Please Help To Group Dates By Next Month

Aug 21, 2007

I have a table column with dates like

02/05/06
02/06/06
...........

I need to calculate the next date[Due Date] in 6 months, which is simple

[Patient's file].[Last Appointment Date]+180 as [Due Date]

and I have to group the Due Date by the next month. Lets say the due date is 08/25/07, so it has to be grouped as September group at the top or the list. It should be displayed as "September, 2007"

How should I assing let say 08/01/07 - 08/31/07 dates to September groups. I mean the statement should be universal for any day and any month of the year.

Thank you.

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

Record Count On Group

Oct 5, 2004

In have a table which contains customer address details.
I simply want to display a 2-column list. the first column
to display a disnct list of city's from the main table.
the corresponding column must give the no. of records that
contain the entry in the first column.

eg if the main table had 5 customers that lived in london
and 6 in Maidstone then

London 5
Maidstone 6

etc

View 1 Replies View Related

Trying To Count Values Within A Group

Sep 11, 2007

I am trying to count/find the page count. or more like to find how many pages(blanks) are within Y(each Y)..

example:

(fieldl1) (field2) (what im trying to get in the nxt field)
1 Y 3
2
3
4 Y 1
5 Y 5
6
7
8
9
10 C 1

Do i use group by?? im not sure, when i use count, it counts all of the Ys and gives me one the total of all of them. Im trying to find the total of each group of Y's...

can anyone help me out??

View 8 Replies View Related

Group By Hour Of Day And Count Records

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

Helpp!!: Count & Group By With In A Query

Nov 10, 2006

Hi all,

I hope someone can help, i'v searched but i still couldnt figure out how to do this. =(

The screenshot i've attached is what i have so far.
What i want to do is add a column within that query that counts the number of times the ComplaintShort is the same within the Query and displays it.
I'd also like to make the ComplaintShort Field Distinct so it would only show it once and show the number of times it has really appeared as a duplicate entry.

Heres the Query I currently Have to work with.

SELECT Cities.P_Names, Complaints.precinct_id, Complaints.ComplaintShort
FROM Complaints INNER JOIN Cities ON Complaints.prec_id=Cities.P_prec
WHERE Cities.P_Names=Forms!Form1!comType
ORDER BY Complaints.ComplaintShort;

Please Help :(

Thanks in Advance

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

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

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

Forms :: Total On Report - Count Of Records Found For Each Group

Sep 20, 2014

I have a report which gives me a count of records found for each group

group 1 - 10
group 2 - 13
group 4 - 82

what i want is a total below this - ive looked at calculated controls however cant seem to get it what soever - I've tried likes of =sum([counts])

View 1 Replies View Related

Count Between 2 Dates

Nov 28, 2006

I have 2 dates. How do i count the number of days between the two dates?

View 14 Replies View Related

Count Between 2 Dates

Nov 19, 2004

I know this is a simple question.
Using the design view in access, what is the setting to get the total number of records between 2 dates?
Thanks

View 3 Replies View Related

Count Of Dates

Dec 7, 2004

Hi

I am trying to count the dates in the datebase, if date given in the textfield and the date in the table are equal.

sql="select dateField,Count(dateField) from Exam WHERE dateField="+dateField+"";

xxx

View 14 Replies View Related

Count Dates

Dec 9, 2004

Hi,

sql1="select dateField,Count(dateField) as CountOfdateField from Exam GROUP BY dateField";

rst1=stm.executeQuery(sql1);
while(rst1.next()){
String d2=rst1.getString("dateField");
c=rst1.getInt("CountOfdateField");
System.out.print(d2);
System.out.println("");
System.out.println(c);
while(rst1.next()){
if(d2.equals(dateField)&&(c<3))
{
flag1=true;
break;
}
else if(!(d2.equals(dateField))){
flag1=true;
break;
}
else if((c>=3)&&(d2.equals(dateField))){
flag1=false;
break;}
}

}

I am trying to execute the above part of code. But I am getting required output. Can u suggest me the changes in the code.

jyothsna

View 1 Replies View Related