SELECT DISTINCT

[redbay]

Hi

i am having problems with a distinct query, it want to show records in the query but if there a re more than one record with the same name i just want the name to display once. Here is the query

SELECT DISTINCT tblFlowers.name, tblFlowers.productID, tblFlowers.nickname, tblFlowers.height, tblFlowers.drought, tblFlowers.type, tblFlowers.fullSun, tblFlowers.partialShade, tblFlowers.image, tblFlowers.employeeExpert
FROM tblFlowers;

what am i doing wrong??

 

[THWatson]

redbay
I'm not sure what you are doing wrong, but I do see something...

One of your fields in the tblFlowers table is called "name." That's a reserved word in Access and shouldn't be used. Change it to FlowerName, or something like that.

Also, I assume your productID is meant to identify either each product or each record. So if you have a product named Roses with productID of 12, and another product named Roses with a productID of 42, you will still have two products and both are going to show.

If there are duplicate entries, you may wish to run a Find Duplicates query and eliminate the duplicates.

Tom

 

[SkipVought]

Hi,

It selects the Distinct occurrence of EACH COLUMN in the select.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[redbay]

Tom

how do i run a find duplicates query and elininate the duplicates?

 

[THWatson]

Select a New query, and one of the wizards on the list is "Find Duplicates..."

Tom

 

[redbay]

Tom

Have tried using the find duplicates query but this shows the duplicates and i need to hide them??

 

[THWatson]

redbay
You said you wanted to "eliminate" then, which led me to believe you have duplicate records you want to remove. The purpose of the Find Duplicates query would be to identify records you want to remove.

If you have duplicate records, is that because they are there in error...or do you want to keep duplicate records?

Tom

 

[redbay]

i realise where my problem is, as i am linking to an ID in another table that is on a one to many relationship it is showing all occurences and i just need to show the one

 

[lespaul]

Ok, if you are going to join into the many table, which ONE of the many do you want to select?

The query you posted doesn't join into any table???



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[redbay]

Sorry, tblflowers.flowername (ive changed the field name) is the one and tblColour.Colour is the many. for example i can have one rose with many colours but i just want to show rose in this query. thanks for your help

 

[lespaul]

ok, i'm really confused, I'm still not sure what you are trying to accomplish. You have a table Flowers which contains the following fields:

name, productID, nickname, height, drought, type, fullSun, partialShade, image, employeeExpert

[ps]what do you do if there is more than one employee expert?[/ps]

You have a query:


SELECT DISTINCT tblFlowers.name, tblFlowers.productID, tblFlowers.nickname, tblFlowers.height, tblFlowers.drought, tblFlowers.type, tblFlowers.fullSun, tblFlowers.partialShade, tblFlowers.image, tblFlowers.employeeExpert
FROM tblFlowers;

(now you realize that when you use the DISTINCT keyword that EVERY field has to be the same? So if you have 6 records in tblFlowers with a FlowerName of "Rose", but each rose has a different nickname, ALL 6 Rose records will be returned.)

The query you showed above should select from the Flowers table all records where EACH of the fields in the select are unique. Is this not what is happening? What are you trying to do with the colours table?

Can you post a sample of the records that are being returned with your query above and what is wrong with the results set?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual