If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Numpty Query Question
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 |
#2
|
|||
|
|||
Numpty Query Question
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. -- Wayne Morgan MS Access MVP "TheScullster" wrote in message ... 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 |
#3
|
|||
|
|||
Numpty Query Question
"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 |
#4
|
|||
|
|||
Numpty Query Question
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" wrote in message ... "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 |
#5
|
|||
|
|||
Numpty Query Question
"John Spencer" wrote (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 |
#6
|
|||
|
|||
Numpty Query Question
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). "TheScullster" wrote in message ... "John Spencer" wrote (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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
Access shuts down, when i try to save this query | Dann | Running & Setting Up Queries | 12 | February 3rd, 2006 02:49 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |