Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use "distinct" to prevent duplicate records
I'm using MS Query within Excel to extract mailing addresses from my SQL
database (based on total dollars spent). See the Query, below. I'm getting duplicate addresses because there are multiple records for the same address. I don't want to send multiple letters to the same address so I want to prevent duplicate addresses. But no matter where I place the word "distinct," I get an error message (in Query): "could not add the table." Names and addresses are held in a table called IINFO. Street addresses are in the field called I_ADDR1 within the IINFO table. Any suggestions on how to prevent duplicate street addresses? (BTW ... I previously posted this question to the group but it never appeared ... maybe I didn't wait long enough ... sorry if this is a duplicate question.) Thanks. Dan SELECT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP FROM (IINFO LEFT JOIN STAY ON STAY.S_IRECID = IINFO.I_RECID) WHERE STAY.S_STATUS = 'HIST' AND STAY.S_ADATE = CONVERT(DateTime,'11/14/2004 00:00:00',101) AND IINFO.I_FIRST Not Like 'Group Master' AND IINFO.I_ADDR1 '1' GROUP BY IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP ORDER BY IINFO.I_ADDR1 ASC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use "distinct" to prevent duplicate records
Hi Dan
Generally the DISTINCT statement would go immediately after SELECT so: SELECT DISTINCT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST,... But considering that you are using SUM and GROUP BY statements you should only be returning distinct records anyway. One way I can see that you could be getting records with duplicates in I_ADDR1 is if you have more than one person listed at the same address in the IINFO table. If this is the case I guess you would need to decide which name to use. Anyway, hope this helps Rowan crimsonkng wrote: I'm using MS Query within Excel to extract mailing addresses from my SQL database (based on total dollars spent). See the Query, below. I'm getting duplicate addresses because there are multiple records for the same address. I don't want to send multiple letters to the same address so I want to prevent duplicate addresses. But no matter where I place the word "distinct," I get an error message (in Query): "could not add the table." Names and addresses are held in a table called IINFO. Street addresses are in the field called I_ADDR1 within the IINFO table. Any suggestions on how to prevent duplicate street addresses? (BTW ... I previously posted this question to the group but it never appeared ... maybe I didn't wait long enough ... sorry if this is a duplicate question.) Thanks. Dan SELECT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP FROM (IINFO LEFT JOIN STAY ON STAY.S_IRECID = IINFO.I_RECID) WHERE STAY.S_STATUS = 'HIST' AND STAY.S_ADATE = CONVERT(DateTime,'11/14/2004 00:00:00',101) AND IINFO.I_FIRST Not Like 'Group Master' AND IINFO.I_ADDR1 '1' GROUP BY IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP ORDER BY IINFO.I_ADDR1 ASC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use "distinct" to prevent duplicate records
Yes, Rowan, you're exactly right. When the addresses are repeated, it's
because there are different names at the same address. I guess there's no way of saying "don't show the address twice even though the name is different." In other words, I'd accept whichever name appears first (and disregard the others) ... anything to prevent sending multiple mailings to the same address. If you have another suggestion, I'd appreciate it. Otherwise, thanks just the same. Dan "Rowan Drummond" wrote: Hi Dan Generally the DISTINCT statement would go immediately after SELECT so: SELECT DISTINCT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST,... But considering that you are using SUM and GROUP BY statements you should only be returning distinct records anyway. One way I can see that you could be getting records with duplicates in I_ADDR1 is if you have more than one person listed at the same address in the IINFO table. If this is the case I guess you would need to decide which name to use. Anyway, hope this helps Rowan crimsonkng wrote: I'm using MS Query within Excel to extract mailing addresses from my SQL database (based on total dollars spent). See the Query, below. I'm getting duplicate addresses because there are multiple records for the same address. I don't want to send multiple letters to the same address so I want to prevent duplicate addresses. But no matter where I place the word "distinct," I get an error message (in Query): "could not add the table." Names and addresses are held in a table called IINFO. Street addresses are in the field called I_ADDR1 within the IINFO table. Any suggestions on how to prevent duplicate street addresses? (BTW ... I previously posted this question to the group but it never appeared ... maybe I didn't wait long enough ... sorry if this is a duplicate question.) Thanks. Dan SELECT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP FROM (IINFO LEFT JOIN STAY ON STAY.S_IRECID = IINFO.I_RECID) WHERE STAY.S_STATUS = 'HIST' AND STAY.S_ADATE = CONVERT(DateTime,'11/14/2004 00:00:00',101) AND IINFO.I_FIRST Not Like 'Group Master' AND IINFO.I_ADDR1 '1' GROUP BY IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP ORDER BY IINFO.I_ADDR1 ASC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use "distinct" to prevent duplicate records
Maybe take the names out of the group by statement and use
MIN(IINFO.I_FIRST) and MIN(IINFO.I_LASTNAME) in the select statement. Actually thinking about that it would cause its own set of problems eg. if you have Abby Winston and Zack Adams at the same address you would end up sending your letter to Abby Adams. Maybe contactenate the names first so that you have a single name field and then select the min (or max) of that. You will also need to think about how this affects your Total Spent calculation. If you select the MIN NAME in your query as it is you will report the total spend from both names on the one resultant record. Regards Rowan crimsonkng wrote: Yes, Rowan, you're exactly right. When the addresses are repeated, it's because there are different names at the same address. I guess there's no way of saying "don't show the address twice even though the name is different." In other words, I'd accept whichever name appears first (and disregard the others) ... anything to prevent sending multiple mailings to the same address. If you have another suggestion, I'd appreciate it. Otherwise, thanks just the same. Dan "Rowan Drummond" wrote: Hi Dan Generally the DISTINCT statement would go immediately after SELECT so: SELECT DISTINCT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST,... But considering that you are using SUM and GROUP BY statements you should only be returning distinct records anyway. One way I can see that you could be getting records with duplicates in I_ADDR1 is if you have more than one person listed at the same address in the IINFO table. If this is the case I guess you would need to decide which name to use. Anyway, hope this helps Rowan crimsonkng wrote: I'm using MS Query within Excel to extract mailing addresses from my SQL database (based on total dollars spent). See the Query, below. I'm getting duplicate addresses because there are multiple records for the same address. I don't want to send multiple letters to the same address so I want to prevent duplicate addresses. But no matter where I place the word "distinct," I get an error message (in Query): "could not add the table." Names and addresses are held in a table called IINFO. Street addresses are in the field called I_ADDR1 within the IINFO table. Any suggestions on how to prevent duplicate street addresses? (BTW ... I previously posted this question to the group but it never appeared ... maybe I didn't wait long enough ... sorry if this is a duplicate question.) Thanks. Dan SELECT Sum(STAY.S_TOTSPENT), IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP FROM (IINFO LEFT JOIN STAY ON STAY.S_IRECID = IINFO.I_RECID) WHERE STAY.S_STATUS = 'HIST' AND STAY.S_ADATE = CONVERT(DateTime,'11/14/2004 00:00:00',101) AND IINFO.I_FIRST Not Like 'Group Master' AND IINFO.I_ADDR1 '1' GROUP BY IINFO.I_FIRST, IINFO.I_LASTNAME, IINFO.I_ADDR1, IINFO.I_CITY, IINFO.I_STATE, IINFO.I_ZIP ORDER BY IINFO.I_ADDR1 ASC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent Duplicate Records | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
In Excel 2003 is there a way to prevent "Save As" and "Print"? | Excel Discussion (Misc queries) | |||
Excel should let me eliminate "duplicate records" in a column | Excel Discussion (Misc queries) | |||
Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming |