Numpty Query Question

[TheScullster]

Hi all

I have been asked to create a list of suppliers and their relevant id codes
from a table containing this information along with order data etc.
So, each supplier and their related codes appear numerous times in this
table.

So far I have created a query which displays all suppliers and codes in
ascending order. But each supplier/code occurs once for each order in the
underlying table.
How do I modify the query to show each supplier name and associated code
only once?

Access 2003 viewing Access 2000 mdb table.

TIA

Phil

 

[Wayne Morgan]

In query design view, right click the area where the tables go and choose
Properties. Set Unique Values to Yes.

Normally, I would expect the suppliers to be in their own table and that
table linked to the orders table.

 

[TheScullster]

"Wayne wrote

In query design view, right click the area where the tables go and choose
Properties. Set Unique Values to Yes.

Thanks Wayne, but this doesn't work.
The data I am working with is in a single table and the Unique Values option
has no effect.
I know that multiple tables would be expected here, but the data is written
out to a single table in Access format from other software and is therefore
not normalised.

Phil

 

[John Spencer]

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

 

[TheScullster]

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Thanks John

I think I've done it. Just added Group By to the design view window.
Not sure exactly how this works, cos it adds the Group By expression to all
fields, not just the Supplier name required.

SQL stuff is:

SELECT EMDATA.PcSuppName, EMDATA.PcSuppCode
FROM EMDATA
GROUP BY EMDATA.PcSuppName, EMDATA.PcSuppCode
ORDER BY EMDATA.PcSuppName;


Phil

 

[John Spencer]

Actually, you can use an even simpler construct if all you need is a list of
Names and SuppCode.

SELECT DISTINCT EMDATA.PcSuppName, EMDATA.PcSuppCode
FROM EMDATA
ORDER BY EMDATA.PcSuppName;

If you were building this in the query grid, there is is property for the
query that sets DISTINCT for the query. Take you present query and remove
the Totals row (menu View:Totals - uncheck). Then right-click in the grey
area and select properties to show the properties window. In the window,
set Unique Values to Yes (True).