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 Replies

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

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

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

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

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

Reports :: Sum Group Items By Specific Item And Hide Details Of Group Summing

May 29, 2015

Despite Google I can't seem to figure this out.

I have some data in a format similar to:

Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000

I want my report to total by style, without details and to look like:

driver careful 13500
racer reckless 12500
snail slow 6000

How do you get a report to sum the group items by a specific item and to hide the details of that group summing?

View 2 Replies View Related

Reports :: Control Group Expression For Group In Report?

Mar 28, 2013

Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?

View 5 Replies View Related

Forms :: Option Group - Warn User When Neither Of 2 In Option Group Not Ticked?

Jul 25, 2013

I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".

[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]

The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.

Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.

Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.

View 1 Replies View Related

Count Records Problem. Display Field Even When Count Is Zero.

Apr 13, 2006

I have a table tblBookings.

In this table it has a bookingID, CustomerID and some other none relevant details.

The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings

A customer can exist in tblCustomer without existing in the booking table.

I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).

I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.

SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;

How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)

I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.

The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2

For every hotel.

That kind of thing.

If you need more information please shout.

View 3 Replies View Related