[thelist] Jet SQL help

Ken Schaefer Ken at adOpenStatic.com
Sat Jan 22 23:30:20 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 1:18 PM
: To: thelist at lists.evolt.org
: Subject: Re: [thelist] Jet SQL help
: 
: Ken Schaefer wrote:
: <snip>
: > 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?
: 
: Good question.  In this case, since presumably all "name" entries that a
: customer used to make an order are valid (they entered it themselves
: after all), the first one would be fine and probably easiest.
: 
: By "properly" I meant in contrast to what I did end up doing, which was
: to create new table with email as primary key, and then used
: insert...select to populate the table and discarded the primary
: key-violating rows when Access prompted me.  It worked, but it felt
: dissatisfying.  :)

Something like:

SELECT
	MIN(a.UserName)
	, a.UserEmail
FROM
	Users AS a
GROUP BY
	a.UserEmail

will select the first user name (sorted alphabetically) for each unique email
address.

The query is a bit of a "hack" though. You tend to see that when your data
isn't normalised. When data is normalised, the queries tend to write
themselves.

Cheers
Ken


More information about the thelist mailing list