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
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 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?
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
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.....
View 4 Replies View RelatedHi 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
I need to select two fields in a select DISTINCT query
Select tbl_Aplaws.AplawID, DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
anyone know how to make this work?
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 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
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
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;
Thanks in advance!
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 RelatedHello
I have a little problem with a query.
There are some tables, one is n:m with the main table.
main_table n:m a_other_table
Now when I run the query, it displays all relations. But I want only the first.
I tried TOP 1 but, this only show 1 record, DISTINCT will not work, I select * from the query.
Now my question, is it possible to DISTINCT only one argument?
I have id's, one id should only appears one time.
Select DISTINCT idAny, * will not work.
Any ideas?
All,For some reason, I cannot seem to get a result set with unique records using the DISTINCTROW predicate in Access 2007. However, if I simply use the DISTINCT word (even if I am querying more than one field in a table), the result set gives unique records. This seems backwards....it's certainly not right. I know that the DISTINCTROW predicate does what it's supposed to do in earlier versions of the program, because I use them as well. Does anyone have any ideas on this??Has anyone else experienced this?? Thanks!!
View 9 Replies View RelatedOk , I will try and explain this as best I can.
I have a database and would like to run a DISTINCT query ( which I can do no problems ) I would also like to count the number of records at the same time. Say for example my database looked like this
Name Fruit
Adam Apple
Carrie Apple
Hollie Apple
Joe Apple
Adam Orange
Carrie Orange
Hollie Orange
Joe Orange
What I would like it to do is Select the distinct names and list them once ( I can do this bit )
Adam
Carrie
Hollie
Joe
I would then like to count the number of records found for each name and put it next to the relevant name
for example Adam (2) (one apple and one orange)
How would I write the actual query
I hope this makes sense.
Thanks in advance
Adam
PS , great site btw
Hey all
I have data in the table.
(various IDs - in example below just 1)
Each ID has date and status.
I need to pull each ID only ones
for MAX date
If there are 2 like that (with different statuses) i need the one with lowest importance. Any advise ?
StatusLogIDDateID DateImportance
ssss02/04/200802/04/2008ghj6
ssss02/04/200802/04/2008ghj6
tttt02/04/200802/04/2008ghj5
tttt02/04/200802/04/2008ghj5
rrrrrr02/07/200802/07/2008ghj4
rrrrrr02/07/200802/07/2008ghj4
ssss02/08/200802/08/2008ghj6
ssss02/08/200802/08/2008ghj6
ssss02/11/200802/11/2008ghj6
ssss02/11/200802/11/2008ghj6
tttt02/11/200802/11/2008ghj5
tttt02/11/200802/11/2008ghj5
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.
I'm making a form for my school that needs to show every parents name, along with the child they are the gaurdians of. When i use SELECT DISTINCT [Parent Menu PTC].Relations_FullName FROM [Parent Menu PTC]; as the source code it shows only one of each parents name, btu when i use that; I can't see a divorced parents child (it shows bboth parents but only one kid, and they have two kids...) Can any1 help?
View 2 Replies View RelatedHey all
Hope everyone is well.
Im having some probs with a DISTINCT query and hope you can help me out.
In the simplest terms I have a table that has 3 fields. Firstly the BikeManufacturer, then the BikeModel and finally the EngineCC.
Now from this table I am dynamically creating a drop down list via an AJAX request.
See it here .
When the user clicks on a manufacturer the select should be created showing DISTINCT BikeModel.
This could simply be achieved with a DISTINCT Statement but what I also want to do is order the list by the EngineCC.
Now if i put both fields into the DISTINCT statement i get duplications of the models since they dont all have the same EngineCC. But if i leave out the EngineCC from the select then I cant order by it.
Any ideas?
Thanks
I am trying to run a query that queries and returns several data fields with one of them being an ID field. I want to select distinct IDs only, however, I don't think just adding a 'distinct' qualifier prior the ID field will do it--the only field that I want to be distinct is the ID, the others it doesn't matter.
how to add a 'distinct' qualifier to a query that just applies to one field only (the ID field)?
Select distinct ID, firstName, lastName, DOB, City from Person where City = "NYC";
Ok, this is a quote from MS Office online.
Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;
If you omit DISTINCT, this query returns both Smith records.
If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.
The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.
So, from what I understand of this, if any of the fields in the record (or row, whatever) repeat then it is not displayed. Correct me if I am wrong.
The problem is that when I try and use it in my DB it return all the fields regardless of the Distict clause, unless I am only querying one field, which is next to useless.
For example when I make a test table that has repeating words in one field, with a unique ID for each one, the distinct clause works when I display the words field alone and not when I display the ID field as well.
Can anyone explain what is happening or what I am doing wrong?
i'm trying to get a count of how many customers made billing requests during the month of november. the query is based on one table in the database. some customers made multiple requests, but i'm looking for the number of different customers who made requests, not the total number of requests made. the following sql stuff weeds out the duplicate customer numbers and lists each unique customer number.
SELECT DISTINCT TBLBillingRequest.MemberID
FROM TBLBillingRequest
WHERE (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
my problem occurs when i try to to incorporate the count function like so.
SELECT DISTINCT Count(TBLBillingRequest.MemberID) AS CountOfMemberID
FROM TBLBillingRequest
HAVING (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
it gives me a count, but the count includes duplicate customer numbers as well, not the total of unique customer numbers, which is what i want.
any suggestions/help would be greatly appreciated!
if i have a table like this:
4/3/2006
4/3/2007
4/12/2006
4/12/2008
5/2/2003
5/5/2003
i would like to extract only the distinct month and year date, so my query should return
4/12/2006
4/3/2007
4/12/2008
5/5/2003
can anyone plz help me with the sql query? thanks
Hey,
Ok so I have this form that has two subforms on it (sf1 and sf2), these are run by one query called 'test'. Below screenshot of what i dont want happening;
Bugger.jpg (http://i5.photobucket.com/albums/y152/greyowlsl/bugger.jpg)
Now what i want is a distinct command for the 'Document Number' column so it cant be shown more than once. basicly i want it to look like this (http://i5.photobucket.com/albums/y152/greyowlsl/This.jpg)...
what do i do?
Thanks for your time.
,Leon
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!
Hi All, I am stuck, and can't get my head around an easy way to do this. I have a table that has an Team_mem,strmonth, and 31 other fields 01 through 31 with valves of D, V, A etc and I need a way to count how many times the letter V shows up in fields 01 through 31 in a given row. Example
Team_mem strmonth, 01 02 03 04 05 06 07 08 09 etc.....
Bill P May 2007 D D D A V V D D
Joe E May 2007 D D D V V V V D D
I need to be able to see a result like
Team_mem strmonth, Count of V Count of A
Bill P May 2007 2 1
Joe E May 2007 4
Thank you for all your assitance in advance