Eliminating Duplicate Items in a Query

  • Thread starter Thread starter The Chinaman
  • Start date Start date
T

The Chinaman

Hi,

I have two tables in a database.

1. Has several colums one of which can contain duplicate names, other
colums
are unique to the ID.

2. Has a number of columns which have items from table one under a unique
Named product.

The query I need is for the items to be shown from table two but only those
which are unique to table two.

I have tried various permutations and keep getting all the same named items
returned
for each column in table two.

Thanks for your help
 
Without a look at the underlying SQL statement, this is just a wild guess...

Have you set the Unique Values property of your query?
 
Hi,

This is the SQL statement:-

SELECT tblSkins.SkinName, tblSoftware.SoftwareName,
tblSkins.CategoryDescription, tblSkins.ScreenShotFile, tblAuthors.NickName,
tblSkins.SkinFile
FROM (tblSkins INNER JOIN (tblAuthorSkins INNER JOIN tblSoftware ON
tblAuthorSkins.SoftwareID = tblSoftware.SoftwareID)
ON tblSkins.SkinID = tblAuthorSkins.SkinID) INNER JOIN tblAuthors ON
tblAuthorSkins.AuthorID = tblAuthors.AuthorID
WHERE (SkinName = ::SkinName::)
ORDER BY tblSoftware.SoftwareName, tblSkins.SkinName;

------------------------------

I guess the problem arises in the "WHERE" part, but I am not syre how to
make it unique.

Thanks
 
SELECT tblSkins.SkinName, tblSoftware.SoftwareName,
tblSkins.CategoryDescription, tblSkins.ScreenShotFile, tblAuthors.NickName,
tblSkins.SkinFile
Click to expand...

Try changing SELECT to SELECT DISTINCT, or (equivalently) opening the
query grid, viewing the query's Properties, and set its Unique Values
property to True.

John W. Vinson[MVP]
 
Hi,

Sorry, cannot find query's properties and unique value properties.
Click to expand...

Edit the SQL to SELECT DISTINCT is one way; another is to open the
query grid in design view; right mouseclick the grey background and
select "Properties" from the popup window; yet another is to select
"View... Properties" from the menu.

John W. Vinson[MVP]
 
Hi.

I have a feeling I am trying to use the wrong query.

The details shown below will hopefully make it clearer.


TableOne has around 2000 entries, whilst TableTwo only about 25 but will
grow very quickly.

TableOne:

OneID, OneName, One Category, OneFile, OnePictureFile

1 A M unique unique
2 A P unique unique
3 C Q unique unique
4 A O unique unique
5 C N unique unique

TableTwo:

TwoID, TwoName, CategoryM, CategoryN, CategoryO, CategoryP,CategoryQ,

1 X A A
A
2 Z C
C



The two tables have a relationship join at there "IDs".

TableOne and TableTwo are filled in independantly.

Table two contains all text apart from ID, that is I have the Name under
each category which I have taken
from TableOne.

Thanks
 
The details shown below will hopefully make it clearer.


TableOne has around 2000 entries, whilst TableTwo only about 25 but will
grow very quickly.
Click to expand...

I'm completely lost. Your first post had the SQL of a query involving
tblSkins, tblSoftware, and tblAuthorSkins. It appears to be unrelated
to this. Could you please explain a) your actual table structure,
preferably without word wrap; b) the SQL of the query; c) the results
you're actually getting; and d) the results you want.

John W. Vinson[MVP]
 
I'm not sure I understand what you are trying to accomplish, even after
looking at the SQL statement. Could you paraphrase what you want to end up
with, without referring to your current table structure?

For example, "I want to have a list of unique names of products".
 
Hi.

I am als very bad at explaining, but I think you have it.

There are many products in different categories. Some have the same name.

I wish to be able to show details for a specific product from whatever
category. I do not
want to show all products of the same name.

Many thanks your diligence
 
Create a query that returns all the names. Change the Unique Values
property to Yes. That should give you only the "unique" names.
 
Hi,

I tried that already. Sorry


Jeff Boyce said:
Create a query that returns all the names. Change the Unique Values
property to Yes. That should give you only the "unique" names.

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
 
Hi,

If a run a "SELECT DISTINCTROW SkinName FROM tblSkins"

I get one record returned. So OK.

However, if a run a query which requires the linking of more than one table,
I get all records of the samename returned.

Thanks
 
So, if you first run as many queries as you need to get the unique values,
then create one more query that joins to any additional tables for
"details", you could still make this last query a "unique values" query.
 
Hi,

Some progress. I can now get three out the five items required. These are
as you would
expect in one table. The other two items are in different tables.

When I link the tables I get an error message :-

The specified field 'SkinID' could refer to more than one table listed in
the
FROM clause of your SQL statement.


First statement that works:- called this qryskinname

SELECT DISTINCT tblSkins.SkinID, tblSkins.SkinName, tblSkins.ScreenShotFile,
tblSkins.SkinFile, tblSkins.CategoryDescription FROM tblSkins
WHERE (SkinID = ::SkinID::);

Second statement:-

SELECT DISTINCT qryskinname.*, tblSoftwareAuthors.SoftwareName,
tblSoftwareAuthors.NickName
FROM qryskinname INNER JOIN (tblSoftwareAuthors INNER JOIN tblAuthorSkins ON
(tblSoftwareAuthors.AuthorID=tblAuthorSkins.AuthorID) AND
(tblSoftwareAuthors.SoftwareID=tblAuthorSkins.SoftwareID)) ON
qryskinname.SkinID=tblAuthorSkins.SkinID;


Thanks
 
Back
Top