COUNT() And DISTINCT Functions Help Needed

Jan 4, 2007

Hello, guys. First of all, may all of you have a very cool New Year 2007!

In my MS Access database i have 2 tables: Table1 and Table2. These are related by means of the fields Field1 (Primary key of Table1) and Field2 (Secondary key of Table2). The relation is 1 to Many.

I pretend to count the rows from the Table2 with different Field2 values. Thus, the following query would be an ideal solution:

SELECT COUNT(DISTINCT Field2)
FROM Table2

Unfortunately, the DISTINCT clause is not admitted within COUNT parenthesis. So, what i came up with is:

SELECT COUNT(*)
FROM Table1
WHERE Field1 IN (SELECT DISTINCT Field2 FROM Table2)

It works, but it takes too long to run the main query, where i embedded several subqueries like the one above.
Is there any other way to get the same result reducing the response time?

Thanks for your attention in advance.

View Replies


ADVERTISEMENT

Super Snazzy Search Functions Needed

Feb 5, 2008

This Forum is a God Send and everyone is so helpful. Ghudson's Audit Trail code saved me many hours of hair pulling :) Anyone out there who may be able to help with my Audit Trail Query?

Anyway, I've been developing a database for the last 2 weeks (night & day it seems!) to track "bugs" found in the code we (not me) develop, suggestions & new requirements and just when I thought I'd got it cracked they came up with the next requirement they'd forgotten to mention!! :eek:

I need a super snazzy search function. The top half of the screen is a form with various combo boxes of which some or all of the values can be selected i.e reference no. author, between dates and a keywords field (a bit like the search page at the beginning of this forum!). On clicking the 'Select' button all the records that meet the input criteria appear in the bottom of the screen in a data sheet view (Ref No. Description & Date). The user can the Double Click on the record they wish to view and they will be taken to the full record in the DataEntry Form.

I'm sure this isn't as complicated as it seems to me at the moment but I am so stumped I don't even know when to start. I've been writing databases for years using the wizards within Access but only recently started to experiment with code. I would really appreciate any assistance forum members can give me but as I am new to these complications please provide 'idiot proof' answers so I can understand what I am doing. :)

Thanks in anticipation

Helen

View 12 Replies View Related

Count Distinct

Jul 29, 2005

Hi Everyone!,

I have a table with the follwing;
ShopID, CustomerID, Month, Item_ref, Product Category

There are about one thousand shops.
The CustomerID field is only unique per shop.
I.e. all transactions for customer ID '002' in shop 1 will be for the same customer, but customer ID '002' could appear in another shop and will be a different customer,
I want to be able to bring back a list of distinct customers, the number of items they have had and the product categories.

Has anyone got any ideas how I do this? I presume I would need to do a count distinct or something??

thanks!,

Sasha

View 2 Replies View Related

Distinct Count And Sum

Jan 28, 2007

I have a table like this in MS Access database:

IDDateCustomerIDInvoiceNoItemNoAmount
101/01/2007A1AAA1
201/01/2007A1BBB2
301/01/2007A2CCC3
601/01/2007B5AAA6
701/01/2007A6BBB7
801/01/2007B5BBB8

I want to get disctinct count of InvoiceNo while summing up Amount by Date and CustomerID in one query. Firstly, I tried to use correlated query to count unique records but it failed.
SELECT A.Date, A.CustomerID, (SELECT COUNT(B.InvoiceNo) FROM Table1 B WHERE B.Date = A.Date AND B.CustomerID = A.CustomerID ) AS CountOfDistInvNo, Sum(A.Amount) AS SumOfAmount
FROM Table1 A
GROUP BY A.Date, A.CustomerID;

The result of Count is not a unique count. Can anyone tell me what's wrong with this correlated query? :confused:

After hours of googling on internet, the solution I got is equivalent to querying on top of the resulting table from another query.

My final query is

SELECT B.Date, B.CustomerID, Count(B.InvoiceNo) AS CountOfInvoiceNo, Sum(B.SumOfAmount) AS SumOfAmount
FROM (SELECT A.Date, A.CustomerID, A.InvoiceNo, SUM(A.Amount) AS SumOfAmount FROM Table1 A GROUP BY A.Date, A.CustomerID, A.InvoiceNo) AS B
GROUP BY B.Date, B.CustomerID;

Is there a better solution than this in Access? :)


Thanks,

Huyeote

View 5 Replies View Related

Distinct Count

Jun 18, 2007

Tried several suggestions in the forum but haven't found one to match my needs.

I have one table, Jupiter and need to use this query

SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind
FROM Jupiter
GROUP BY Jupiter.codename, Jupiter.tooling_ind
HAVING (((Jupiter.tooling_ind)="Y"));

For each "codename", there is an attached "part_no" which can appear under a codename several times. The problem is that I need to count only one instance of the "part_no" for each "codename".
So, my query looks like this:
Build # Needing Tool #Tooled
LP1 769 192

The # Needing Tool is counting every record that, say pn 10592 shows up in and I need it to be counted only once in each "Build"

I did try this suggestion I found with no results.

SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind
FROM (SELECT DISTINCT Jupiter.part_no FROM Jupiter)Jupiter
GROUP BY Jupiter.codename, Jupiter.tooling_ind
HAVING (((Jupiter.tooling_ind)="Y"));

Is there a way to do this somehow?

Thanks!

Toni

View 3 Replies View Related

Count/SUM Distinct Help

Jul 6, 2007

I've searched on google for count distinct help but I had no luck. I'm counting 2 different columns and when the query is run the counts in both columns appear exactly the same. Also, I need a SUM distinct (if possible) to sum only where it is being counted in 'CountOfCar_Nr'. Here is my SQL....

View 3 Replies View Related

Count Distinct

Aug 30, 2005

I'm building a sort of uberquery to do some calculations on a dataset for me, and I reached a point where I'm having some problems. I'm going to have to assemble the main query by doing a join on several subqueries.

Essentially, I have one table with the following fields (DrawingNo, DrawingRev) and another table with a great deal of data in it, but as far as this problem is concerned, it has (DrawingNo, DrawingRev, OrderNo, SPOOL). I have several tests that have to be performed.

The first table contains a master listing of al drawings we are supposed to have, the second contains information related to procesing the drawings. There is one row in the second table for each piece to be made from each drawing.

The first test is to verify that no gievn revision of any drawing has been split across more than one OrderNo. Essentially, I Count(OrderNo) and group by DrawingNo and DrawingRev, so that there is one line in the query for each drawing/revision pair, and a count of the number of OrderNos that meet those criteria.

The second test (and this is the one I have a problem with) involves taking a count of the number of unique values of DrawingRev that occur for a given DrawingNo. Something like SELECT Count(DISTINCT Drawingrev) FROM SecondTable GROUP BY DrawingNo should work, but Access doesn't appear to support "Count(Distinct". How do I get around this, and remember that any answer has to be able to be used as a subquery.

View 3 Replies View Related

Two Count Functions

Feb 6, 2008

Hi
Im trying to run two count functions on one column in sql (access 2003). I need to return two new column, pass and fail. something like this; select count(grade) as pass, where grade>=40 and select count (grade) as fail, where grade<= 39.

my table is student_ID, module and grade:

student_ID Module Grade
0012 history 59
0034 history 34
0045 maths 78
0031 maths 45
0046 spanish 66

what i need is:

Module Pass Fail
History 1 1
Maths 2 0
Spanish 1 0

I have achieved it by running two seperate querys, one for pass and one for fail and then merging them with another query but is there any other way?

View 1 Replies View Related

Count The Distinct Items

Jun 3, 2007

Hello all,
I am new to this forum. I have this problem where I need to sort the 4 books in the tables below according to the highest quantity of book sold.In this scenario,
Book C would be 1st in the table with a total quantity of 8.
How do I write a query in access to perform such operation. I have to use DISTINCT to get each book and also count the quantity(e.g. Book C:5+8).
But it seems that I can't use Count and distinct together in Access.

Title SalesNo Quantity
BookA22
BookB 31
BookC 45
BookC 53
BookD 6 1

Hope u all can help!

View 4 Replies View Related

Distinct Count In A Query?

Sep 13, 2011

I have a query I have 4 fields:

E.G.

Field1 Field2 Field3 Field4
C 20 25 145541
C 40 20 145541
C 10 10 145540

D 20 20 145540
D 10 10 145538

I need to obtain:

C 70 55 2
D 30 30 2

I need to group Field1 and sum Field 3 and field3 and count the UNIQUE ID in field 4.

View 5 Replies View Related

Queries :: Distinct Count In Subquery

Jun 3, 2015

Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.

Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.

SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];

View 4 Replies View Related

Queries :: How To Pull All Records If Distinct Count Is Less Than 1

Mar 18, 2014

I have a table with the following values.

EMPLLOYEE_ID
JOB
PAY_DATE
LOCATION

Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1. There are cases where an employee might have two pay checks generarted for the same PAY_DATE.

View 3 Replies View Related

Modules & VBA :: Count Distinct Excel Output?

Dec 16, 2014

I have a query that contains Invoices(usually 2 line items per invoice) Taxable and NonTaxable. I need to able to basically export it to excel on multiple worksheets when the Amount of unique invoice number reaches 15.

From all my scouring on the internet I can't find anything with this criteria.

View 2 Replies View Related

Forms :: Count Distinct Values In A Control?

Nov 22, 2014

I would like to count distinct values on a control in the footer of a search form. How to accomplish it.

View 2 Replies View Related

Queries :: COUNT With Select Distinct On Expression

Dec 9, 2014

I have the following SQL which returns rows of distinct numbers that are calculated from a field.

SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;

ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307

I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:

1307
1311
1401
etc...

I just want NumberOfCalls to show "3". Is there an easy way to do this?

View 5 Replies View Related

Queries :: Distinct Count In Access 2010?

Jul 15, 2015

I'm trying to write a query to get a count of Volunteers under a certain Job Code for a given year - problem comes in that a single Volunteer may record hours multiple times under a Job Code in a given Fiscal Year. I can't seem to get "Unique Values" to work. From my research it looks like I need a two-part query but (as a newbie) I'm not quite sure how to write that. I have two queries, one that works and one that doesn't.

This one counts total amount of hours volunteered under a given Job Code, it works:

Code:
SELECT tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup, Sum(tblHoursWorked.HoursWorked) AS [Sum Of HoursWorked]
FROM tblHoursWorked
GROUP BY tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup;
HAVING (((tblHoursWorked.FiscalYear)=[Enter Year:]));

This one attempts to count number of Volunteers that worked under each Job Code in a given year - it instead counts number of entries under that job code. What I think I need to do is count unique instances of the NamesIDFK, but I can't seem to get that to work.

Code:
SELECT DISTINCT tblHoursWorked.[JobCodeLookup]
FROM tblHoursWorked
GROUP BY tblHoursWorked.[JobCodeLookup];

1) correcting my second query and 2) putting them into one query so I can use them in a report.

View 6 Replies View Related

Reports :: Concatenating Sum And Count Functions In Footer

Mar 21, 2013

I have a report that I'm trying to concatenate some "sum" and "count" data into one line. This is what I have

Code : "There is currently" & " " & =Sum([pounds]) & " " & "Pounds in" & " " & =count([quant]) & " " & "Containers"

but it gives me a syntax error when going from Design view to Report View.

View 16 Replies View Related

Reports :: Get Count Of Distinct VolunteerID In Unbound Textbox On Report?

Jan 28, 2014

SELECT qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, Sum(qryHoursReq.Total_Time) AS SumOfTotal_Time, qryHoursReq.S_date
FROM qryHoursReq
GROUP BY qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, qryHoursReq.S_date
HAVING (((qryHoursReq.S_date) Between [startdate] And [enddate]));

This is my query

I'm trying to get a count of distinct volunteerID in an unbound textbox on report.

If I use =Count(*) I get 2 when I put in date parameters and it groups by program

This is kind of correct, but, these 2 are the same person, she had different days she participated.

What code can I use for only get a count of 1

FOrgot to mention, when I use the DCount with numerous examples, I get #Error

View 2 Replies View Related

Queries :: DISTINCT COUNT Of Name If Total Number Of Items Greater Than 0

Jan 29, 2014

I have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....

TBL-STORE

NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere

TBL-PURCHASES

NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14

I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.

This is one way I've tried to write the SQL, but I keep getting an error...

SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");

I also tried an Count(IiF( and can't get that to work either..

View 3 Replies View Related

Queries :: Applying Multiple Count Functions With WHERE Criteria Without Affecting Others

Dec 11, 2013

Any way to use multiple count functions in a query with their own individual filters without affect the others?

For example:

SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2]
FROM [E&I Table]
WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV"))
GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;

I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...

View 6 Replies View Related

Domain Aggregate Functions To Count Occurrences Of Specific Value In Field

Jul 8, 2012

i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.

View 3 Replies View Related

Queries :: Count Distinct Error - Missing Operator In Query Expression

Jan 23, 2015

I am trying to get a count of the unique customers in an access 2010 database

After some research it seems i should be using

SELECT Count(Distinct [Customer]) FROM [tblMain];

But when i use this i get

Syntax error (missing operator) in query expression 'Count(Distinct Customer)'.

I have tried leaving out the square brackets but this does not work....

View 3 Replies View Related

Need Distinct Records Of Whle Table But Distinct On One Field

Sep 15, 2006

Hi, Wish if some one could help me ASAP.
I have a table which contains name, tel, email
i need to import only records which have distinct email.
I do need need to import data of all three fields but only which has distinct email.
As there are number of record which are duplicate.
They have different names but same email.
So i need to condition only for distinct email but dump the data in a new table with all three records.
so same names can have different email.
but same email can't have duplicate email.
So need only records which have distinct email.
Please help .......

View 1 Replies View Related

Distinct Rows From Non-distinct Data?

Oct 10, 2005

I have data which consists of:

xxxxx123 A.Nother 123456
xxxxx123 B.Jones 123457
xxxxx456 D.Smith 123458
xxxxx456 Z.Zephir 123489

How would I ensure that the query returned only unique rows (where column 1 is unique) based on the first alphabetical record of column 2?

Any ideas?

View 1 Replies View Related

Looking Up A Distinct Value

Aug 28, 2005

I want to count the number of distinct dates in a table. Ie - So I know there were 80 requests over 5 days.

The syntax I imagine is something like:

myText = Dlookup("count(DISTINCT myDate)", "myTable")

Can somebody please help.

Thank you in advance

View 3 Replies View Related

Distinct

Sep 13, 2006

Hi,

I have a query with in excess of 20 fields. One of the fields is [POLICY NUMBER]. I want to use DISTINCT to only show rows of data where the Policy number is unique.

If I put DISTINCT after SELECT the query will only include results where the combination of values from all the fields are unique and I dont want this.

How would I go about this.

Any help would be appreciated.

Regards

Matt

View 8 Replies View Related







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