Hi, I have a list of date/times people visited a clinic stored in a
query. I'm trying to write another query based on it to filter a list
of unique visitors and the last date they attended. I'm having a lot
of trouble with duplicate data.

SELECT *
FROM attendeesSearchVisits AS V
WHERE [Visit Date] =
(SELECT MAX([Visit Date])
FROM attendeesSearchVisits
WHERE ID=V.ID);

This query works but returns duplicates if there were 2 visits by one
person on the same day, which annoyingly, is often the case. How can I
tweak it to only show unique IDs from attendeesSearchVisits? I've
tried using group by but I can't get it working!

Thanks,
Ciarán

Group by Attendee and get max date


"Cron" <cronoklee (AT) hotmail (DOT) com> wrote

Hi, I have a list of date/times people visited a clinic stored in a
query. I'm trying to write another query based on it to filter a list
of unique visitors and the last date they attended. I'm having a lot
of trouble with duplicate data.

SELECT *
FROM attendeesSearchVisits AS V
WHERE [Visit Date] =
(SELECT MAX([Visit Date])
FROM attendeesSearchVisits
WHERE ID=V.ID);

This query works but returns duplicates if there were 2 visits by one
person on the same day, which annoyingly, is often the case. How can I
tweak it to only show unique IDs from attendeesSearchVisits? I've
tried using group by but I can't get it working!

Thanks,
Ciarán

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put all the columns you want in the SELECT clause. Try DISTINCT or
DISTINCTROW:

SELECT DISTINCT <column names>
SELECT DISTINCTROW <column names>

Or this:

SELECT ID, Max([Visit Date]) As Latest_Visit
FROM attendeesSearchVisits AS V
GROUP BY ID

If you still get "duplicates," then obviously, one of the column values
is different in the "duplicate."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXo5h4echKqOuFEgEQIxIQCgtyUWxzaC+lbVpuyoE2AjGd TQM1YAnA4k
P8uNgVEo6EBIThNqfUDhLreu
=QOyu
-----END PGP SIGNATURE-----


Cron wrote:

On Jan 23, 9:41*pm, MGFoster <m... (AT) privacy (DOT) com> wrote:
Thanks for the reply. I tried:
SELECT ID, Forename, Surname, Max([Visit Date]) As Latest_Visit
FROM attendeesSearchVisits AS V
GROUP BY ID

And got:
"You tried to execute a query that does not include the specified
expression"

I also tried using the table name in front of each column and got a
prompt for each one?!
Wy does mySQL on the web always work easier than mySQL in access?

Ciarán

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to include all the non-aggregate columns in the GROUP BY
clause:

SELECT ID, Forename, Surname, Max([Visit Date]) As Latest_Visit
FROM attendeesSearchVisits AS V
GROUP BY ID, Forename, Surname

Access uses JET SQL. MySQL is a separate product/application. There
are many different relational database systems (which is what Access/JET
and MySQL are) and each has its own SQL brand - though they use most of
the standard SQL reserved words and syntax.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXpGaIechKqOuFEgEQKKwgCeN7b/Tqvi8ylW0N0flI4ev3IYsAQAn2gG
9pxKSJ+rFv/M2LfdkeI4JY0P
=gYpH
-----END PGP SIGNATURE-----

Cron wrote:

On Jan 23, 10:36*pm, MGFoster <m... (AT) privacy (DOT) com> wrote:



Hey thanks a lot MG - obviously I've a bit to learn about the group by
syntax! This query works perfectly until I try to include a binary yes/
no field in the mix. When I include it, I get a message saying "No
current record". Can you shed any light?

Ciarán

On Jan 23, 10:36*pm, MGFoster <m... (AT) privacy (DOT) com> wrote:

Hi I'm still stuck with this issue. Can anyone help me with these yes/
no fields?
Thanks,
Ciarán

On Jan 26, 3:13*pm, Cron <cronok... (AT) hotmail (DOT) com> wrote:

HI Figured this one out - There's a problem with access that doesn't
allow group by yes/no fields.
Solution is to use Int(columnName). This converts all true values to
-1 and all false values to 0.

Hope this helps someone else!
Ciarán