[thelist] Jet SQL help
Ken Schaefer
Ken at adOpenStatic.com
Sat Jan 22 18:36:09 CST 2005
: -----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
More information about the thelist
mailing list