how to use "distinct" to prevent duplicate records

[Guest]

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

 

[Rowan Drummond]

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

 

[Guest]

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]

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