[thelist] Jet SQL help

[Ken Schaefer]

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On
: Behalf Of Maximillian Schwanekamp
: Sent: Sunday, 23 January 2005 4:44 AM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] Jet SQL help
: 
: Ken Schaefer wrote:
: > SELECT
: > 	DISTINCT a.[Name]
: > 	, a.Email
: > FROM
: > 	tblOrders
: > give you what you want?
: 
: That was my first try, but no that does not do it - in my haste to put
: up the question to the list, I made the example too simplistic - in the
: real data, the names vary as well:
: tblOrders
: OrderID    Name         Email               Amount
: 1000       John Doe     john at example.com      5.00
: 1001       Jane Doe     jane at example.com     12.00
: 1002       John A. Doe  john at example.com     11.00
: 1003       José Doe     jose at example.net     78.00
: 1004       Jose Doe     jose at example.net     13.50
: 
: Using DISTINCT in this case returns all rows.  Only one column is
: consistent (email).
: 
: What I ended up doing was to create a new table, and insert
: into...select(fields) using email as primary key on the new field.
: Access ignored the redundant rows.  It worked for my purpose, but I'm
: still wondering how to do this properly.

I'm not sure what you mean by "doing it properly". I think you need to tell
us what you want the output to look like. If you just want a list of distinct
email addresses, then:

SELECT
	DISTINCT a.Email
FROM
	tblOrders

but if you want to bring other data across (the Name field) you'll need to
tell us what rule you want to use for eliminating one (or more) of the
alternate values. In the case of the data above:

1000       John Doe     john at example.com      5.00
1002       John A. Doe  john at example.com     11.00

which Name would you eliminate? John Doe or John A. Doe?

Cheers
Ken