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

Understanding Existing Databases

Feb 14, 2007

Hi, Big Jim Here:

I was hoping you guys would have a recommendation. Do you know of a book or web site that offers workbook examples to help practice learning how to understand existing databases?

My biggest problem with databases has never been designing them but learning how to follow someone else's work. Some place to practice on this deficiency of mine would be great. Any thoughts?

Thanks in advance,

Big Jim

View 7 Replies View Related

General Understanding Of Relationships

Dec 22, 2006

Please see the attached two files.

I have tried to figure out what the difference in functionality would be between 1 and 2. In the latter I have just created a series of relations between the last two tables (but that structure could be used throughout the whole DB instead of hanging it together with one relation between tables as is the case for the rest of the DB in this picture).

So there can be three different situations with this table structure:
- linking all tables together via multiple relations
- linking all tables together via a single relation
- a combination of both, such as in the images.

Why would someone choose one versus the other? "Which is better in what situation?"

I've been fiddling about for a while now, but in a structure such as this it doesn't seem to make a difference. Or maybe there are differences in use/design, but on a level that I have not explored yet.

Could anyone explain this to me a bit? I have multiple books, all of which handle basic relations but I have not yet seen an explanation for this question. I think understanding the whole relationship topic is critical to be able to see other pieces of the puzzle fall into place later on. And as of yet, this is really fuzzy to me.

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

Need Advice On Understanding A 3rd Party Database

Jan 9, 2006

I have to create reports from an Access database used in a commercial application. The backend uses Access but the front end does not.

The vendor does not document the data or provide any support for that. The database has over one hundred tables and thousands of fields.

I'm thinking I'll create small transactions in the application and then study the database to see what has changed. But this seems overwhelming considering how many tables and fields there are. So I'm looking for advice on how to approach this.

I have a FoxPro background but I'm new to Access.

View 4 Replies View Related

Queries And Forms Need Help Geting An Understanding

Jun 11, 2007

Hello i am a college student and i was just getting on with my database assignment. i don't want any one to do it for me i just want someone to point me in the right direction. i am making a basic Encyclopedia on planets for my project. i have made one form which i want all my other forms to look like. this form is getting its information out of queries. this form is opened when i click a picture of the planet. so i was wondering if it was possible if i could link all the picture up to the one form but each picture opens a different set of data with in this form. i have already thought of just copying the forms and changing the control source. just wondering if there is a different way of doing it.

thanx
tom

View 3 Replies View Related

Field Relationship Understanding Needed

Jun 5, 2006

ok, let me please ask this question regarding a payment relationship in my database

it will be paid in four ways, cash, creditcard, cheque, paypal

each of these payment types will have their own features..

eg creditcard (name on card, card number)

cheque (cheque date, date cleared)

paypal (username...YOU GET THE PICTURE

so is this below way a good way to store these details, or can i do it with 4 less tables..

http://bb.1asphost.com/antonyx6/untitled2.jpg

if this way is correct, i dont see how each id will be entered..
eg
if i enter some cheque details.. will that lead the chequeid to be linked to the paymentid, and the paymentid to the bookingid, and the bookingid to the booking details etc

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







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