How to ignore records with a duplicate ID based on a value

[Wiley]

I hae searched the forum tono avail, so I'll ask for help.

I have a table [Scope Event Table] which records events with status changes.
The table uses an auto numbered primary key (not shown in the example
below). It has data like this:

DR_ID Event_Date Reason
12556 01/03/2008 Added
12556 01/24/2008 Closed
12874 01/05/2008 Added
14128 02/09/2008 Added

I am a novice at SQL. I am trying to build a query that would pull only the
DR_ID’s where the most recent Reason = “Addedâ€. So I would want results to
look like this:

DR_ID Event_Date Reason
12874 01/05/2008 Added
14128 02/09/2008 Added

Thanks in advance for your assistance.

 

[Allen Browne]

Firstly, open the table in design view, and make a unique index on the
combination of DR_ID + Event_Date, so there cannot be duplicates. (Use the
Indexes box on the toolbar.)

Now create this query:
SELECT [Scope Event Table].DR_ID,
Max([Scope Event Table].[Event_Date]) AS MostRecent,
(SELECT Reason from [Scope Event Table] AS Dupe
WHERE Dupe.DR_ID = [Scope Event Table].DR_ID
AND Dupe.Event_Date = Max([Scope Event Table].[Event_Date]))
AS LastReason
FROM [Scope Event Table]
GROUP BY DR_ID;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

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

 

[Stockwell43]

In your query, the column that supports Added and Closed put "Added" in the
criteria field. When you run the query it should only show the records with
Added in the column and omit Closed.

 

[John Spencer]

I think the following might work for you.

SELECT S.DR_ID, S.Event_Date, S.Reason
FROM [Scope Event Table] as S
WHERE S.Event_Date =
(SELECT Max(Event_Date)
FROM [Scope Event Table] as Dupe
WHERE Dupe.DR_ID = S.DR_ID)
AND S.Reason = "Added"

In Design view
-- Add your table
-- add your field
-- Under Reason set the criteria to
= "Added"
-- Under Event_Date set the criteria to
= (SELECT Max(Event_Date) FROM [Scope Event Table] as Dupe WHERE
Dupe.DR_ID =[Scope Event Table].DR_ID)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Wiley]

Allen,

Thansk for your response. I changed the primary key as you instructed and
pasted in your query in SQL window. When I run it I get "Canot have
aggregate function in WHERE clause...", and it puts the entier Where clause
in the error message.

Firstly, open the table in design view, and make a unique index on the
combination of DR_ID + Event_Date, so there cannot be duplicates. (Use the
Indexes box on the toolbar.)

Now create this query:
SELECT [Scope Event Table].DR_ID,
Max([Scope Event Table].[Event_Date]) AS MostRecent,
(SELECT Reason from [Scope Event Table] AS Dupe
WHERE Dupe.DR_ID = [Scope Event Table].DR_ID
AND Dupe.Event_Date = Max([Scope Event Table].[Event_Date]))
AS LastReason
FROM [Scope Event Table]
GROUP BY DR_ID;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I hae searched the forum tono avail, so I'll ask for help.

I have a table [Scope Event Table] which records events with status
changes.
The table uses an auto numbered primary key (not shown in the example
below). It has data like this:

DR_ID Event_Date Reason
12556 01/03/2008 Added
12556 01/24/2008 Closed
12874 01/05/2008 Added
14128 02/09/2008 Added

I am a novice at SQL. I am trying to build a query that would pull only
the
DR_ID’s where the most recent Reason = “Addedâ€. So I would want results
to
look like this:

DR_ID Event_Date Reason
12874 01/05/2008 Added
14128 02/09/2008 Added

Thanks in advance for your assistance.

 

[Wiley]

John,

Thanks so much for your response. I never knew about the 'Dupe" feature.
It works great! Woudl it be good insurance to add the ORDER BY clause belwo
to force a record order?

ORDER BY [Scope Event Table].DR_ID, [Scope Event Table].Event_Date;

I think the following might work for you.

SELECT S.DR_ID, S.Event_Date, S.Reason
FROM [Scope Event Table] as S
WHERE S.Event_Date =
(SELECT Max(Event_Date)
FROM [Scope Event Table] as Dupe
WHERE Dupe.DR_ID = S.DR_ID)
AND S.Reason = "Added"

In Design view
-- Add your table
-- add your field
-- Under Reason set the criteria to
= "Added"
-- Under Event_Date set the criteria to
= (SELECT Max(Event_Date) FROM [Scope Event Table] as Dupe WHERE
Dupe.DR_ID =[Scope Event Table].DR_ID)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I hae searched the forum tono avail, so I'll ask for help.

I have a table [Scope Event Table] which records events with status
changes.
The table uses an auto numbered primary key (not shown in the example
below). It has data like this:

DR_ID Event_Date Reason
12556 01/03/2008 Added
12556 01/24/2008 Closed
12874 01/05/2008 Added
14128 02/09/2008 Added

I am a novice at SQL. I am trying to build a query that would pull only
the
DR_ID's where the most recent Reason = "Added". So I would want results
to
look like this:

DR_ID Event_Date Reason
12874 01/05/2008 Added
14128 02/09/2008 Added

Thanks in advance for your assistance.

 

[John Spencer]

You can add the order by if you want to display the records in that order in
a query or on a form.

If you want to use the records returned in a report, don't bother to add an
order by clause here. Use the reports Sorting and Grouping dialog (View:
Sorting and Grouping) to set the sort order.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks so much for your response. I never knew about the 'Dupe" feature.
It works great! Woudl it be good insurance to add the ORDER BY clause
belwo
to force a record order?

ORDER BY [Scope Event Table].DR_ID, [Scope Event Table].Event_Date;

I think the following might work for you.

SELECT S.DR_ID, S.Event_Date, S.Reason
FROM [Scope Event Table] as S
WHERE S.Event_Date =
(SELECT Max(Event_Date)
FROM [Scope Event Table] as Dupe
WHERE Dupe.DR_ID = S.DR_ID)
AND S.Reason = "Added"

In Design view
-- Add your table
-- add your field
-- Under Reason set the criteria to
= "Added"
-- Under Event_Date set the criteria to
= (SELECT Max(Event_Date) FROM [Scope Event Table] as Dupe WHERE
Dupe.DR_ID =[Scope Event Table].DR_ID)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I hae searched the forum tono avail, so I'll ask for help.

I have a table [Scope Event Table] which records events with status
changes.
The table uses an auto numbered primary key (not shown in the example
below). It has data like this:

DR_ID Event_Date Reason
12556 01/03/2008 Added
12556 01/24/2008 Closed
12874 01/05/2008 Added
14128 02/09/2008 Added

I am a novice at SQL. I am trying to build a query that would pull
only
the
DR_ID's where the most recent Reason = "Added". So I would want
results
to
look like this:

DR_ID Event_Date Reason
12874 01/05/2008 Added
14128 02/09/2008 Added

Thanks in advance for your assistance.

 

[Wiley]

OK. Thanks for the advice.

You can add the order by if you want to display the records in that order in
a query or on a form.

If you want to use the records returned in a report, don't bother to add an
order by clause here. Use the reports Sorting and Grouping dialog (View:
Sorting and Grouping) to set the sort order.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks so much for your response. I never knew about the 'Dupe" feature.
It works great! Woudl it be good insurance to add the ORDER BY clause
belwo
to force a record order?

ORDER BY [Scope Event Table].DR_ID, [Scope Event Table].Event_Date;

I think the following might work for you.

SELECT S.DR_ID, S.Event_Date, S.Reason
FROM [Scope Event Table] as S
WHERE S.Event_Date =
(SELECT Max(Event_Date)
FROM [Scope Event Table] as Dupe
WHERE Dupe.DR_ID = S.DR_ID)
AND S.Reason = "Added"

In Design view
-- Add your table
-- add your field
-- Under Reason set the criteria to
= "Added"
-- Under Event_Date set the criteria to
= (SELECT Max(Event_Date) FROM [Scope Event Table] as Dupe WHERE
Dupe.DR_ID =[Scope Event Table].DR_ID)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I hae searched the forum tono avail, so I'll ask for help.

I have a table [Scope Event Table] which records events with status
changes.
The table uses an auto numbered primary key (not shown in the example
below). It has data like this:

DR_ID Event_Date Reason
12556 01/03/2008 Added
12556 01/24/2008 Closed
12874 01/05/2008 Added
14128 02/09/2008 Added

I am a novice at SQL. I am trying to build a query that would pull
only
the
DR_ID's where the most recent Reason = "Added". So I would want
results
to
look like this:

DR_ID Event_Date Reason
12874 01/05/2008 Added
14128 02/09/2008 Added

Thanks in advance for your assistance.