If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Top Two Items per customer
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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#12
|
|||
|
|||
Top Two Items per customer
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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#13
|
|||
|
|||
Top Two Items per customer
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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#14
|
|||
|
|||
Top Two Items per customer
Glad to hear you got it working
Cheers, Alex. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#15
|
|||
|
|||
Top Two Items per customer
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! "Tokyo Alex" wrote: Glad to hear you got it working Cheers, Alex. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#16
|
|||
|
|||
Top Two Items per customer
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. "sg" wrote: 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! "Tokyo Alex" wrote: Glad to hear you got it working Cheers, Alex. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#17
|
|||
|
|||
Top Two Items per customer
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! "Tokyo Alex" wrote: 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. "sg" wrote: 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! "Tokyo Alex" wrote: Glad to hear you got it working Cheers, Alex. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#18
|
|||
|
|||
Top Two Items per customer
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. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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! "Tokyo Alex" wrote: Glad to hear you got it working Cheers, Alex. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID You have a Client ID to link PT tbl and Scope tbl. Your check field is a Yes/No field. Hope this helps (or at least points you in the right direction), Alex. "sg" wrote: I have a table called PT tbl. If a client has a status of Unacceptable (a field in the table) for two out of the three most recent dates (PT Date field), I need to find them and run a macro that will uncheck a box in another table (scope tbl). I am not sure if I need a macro only or if I need a query to first pull those who are the top two out of three or where to start here. I did get a query put together to show the top two, but it was just the top two dates out of the entire client list, not the top two per client. Any help out there? Thank you in advance for any one who will help! . |
#19
|
|||
|
|||
Top Two Items per customer
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! "Tokyo Alex" wrote: 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. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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! "Tokyo Alex" wrote: Glad to hear you got it working Cheers, Alex. "sg" wrote: 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! "Tokyo Alex" wrote: 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. "sg" wrote: 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. "Tokyo Alex" wrote: Hi again, After a quick test, it seems Access won't let you omit the value you want to set the field to. It failed with exactly the same error as you report. So, you'll need: UPDATE [Cust Site Item Junction tbl] SET [active] = 0 WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); This will set the [active] field to FALSE, use SET [active] = -1 to set it to true. Again, this assumes that it's a Yes/No field; if not, SET [active] = some appropriate value. Cheers, Alex. "sg" wrote: Thank you so much for your help so far! I took some time to dig in and figured out how to get the rest to work, and it seems to be working so far. The only problem I am having now is the final Update qry. I have the following: UPDATE [Cust Site Item Junction tbl] SET [active] WHERE [Cust Site Item Junction tbl].[cust_num] in (SELECT qdfNGClients.[cust_num] FROM qdfNGClients); I wasn't sure if I was missing something or what I did wrong...when I try to run it, I get the message Syntax error in Update Statement. When I click on Ok, it highlights the word Where. I looked up the error but all I found was that it said I might be missing a part of the statement or that it might be misspelled. Any ideas? "John Spencer" wrote: You never even mentioned a second table. Yes it could make a difference. At this point we don't know what queries you have run or what they look like. It would help, if you posted the table structure (at least of the fields involved) and the query or queries you have tried. And a short description of the results returned - no records, expected records, unexpected records. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County sg wrote: Question: When I run the query, I get no results back, but I definitely have data that should come up. I see in my original post that I did not state that the Client ID is from the Cust tbl and not in the PT tbl. However, the two tables are related to each other in a many-to-many relationship with the Item junction tbl. Will that make a difference? "Tokyo Alex" wrote: Hi, You can do this with queries. This is untested air-SQL, so use at your own risk g. First you need the latest three PT records per client: SELECT [PTClientID], [PT Date], [PT Status] FROM [PT tbl] WHERE [PTKeyField] IN (SELECT TOP 3 [PTKeyField] FROM [PT tbl] AS D WHERE D.[PTKeyField] = [PT tbl].[PTKeyField] ORDER BY D.[PT Date] DESC, D.[PTKeyField] DESC) ORDER BY [PTClientID], [PT Date] Save this query (e.g. as qdfTopThree), and then use a totals query to find any clients with 2 or more 'Unacceptable' statuses: SELECT [PTClientID], Count([PT Status]) AS StatusCount FROM qdfTopThree WHERE [PT Status] = "Unacceptable" GROUP BY [PTClientID] HAVING Count([PT Status]) = 2 Once again, save the query (qdfNGClients) and then - after backing up your database - use an UPDATE query to set your check field: UPDATE [scope tbl] INNER JOIN qdfNGClients ON [scope tbl].[ClientID] = qdfNGClients.[PTClientID] SET [check fld] = 0 Caveat: Once again, this is untested code; please ensure you have a backup of your database before doing this! Also, no guarantee that this is the most effective, most elegant, or quickest method. Assumptions: PT tbl has a Primary Key [PTKeyField] which is NOT the Client ID |
|
Thread Tools | |
Display Modes | |
|
|