Top Two Items per customer

[sg]

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!

 

[Tokyo Alex]

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.

 

[Tokyo Alex]

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.

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.

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!

 

[John Spencer]

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

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.

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.

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!

 

[sg]

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!!

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

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.

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.


:

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!

.

 

[sg]

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?

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.

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!

 

[John Spencer]

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

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?

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.

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!

 

[Tokyo Alex]

Hi John,

Thanks for the correction.

I always seem to forget about nonupdateable queries

Cheers,
Alex.

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

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.

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.


:

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!

.

 

[sg]

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?

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

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?

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.


:

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!

.

 

[Tokyo Alex]

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.

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?

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

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?

:

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.


:

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!

.

 

[sg]

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

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.

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?

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?

:

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.


:

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!
.

 

[Tokyo Alex]

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

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.

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?
:

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?

:

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.


:

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!
.

 

[sg]

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

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.


:

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?
:

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?

:

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.


:

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!
.

 

[Tokyo Alex]

Glad to hear you got it working

Cheers,
Alex.

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

:

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.


:

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?
:

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?

:

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.


:

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!
.

 

[sg]

I do have one more question. Can I incorporate a message box into the last
query (the one that unchecks the field [Active] if the last 2 of 3 are
unacceptable)? I would like the user to be made aware of the fact that this
item is no longer active.

Thanks in advance!

Glad to hear you got it working

Cheers,
Alex.

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.


:

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

:

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.


:

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?
:

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?

:

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.


:

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!
.

 

[Tokyo Alex]

Hi,

You can't launch a message box from a query, but if you're running the
queries from code you could do something like:

'*****VBA Code Fragment*****
Dim strSQL As String
Dim db As DAO.Database: Set db = DBEngine(0)(0)

strSQL = "<Query string goes here>"

db.Execute strSQL, dbFailOnError

MsgBox db.RecordsAffected & " records were set to inactive.",
vbOKOnly+vbInformation, "Results"

Set db = Nothing
'*****End Code Fragment*****

That way you can let the users know exactly what you want to let them know.

Cheers,
Alex.

I do have one more question. Can I incorporate a message box into the last
query (the one that unchecks the field [Active] if the last 2 of 3 are
unacceptable)? I would like the user to be made aware of the fact that this
item is no longer active.

Thanks in advance!

Glad to hear you got it working

Cheers,
Alex.

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

:

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.


:

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

:

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.


:

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?
:

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?

:

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.


:

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!
.

 

[sg]

Thanks for your help on that.

Unfortunately, I now seem to have a problem with my original question.

Its strange, though, because I have tested it over and over and can't seem
to figure out what is going on.

Sometimes it works where the checkbox is unchecked and sometimes it doesn't.
One thing I know for sure is that if I just have 2 instances where the
status is unacceptable, it does not update. However, sometimes when I have
more than 3 items and then end up with 2 of the last 3 being unacceptable,
the checkbox does not change. When I go back through all the queries, it
appears that everything is set up ok - I am seeing the records I expected
to. The only thing that seems a little strange is that I get the messages
about the update query running almost everytime I add a record even if the
addition doesn't warrant the checkbox being unmarked. Why would that run
even if there is nothing for it to update?

The macro I set up just opens the update query, which runs the query, and
then closing the update query. I set it to run on the On Change event.

If you see anything that might possibly be wrong, I would be happy to test
it out and see if I can find anything else to make it work.

Thanks!

Hi,

You can't launch a message box from a query, but if you're running the
queries from code you could do something like:

'*****VBA Code Fragment*****
Dim strSQL As String
Dim db As DAO.Database: Set db = DBEngine(0)(0)

strSQL = "<Query string goes here>"

db.Execute strSQL, dbFailOnError

MsgBox db.RecordsAffected & " records were set to inactive.",
vbOKOnly+vbInformation, "Results"

Set db = Nothing
'*****End Code Fragment*****

That way you can let the users know exactly what you want to let them know.

Cheers,
Alex.

I do have one more question. Can I incorporate a message box into the last
query (the one that unchecks the field [Active] if the last 2 of 3 are
unacceptable)? I would like the user to be made aware of the fact that this
item is no longer active.

Thanks in advance!

Glad to hear you got it working

Cheers,
Alex.


:

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

:

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.


:

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

:

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.


:

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?
:

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?

:

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.


:

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!
.

 

[Tokyo Alex]

Hi,

First thoughts:
The Change event of a control fires every the control's contents are
changed, even by a single keystroke, and regardless of wether the changes are
committed to the underlying query or table field. So it's probably not the
best place to put your query-running macro.

The best place depends on your exact design needs, but a good bet would be
the AfterUpdate event of the *form*, not a control. That way it will fire
when a record (new or edited) is saved. Alternatively, you could add a
command button and have the event fire on user demand.

Possible issues with the queries:
Correct fields? - A no brainer, I know, but it needs to be said.
Does each, individual query and subquery return the results you expect?
If so, open the UPDATE query and switch to datasheet view. Does it return
the correct number of records.
If not, do you have any fields containing Null values that may be causing
problems?

One thing I've just thought of:
The UPDATE query will set all records with 2 or more unacceptable ratings to
False, *even if it's already false*. This may well be why you're getting the
'Action Query' warnings even when you think you shouldn't be. You could add
something like:
AND [Cust Site Item Junction tbl].[active] = True
to the WHERE clause to make Access ignore records that have alread been set
to false and eliminate this problem.

I suppose that there's also the possibility of an error in your Macro.
Unfortunately, I never use Macros for anything, so I won't be able to provide
much help for you on that score.

Hope that this nudges your troubleshooting in the right direction.

If you need any more help, please post the full SQL of all the queries
(including your [test2] initial query), and the structure and datatypes of
your tables and fields.

Hope you get it going,
Alex.

Thanks for your help on that.

Unfortunately, I now seem to have a problem with my original question.

Its strange, though, because I have tested it over and over and can't seem
to figure out what is going on.

Sometimes it works where the checkbox is unchecked and sometimes it doesn't.
One thing I know for sure is that if I just have 2 instances where the
status is unacceptable, it does not update. However, sometimes when I have
more than 3 items and then end up with 2 of the last 3 being unacceptable,
the checkbox does not change. When I go back through all the queries, it
appears that everything is set up ok - I am seeing the records I expected
to. The only thing that seems a little strange is that I get the messages
about the update query running almost everytime I add a record even if the
addition doesn't warrant the checkbox being unmarked. Why would that run
even if there is nothing for it to update?

The macro I set up just opens the update query, which runs the query, and
then closing the update query. I set it to run on the On Change event.

If you see anything that might possibly be wrong, I would be happy to test
it out and see if I can find anything else to make it work.

Thanks!

Hi,

You can't launch a message box from a query, but if you're running the
queries from code you could do something like:

'*****VBA Code Fragment*****
Dim strSQL As String
Dim db As DAO.Database: Set db = DBEngine(0)(0)

strSQL = "<Query string goes here>"

db.Execute strSQL, dbFailOnError

MsgBox db.RecordsAffected & " records were set to inactive.",
vbOKOnly+vbInformation, "Results"

Set db = Nothing
'*****End Code Fragment*****

That way you can let the users know exactly what you want to let them know.

Cheers,
Alex.

I do have one more question. Can I incorporate a message box into the last
query (the one that unchecks the field [Active] if the last 2 of 3 are
unacceptable)? I would like the user to be made aware of the fact that this
item is no longer active.

Thanks in advance!

:

Glad to hear you got it working

Cheers,
Alex.


:

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

:

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.


:

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

:

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.


:

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?
:

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?

:

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.


:

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!
.

 

[sg]

Once again, you have come through for me. I changed it to run on the After
update event of the form and it seems to be perfect now. I'll do a little
more testing, but it is definitely working better than before! Thanks again
for your awesome help and for spending so much time on this!

Hi,

First thoughts:
The Change event of a control fires every the control's contents are
changed, even by a single keystroke, and regardless of wether the changes are
committed to the underlying query or table field. So it's probably not the
best place to put your query-running macro.

The best place depends on your exact design needs, but a good bet would be
the AfterUpdate event of the *form*, not a control. That way it will fire
when a record (new or edited) is saved. Alternatively, you could add a
command button and have the event fire on user demand.

Possible issues with the queries:
Correct fields? - A no brainer, I know, but it needs to be said.
Does each, individual query and subquery return the results you expect?
If so, open the UPDATE query and switch to datasheet view. Does it return
the correct number of records.
If not, do you have any fields containing Null values that may be causing
problems?

One thing I've just thought of:
The UPDATE query will set all records with 2 or more unacceptable ratings to
False, *even if it's already false*. This may well be why you're getting the
'Action Query' warnings even when you think you shouldn't be. You could add
something like:
AND [Cust Site Item Junction tbl].[active] = True
to the WHERE clause to make Access ignore records that have alread been set
to false and eliminate this problem.

I suppose that there's also the possibility of an error in your Macro.
Unfortunately, I never use Macros for anything, so I won't be able to provide
much help for you on that score.

Hope that this nudges your troubleshooting in the right direction.

If you need any more help, please post the full SQL of all the queries
(including your [test2] initial query), and the structure and datatypes of
your tables and fields.

Hope you get it going,
Alex.

Thanks for your help on that.

Unfortunately, I now seem to have a problem with my original question.

Its strange, though, because I have tested it over and over and can't seem
to figure out what is going on.

Sometimes it works where the checkbox is unchecked and sometimes it doesn't.
One thing I know for sure is that if I just have 2 instances where the
status is unacceptable, it does not update. However, sometimes when I have
more than 3 items and then end up with 2 of the last 3 being unacceptable,
the checkbox does not change. When I go back through all the queries, it
appears that everything is set up ok - I am seeing the records I expected
to. The only thing that seems a little strange is that I get the messages
about the update query running almost everytime I add a record even if the
addition doesn't warrant the checkbox being unmarked. Why would that run
even if there is nothing for it to update?

The macro I set up just opens the update query, which runs the query, and
then closing the update query. I set it to run on the On Change event.

If you see anything that might possibly be wrong, I would be happy to test
it out and see if I can find anything else to make it work.

Thanks!

Hi,

You can't launch a message box from a query, but if you're running the
queries from code you could do something like:

'*****VBA Code Fragment*****
Dim strSQL As String
Dim db As DAO.Database: Set db = DBEngine(0)(0)

strSQL = "<Query string goes here>"

db.Execute strSQL, dbFailOnError

MsgBox db.RecordsAffected & " records were set to inactive.",
vbOKOnly+vbInformation, "Results"

Set db = Nothing
'*****End Code Fragment*****

That way you can let the users know exactly what you want to let them know.

Cheers,
Alex.


:

I do have one more question. Can I incorporate a message box into the last
query (the one that unchecks the field [Active] if the last 2 of 3 are
unacceptable)? I would like the user to be made aware of the fact that this
item is no longer active.

Thanks in advance!

:

Glad to hear you got it working

Cheers,
Alex.


:

I'm so excited I can barely speak! You are a genius. Thank you soooo much
for alll your help. I got it and it works great and you have no idea how
much stress this takes off of me!

Thank you sooooo much!

:

Is it possible (I suspect it is) that one company can be linked to more than
one PT ID?

The query as written returns records for [company], [PT Date] and
[Accept/Not Accept] if the [PT ID] for those records is in the top 3 *for
that [PT ID]* by date in [test2].

If [PT ID] does not relate 1:1 with [company] (which it looks like it
doesn't) then you could return any number of companies, depending on how many
[PT ID]s each company has.

Something like:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN
(SELECT TOP 3 [PT ID] FROM [test2] AS D
WHERE D.[company] = [test2].[company]
ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

should work, but it may depend on your [test2] query and the structure of
the underlying tables.

The logic here is that the subquery returns the three latest [PT ID]s for a
given company, X, from the current record in the main query. If the record's
[PT ID] is in the top 3 list, that record is returned.

I think this will work; if it doesn't, post back and I'll take another shot.

Cheers,
Alex.


:

Thanks. I'll give that a try...

I did find one other thing that I need help with.

In the first query you gave me that is supposed to pull the top 3, I am
getting all of the records regardless of the date.

I am using:

SELECT [company], [PT Date], [Accept/Not Accept]
FROM test2
WHERE [PT ID] IN (SELECT TOP 3 [PT ID] FROM [test2] AS D WHERE D.[PT
ID] = [test2].[PT ID] ORDER BY D.[PT Date] DESC, D.[PT ID] DESC)
ORDER BY [company], [PT Date];

In the results, I get:

Atlas Thread Gage, Inc. 3/25/2010 Not Acceptable
CMG Sales, Inc. 3/22/2009 Not Acceptable
CMG Sales, Inc. 8/25/2009 Acceptable
CMG Sales, Inc. 12/21/2009 Not Acceptable
CMG Sales, Inc. 3/28/2010 Not Acceptable
Polymer Concentrates, Inc.12/25/2009 Not Acceptable

CMG Sales should be only returning 3 records with the dates 3/28/10,
12/21/09 and 8/25/09.

What am I doing wrong?

P.S. I am using a query as the basis for this query instead of a table
since the Company was in a different table than the rest of the data.

:

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.


:

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?
:

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?

:

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