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? :)
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??
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"));
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....
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.
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.
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?
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];
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.
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.
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?
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.
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
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..
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 .......
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.
I am running a query in a combo box but for some reason the following SQL statement does not give me the result I am after. Which is to only displaying the distinct records. It continues to display the multiple occurrences of the records. I also tried distinctrow with no better results.....
Hello, I have a strange problem: one of my comboboxe is looking up a column in one of my table. Some of the record can be duplicated so I decided to change SELECT to SELECT DISTINCT in my SQL for the row source but it doesn't seem to change anything. Is anybody's got an idea why?
I have a problem using distinct or distinctrow in one of my query. The result shown reflects all records instead of distinct records. May I know what may be the cause of the problem?
Query: SELECT DISTINCT Assets.*, Depreciation.DepreciationDate, Depreciation.DepreciationAmount FROM Assets LEFT JOIN Depreciation ON Assets.AssetID=Depreciation.AssetID;
