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