[thelist] Jet SQL help

Maximillian Schwanekamp lists at neptunewebworks.com
Sat Jan 22 11:43:49 CST 2005


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.

  (Name is a reserved word in Jet SQL, so you need to
> use [] around the field name. I would usually recommend a more descriptive
> field name, eg Username or CustomerName or similar.

Yes indeed.  I've been given this data to normalise and make something 
of it while keeping it in Access (I would much prefer MSSQL/MSDE).  But 
I spose I coulda just changed the column name...

-- 
Maximillian Von Schwanekamp
Dynamic Websites and E-Commerce
NeptuneWebworks.com <http://www.neptunewebworks.com/>
voice: 541-302-1438
fax: 208-730-6504




More information about the thelist mailing list