[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