How do I group in a query to eliminate doubles and maintain the ID

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have created a query that uses GROUP for the similar fields, a MAX for the
date stamp (in order to identify the last entry), but I want to also include
the unique ID for that record.

If I include the ID in the GROUP, I do not eliminate duplicates. I am
hoping not to create a second query that puts the ID back.

Thank you in advance.

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

Not completely clear what you are trying to do.

Just a guess:

SELECT T.ID, T.DateField
FROM theTable As T
WHERE T.DateField = (SELECT MAX(DateField) FROM theTable
WHERE ID = T.ID)

The correlated subquery gets the latest DateField for the main query's
ID. You can include any column you wish in "theTable As T" in the
main SELECT clause.

There can't be multiple records for one ID for the same date.
(Actually, there can be, but the query may not select the "true"
latest date since 1/1/2000 = 1/1/2000 [which one is the latest? Both
are. Therefore, an arbritary record, of the two, will be selected.]).

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQA7//4echKqOuFEgEQLmqQCfbCK6ybfUr6RzB3gtkd1cewJU0h8AoJld
L/x6WpVWNtC/QLaIZFbpdllx
=GUIH
-----END PGP SIGNATURE-----
 
I couldn't make this work. I kept getting error messages.

The table is [Dup1]

The fields are [ID], [Timestamp], [LName], [Sess]

Each record has a unique ID. The user is to enter a Session [Sess] for each
calendar quarter once.

What I am attempting to do is eliminate duplicate entries for the Session by
[LName] and retain the unique [ID]

I can do via several queries, but I was hoping to eliminate a couple of
queries.

Can you help get me started?

Thanks

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

Not completely clear what you are trying to do.

Just a guess:

SELECT T.ID, T.DateField
FROM theTable As T
WHERE T.DateField = (SELECT MAX(DateField) FROM theTable
WHERE ID = T.ID)

The correlated subquery gets the latest DateField for the main query's
ID. You can include any column you wish in "theTable As T" in the
main SELECT clause.

There can't be multiple records for one ID for the same date.
(Actually, there can be, but the query may not select the "true"
latest date since 1/1/2000 = 1/1/2000 [which one is the latest? Both
are. Therefore, an arbritary record, of the two, will be selected.]).

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQA7//4echKqOuFEgEQLmqQCfbCK6ybfUr6RzB3gtkd1cewJU0h8AoJld
L/x6WpVWNtC/QLaIZFbpdllx
=GUIH
-----END PGP SIGNATURE-----

I have created a query that uses GROUP for the similar fields, a MAX for the
date stamp (in order to identify the last entry), but I want to also include
the unique ID for that record.

If I include the ID in the GROUP, I do not eliminate duplicates. I am
hoping not to create a second query that puts the ID back.

Thank you in advance.

Dave K
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Still not sure what you want, exactly. Is Sess a DateTime data type?
Could you show some example rows of table Dup1 and the result you want
from the query?

If you're trying to prevent the user from entering duplicate records,
where the duplicate record is defined as same LName and same Sess
values, then you should create a Primary Key (PK) using the LName and
Sess columns. This would require the Sess to be unique for each
LName. E.g.:

LName Sess
- ----------------- -------
Smith 1/04
Smith 2/04
Smith 4/04
Jones 2/04
Jones 3/04

The PK would prevent entry of these values, again:

Smith 2/04
Jones 3/04

because they already exist in the table.

Of course, you'll have to delete any duplicates before applying the
PK, and I can't think of any query that will do that (which doesn't
mean there isn't one).


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


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

iQA/AwUBQBA4HoechKqOuFEgEQLsTACfdONMntWN2LaRGbMixVQPa6845h8AnR3B
meCWok1GQijZdyNHfKH+0Z/U
=A0d2
-----END PGP SIGNATURE-----
I couldn't make this work. I kept getting error messages.

The table is [Dup1]

The fields are [ID], [Timestamp], [LName], [Sess]

Each record has a unique ID. The user is to enter a Session [Sess] for each
calendar quarter once.

What I am attempting to do is eliminate duplicate entries for the Session by
[LName] and retain the unique [ID]

I can do via several queries, but I was hoping to eliminate a couple of
queries.

Can you help get me started?

Thanks

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

Not completely clear what you are trying to do.

Just a guess:

SELECT T.ID, T.DateField
FROM theTable As T
WHERE T.DateField = (SELECT MAX(DateField) FROM theTable
WHERE ID = T.ID)

The correlated subquery gets the latest DateField for the main query's
ID. You can include any column you wish in "theTable As T" in the
main SELECT clause.

There can't be multiple records for one ID for the same date.
(Actually, there can be, but the query may not select the "true"
latest date since 1/1/2000 = 1/1/2000 [which one is the latest? Both
are. Therefore, an arbritary record, of the two, will be selected.]).

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQA7//4echKqOuFEgEQLmqQCfbCK6ybfUr6RzB3gtkd1cewJU0h8AoJld
L/x6WpVWNtC/QLaIZFbpdllx
=GUIH
-----END PGP SIGNATURE-----


Dave wrote:

I have created a query that uses GROUP for the similar fields, a MAX for
Click to expand...
the
date stamp (in order to identify the last entry), but I want to also
Click to expand...
include
the unique ID for that record.

If I include the ID in the GROUP, I do not eliminate duplicates. I am
hoping not to create a second query that puts the ID back.

Thank you in advance.

Dave K
Click to expand...
Click to expand...
Click to expand...
 
Back
Top