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 Replies


ADVERTISEMENT

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

Limited List Rows In Distinct Row Query

Nov 21, 2006

Hello,
On a form I have a way to search for a record by using a listbox that has a Distinct Row Query from the table that the main table that the form is bound to. It looks for the Sample ID's that are associated with the samples that we test. The list ends at record 87877. We are WAY past that number in our numbering scheme but the list box does not display all the records. When the users type anything over 87877 the auto complete doesn't work and the last record shown in the list is 87877. Does anyone have any idea how to make all records show? The users use this to navigate quickly between samples but now it is broken. Is there some sort of limit?
Thanks
Greg

View 4 Replies View Related

Queries :: Pulling TOP DISTINCT Rows In Final Query

Jun 7, 2013

I've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.

These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.

queryDoTheseFirst:

Code:
SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity],
[Activity Created]+[AddDays] AS [Due Date]
FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN
queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID])
ON [Priority List].Activity = ImportTemp.[Inspection Activity]

[Code] ....

The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.

I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount).

View 2 Replies View Related

Queries :: Table With Duplicate Records - Return Distinct Data For Each Client

Jan 17, 2014

I have a table with duplicate records (which is ok) and I want to return distinct data for each client.

It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.

I.E

SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient

Works fine with only the Distinct records for each client returned

However

SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient

Now returns Duplicates!

Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?

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

SQL Distinct

Feb 22, 2007

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

Select Distinct

Aug 9, 2005

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?

View 1 Replies View Related

Select Distinct

Sep 13, 2005

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?

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

Distinct And DistinctRow

Jun 19, 2007

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!

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

DISTINCT Question

Jul 12, 2007

Hello


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?

View 5 Replies View Related

DISTINCT Predicate

Aug 31, 2007

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 Related

SELECT DISTINCT Help...Please

Nov 15, 2007

Ok , 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

View 10 Replies View Related

Distinct Query

Feb 15, 2008

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

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

SELECT DISTINCT Help

Sep 18, 2006

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 Related

Need Help With DISTINCT Query

Apr 16, 2007

Hey 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

View 1 Replies View Related

How To Select Distinct IDs Only

Jul 9, 2012

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";

View 1 Replies View Related

Trouble Understanding DISTINCT

Oct 12, 2005

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?

View 5 Replies View Related

Counting Distinct Records

Dec 18, 2005

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!

View 2 Replies View Related

Distinct Month AND Year

Aug 28, 2006

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

View 2 Replies View Related







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