Problem with Unique Records

[Van T. Dinh]

I am not sure about your "Web" but in (stand-alone) Access, see the methods
in The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

 

[John Vinson]

People make entries into my Access database via my website. Each time they
do, they enter their email address and the relevant information. A date and
time is automatically entered by the table. Wanting to email everyone who
has made an entry in the database, I created a query and set the Unique
Values property to "Yes". However, this only seems to work when I use the
email address in the query.

I would like include other information for the email merge, such as date and
time they submitted their entry, and the title field of the entry to use in
the disclaimer. However, when I add those fields into the query, it gives me
all the records instead of only one record per email address.

If the date is different for every record, then the records ARE
distinct. If there are 8 records with the same EMail address, and each
has a different date/time, what do you want to see in this single
record? You've got eight different records; Select Distinct is (quite
properly!) seeing them as distinct records!

It sounds like you may want to create a Report with a grouping by
email address and a detail section containing all the orders from that
address; would that meet your needs?

John W. Vinson[MVP]

 

[magmike]

People make entries into my Access database via my website. Each time they
do, they enter their email address and the relevant information. A date and
time is automatically entered by the table. Wanting to email everyone who
has made an entry in the database, I created a query and set the Unique
Values property to "Yes". However, this only seems to work when I use the
email address in the query.

I would like include other information for the email merge, such as date and
time they submitted their entry, and the title field of the entry to use in
the disclaimer. However, when I add those fields into the query, it gives me
all the records instead of only one record per email address.

Can someone help me, and I have I been clear?

Thanks,
Mike

 

[John Vinson]

No, a report would not meet my needs. I need an exportable file for merging.
I only want to see one record per email address, and I don't really care
which one.

Then create a Totals query, grouping by address, and use First() as
the aggregate function for the date (or any other variable fields
which you don't care which you see).

John W. Vinson[MVP]

 

[magmike]

No, a report would not meet my needs. I need an exportable file for merging.
I only want to see one record per email address, and I don't really care
which one.

Mike

--
MEDICATION ASSISTANCE PROGRAM
Qualifying applicants get their medication free.
It's easy to qualify. Use the qualification wizard at
http://www.rexaid.com

 

[John Vinson]

Great, thanks! Does that have the same effect as using "Max" as someone else
suggested?

Not exactly the same effect: Max returns the largest value, after
looking at all of the values in the group; First returns the first one
it comes to and doesn't even examine the rest of the group. So it's
basically arbitrary which one you get, but it can be faster.

John W. Vinson[MVP]

 

[magmike]

Great, thanks! Does that have the same effect as using "Max" as someone else
suggested?

 

[magmike]

Sounds like First() is the winner. Thanks.

--
MEDICATION ASSISTANCE PROGRAM
Qualifying applicants get their medication free.
It's easy to qualify. Use the qualification wizard at
http://www.rexaid.com