Top Two Items per customer

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!

Sorry, the first query is mistaken. It should be:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTClientID] = [PT tbl].[PTClientID]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date] DESC;

Also, I forgot to add ";" to the end of all the queries.

Cheers,
Alex.


"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!

One other change. You will probably get an error message on the last query
saying you must use an updateable query.

Try this modification

UPDATE [scope tbl]
SET [check fld]
WHERE scope tbl].[ClientID] in
(SELECT qdfNGClients.[PTClientID]
FROM qdfNGClients)

You can use an aggregate query in the WHERE clause of an update query. You
normally cannot use it in the JOIN clause.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tokyo Alex wrote:
Sorry, the first query is mistaken. It should be:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTClientID] = [PT tbl].[PTClientID]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date] DESC;

Also, I forgot to add ";" to the end of all the queries.

Cheers,
Alex.


"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!

Thanks to both of you for your quick response. I am a fairly knowledgable
Access user so this will be easy for me to implement...I'm sure I can figure
out any bugs that may come up. I just was at a total loss for where to even
start. Thank you so much!!

"John Spencer" wrote:

One other change. You will probably get an error message on the last query
saying you must use an updateable query.

Try this modification

UPDATE [scope tbl]
SET [check fld]
WHERE scope tbl].[ClientID] in
(SELECT qdfNGClients.[PTClientID]
FROM qdfNGClients)

You can use an aggregate query in the WHERE clause of an update query. You
normally cannot use it in the JOIN clause.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tokyo Alex wrote:
Sorry, the first query is mistaken. It should be:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTClientID] = [PT tbl].[PTClientID]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date] DESC;

Also, I forgot to add ";" to the end of all the queries.

Cheers,
Alex.


"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!
.

Question: When I run the query, I get no results back, but I definitely have
data that should come up. I see in my original post that I did not state
that the Client ID is from the Cust tbl and not in the PT tbl. However, the
two tables are related to each other in a many-to-many relationship with the
Item junction tbl. Will that make a difference?

"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!

You never even mentioned a second table. Yes it could make a difference.

At this point we don't know what queries you have run or what they look like.

It would help, if you posted the table structure (at least of the fields
involved) and the query or queries you have tried. And a short description of
the results returned - no records, expected records, unexpected records.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

sg wrote:
Question: When I run the query, I get no results back, but I definitely have
data that should come up. I see in my original post that I did not state
that the Client ID is from the Cust tbl and not in the PT tbl. However, the
two tables are related to each other in a many-to-many relationship with the
Item junction tbl. Will that make a difference?

"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!

Hi John,

Thanks for the correction.

I always seem to forget about nonupdateable queries

Cheers,
Alex.


"John Spencer" wrote:

One other change. You will probably get an error message on the last query
saying you must use an updateable query.

Try this modification

UPDATE [scope tbl]
SET [check fld]
WHERE scope tbl].[ClientID] in
(SELECT qdfNGClients.[PTClientID]
FROM qdfNGClients)

You can use an aggregate query in the WHERE clause of an update query. You
normally cannot use it in the JOIN clause.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tokyo Alex wrote:
Sorry, the first query is mistaken. It should be:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTClientID] = [PT tbl].[PTClientID]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date] DESC;

Also, I forgot to add ";" to the end of all the queries.

Cheers,
Alex.


"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!
.

Thank you so much for your help so far!

I took some time to dig in and figured out how to get the rest to work, and
it seems to be working so far. The only problem I am having now is the final
Update qry. I have the following:

UPDATE [Cust Site Item Junction tbl]
SET [active]
WHERE [Cust Site Item Junction tbl].[cust_num] in
(SELECT qdfNGClients.[cust_num]
FROM qdfNGClients);

I wasn't sure if I was missing something or what I did wrong...when I try to
run it, I get the message Syntax error in Update Statement. When I click on
Ok, it highlights the word Where. I looked up the error but all I found was
that it said I might be missing a part of the statement or that it might be
misspelled.

Any ideas?
"John Spencer" wrote:

You never even mentioned a second table. Yes it could make a difference.

At this point we don't know what queries you have run or what they look like.

It would help, if you posted the table structure (at least of the fields
involved) and the query or queries you have tried. And a short description of
the results returned - no records, expected records, unexpected records.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

sg wrote:
Question: When I run the query, I get no results back, but I definitely have
data that should come up. I see in my original post that I did not state
that the Client ID is from the Cust tbl and not in the PT tbl. However, the
two tables are related to each other in a many-to-many relationship with the
Item junction tbl. Will that make a difference?

"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!
.

Hi again,

After a quick test, it seems Access won't let you omit the value you want to
set the field to. It failed with exactly the same error as you report.

So, you'll need:
UPDATE [Cust Site Item Junction tbl]
SET [active] = 0
WHERE [Cust Site Item Junction tbl].[cust_num] in
(SELECT qdfNGClients.[cust_num]
FROM qdfNGClients);

This will set the [active] field to FALSE, use SET [active] = -1 to set it
to true.

Again, this assumes that it's a Yes/No field; if not, SET [active] = some
appropriate value.

Cheers,
Alex.


"sg" wrote:

Thank you so much for your help so far!

I took some time to dig in and figured out how to get the rest to work, and
it seems to be working so far. The only problem I am having now is the final
Update qry. I have the following:

UPDATE [Cust Site Item Junction tbl]
SET [active]
WHERE [Cust Site Item Junction tbl].[cust_num] in
(SELECT qdfNGClients.[cust_num]
FROM qdfNGClients);

I wasn't sure if I was missing something or what I did wrong...when I try to
run it, I get the message Syntax error in Update Statement. When I click on
Ok, it highlights the word Where. I looked up the error but all I found was
that it said I might be missing a part of the statement or that it might be
misspelled.

Any ideas?
"John Spencer" wrote:

You never even mentioned a second table. Yes it could make a difference.

At this point we don't know what queries you have run or what they look like.

It would help, if you posted the table structure (at least of the fields
involved) and the query or queries you have tried. And a short description of
the results returned - no records, expected records, unexpected records.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

sg wrote:
Question: When I run the query, I get no results back, but I definitely have
data that should come up. I see in my original post that I did not state
that the Client ID is from the Cust tbl and not in the PT tbl. However, the
two tables are related to each other in a many-to-many relationship with the
Item junction tbl. Will that make a difference?

"Tokyo Alex" wrote:

Hi,

You can do this with queries.

This is untested air-SQL, so use at your own risk g.

First you need the latest three PT records per client:
SELECT [PTClientID], [PT Date], [PT Status]
FROM [PT tbl]
WHERE [PTKeyField] IN
(SELECT TOP 3 [PTKeyField]
FROM [PT tbl] AS D
WHERE D.[PTKeyField] = [PT tbl].[PTKeyField]
ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC)
ORDER BY [PTClientID], [PT Date]

Save this query (e.g. as qdfTopThree), and then use a totals query to find
any clients with 2 or more 'Unacceptable' statuses:
SELECT [PTClientID], Count([PT Status]) AS StatusCount
FROM qdfTopThree
WHERE [PT Status] = "Unacceptable"
GROUP BY [PTClientID]
HAVING Count([PT Status]) = 2

Once again, save the query (qdfNGClients) and then - after backing up your
database - use an UPDATE query to set your check field:
UPDATE [scope tbl] INNER JOIN qdfNGClients
ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID]
SET [check fld] = 0

Caveat: Once again, this is untested code; please ensure you have a backup
of your database before doing this!
Also, no guarantee that this is the most effective, most elegant, or
quickest method.

Assumptions:
PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID
You have a Client ID to link PT tbl and Scope tbl.
Your check field is a Yes/No field.

Hope this helps (or at least points you in the right direction),
Alex.


"sg" wrote:

I have a table called PT tbl. If a client has a status of Unacceptable (a
field in the table) for two out of the three most recent dates (PT Date
field), I need to find them and run a macro that will uncheck a box in
another table (scope tbl).

I am not sure if I need a macro only or if I need a query to first pull
those who are the top two out of three or where to start here. I did get a
query put together to show the top two, but it was just the top two dates out
of the entire client list, not the top two per client.

Any help out there? Thank you in advance for any one who will help!
.