Lin:
If I understand your goal correctly, then I would suggest creating a view using the GROUP BY clause with JUST the columns you need for your labels. Something like: CREATE VIEW label_view (COMPANY_NAME,STREET1,STREET2,CITY,STATE,ZIPCODE) + AS SELECT COMPANY_NAME,STREET1,STREET2,CITY,STATE,ZIPCODE + FROM supplier_view + GROUP BY COMPANY_NAME,STREET1,STREET2,CITY,STATE,ZIPCODE You can then base your labels on this view. Does that make sense? Mike Ramsour AK Steel Coshocton Works Quality Department Phone/VMS: 740-829-4340 _____ From: Lin MacDonald [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 22, 2008 11:18 AM To: [email protected] Subject: [RBASE-L] - RE: select distinct... I have a View that I created of Suppliers that contains data from the main Supplier table, an Inventory table and a Contacts table. Because the Inventory & Conatcts are 'one to many' to the Main Supplier table, I end up with multiple Suppliers in my View. I want to print mailing labels so I want to only print one of each Company. I could specify almost every column in my view for my print statement, but I was thinking there might be a shorter way. Lin ----- Original message ----- From: "Ramsour Mike" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[email protected]> Date: Tue, 21 Oct 2008 20:18:29 -0400 Subject: [RBASE-L] - RE: select distinct... Lin: That all depends on what you're wanting to accomplish. Obviously the more columns you specify in your SELECT statement the less DISTINCTness there's going to be. Have you checked out the GROUP BY clause of a SELECT statement? It can be a powerful tool. Here's an example from one of my database: SELECT MELTCODE,COUNT(*),SUM(POUNDS) FROM REJECT GROUP BY MELTCODE MELTCODE COUNT (*) SUM (POUNDS) -------- ---------- --------------- 111-10 196 576,765 151-15 4 30,150 151-21 2 33,370 174-20 1057 681,820 180-21 158 200,319 181-21 469 1,763,874 182-20 228 855,215 This command displays a "distinct" record for each MELTCODE in my REJECT table. In addition to this I aggregated the MELTCODE by having R:BASE give me a count of all records where a given MELTCODE exists plus a total of the POUNDS column. Again it gets back to what you need to accomplish. Hope this helps. Mike Ramsour AK Steel Coshocton Works Quality Department Phone/VMS: 740-829-4340 _____ From: Lin MacDonald [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 21, 2008 7:13 PM To: [email protected] Subject: [RBASE-L] - select distinct... I need a push with the 'select distinct' command. My brain just isn't following along! I want to select all rows in my view (dependant on my 'where' clause), but I only want one instance of each Company to show. Is there a way to use Select * from... yet still use 'distinct' or do I need to select (and list) each column ? thanks, Lin
