My apologies John, I am probably not using the correct terminology.
Rather than naming the target of the script (Catalog01) in the script, I would prefer to run the script against a table I would currently have open. OR if the script could prompt me for the table name.
Also, what might I do with the WHERE clause? A flag of some sort? Turning the descriptions with no matches to red would easily identify them but I don't believe that is possible.
It does matter because any description that did not have a match in the master would need to be addressed. I will append the descriptions in the table back to the master list after the other descriptions are addressed. Eventually my master list of descriptions would be complete except for newly added items. Does that make sense?
Mark
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, February 24, 2017 3:14 PM
Subject: Re: [MS_AccessPros] Re: Newbie: Lookup and populate from table
Mark-
Please explain what you mean by "open, or active, table".
You could add a WHERE clause to the JOIN solution, but does it really matter?
UPDATE Catalog01 INNER JOIN Master ON Master.ItemNumber = Catalog01.ItemNumber
SET Catalog01.Description = Master.Description
WHERE Catalog01.Description <> Master.Description;
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Feb 24, 2017, at 10:11 PM, Mark thgirblam@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Thank you John! Both options work perfectly, but I am using the JOIN option.
Is it possible to run the script against the open, or active, table instead of naming a specific catalog in the script?
Also, is there a simple solution for the script to distinguish items in the catalog with updated descriptions from those that were not updated?
Mark
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, February 24, 2017 9:51 AM
Subject: Re: [MS_AccessPros] Re: Newbie: Lookup and populate from table
Mark-
As long as the item number is unique in the Master table, you should be able to do:
UPDATE Catalog01 INNER JOIN Master ON Master.ItemNumber = Catalog01.ItemNumber
SET Catalog01.Description = Master.Description;
OR
UPDATE Catalog01
SET Catalog01.Description =
(SELECT Description FROM Master WHERE Master.ItemNumber = Catalog01.ItemNumber)
WHERE EXISTS (SELECT * FROM Master WHERE Master.ItemNumber = Catalog01.ItemNumber);
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Feb 24, 2017, at 4:40 PM, Mark thgirblam@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I'm not sure what you mean by "justification for updating the records in all tables". I don't need to update the records in all tables. The description field only needs to be updated to the description field of the matching item number in the master list, on newly created (from a copy) tables. Existing tables are archival only. My intent is to create a query/script/action that can be run on demand from the switchboard, on newly created tables.
The item number is a primary key in the master table. The item number is not unique in the other tables.
Mark
From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, February 24, 2017 2:34 AM
Subject: Re: [MS_AccessPros] Re: Newbie: Lookup and populate from table
I'm not sure you have provided enough justification for updating the records in all tables. I expect you can link to the table with the good descriptions and use them.
Is the Item Number a primary key in all tables? I believe it would need to be unique in at least the master table?
Regards,
Duane Hookom
Vevey, Switzerland
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Mark thgirblam@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 23, 2017 3:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Newbie: Lookup and populate from table
Sent: Thursday, February 23, 2017 3:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Newbie: Lookup and populate from table
Thank you Doyce,
I have been experimenting with update queries and have not figured out how to update a field in one table from a field in another table.
For more background: The database is part of a tool used to create catalogs. Each table is a unique version of the catalog with columns that contain item numbers, descriptions and other identifying data, and rows that distinguish pages and chapter. Sort order of these tables is critical but that has been automated.
This system is recognized as a being a less than desirable solution and a completely new tool is being developed to create these catalogs. It will be many more months before that development is complete and even longer before we are completely out of the old system. What I have been tasked with in the interim, is finding a method to update item descriptions in any given table, from a master list - but only if/when the item numbers match.
I can create a relationship between the item number in a catalog table with the item number field in the master table. But I have not been able to create an update query that will update from a different table in the manner required.
Mark
From: "winberry.doyce@con-way.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, February 15, 2017 4:01 PM
Subject: [MS_AccessPros] Re: Newbie: Lookup and populate from table
Mark,
I can't imagine why you would want or need all of these tables, especially if they contain the same data. Your tables are not normalized. A switchboard is an easy way to create a menu system. It will only open forms and run commands that have been previously created. I can't see how you could use one to copy and update data from one table to another that you have copied and renamed unless you gave the tables some predefined names. However, if you have created a master table and want to update descriptions in another table, there has to be a common key field between the two tables such as a part number. If there is, you can construct an update query that will copy the descriptions from your master table to the new one that you have created. Do some research on update queries. For specific help, ask another question and one of the professionals here will chime in to assist. You have ask for very broad help and without more info, it is impossible to be of more assistance. VBA is a great tool but I don't think you need it to run an update query.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <thgirblam@...> wrote :
I've had advanced training in Access but have been away from it for several years. Currently I am working in Access 2013 with a DB that is several years old and consist of many flat tables (7,000 - 10,000 records per table) with no established relationships. The tables are lists of items with item numbers and item descriptions as well as other pertinent information. When a new project is started, the user will choose one of the flat tables (based on closest match of content), copy the table and rename it, then delete non-relevant entries and add relevant content. All of the fields in the table are fully editable by the user which of course, leads to inconsistencies.
I am not interested in redesigning the system. That project would be too overwhelming and the entire system will be replaced in 2-3 years anyhow, but I am trying to find a way to apply some consistency in this system. I have created a "Master List" of item numbers and their corresponding "approved" item description. When a user is done creating a new table I would like to run a process that creates a copy of the table, then deletes all the item descriptions and replaces them with the item description from the master list - leaving item descriptions blank that were not found in the master list.
To avoid creating relationships in my huge archive of flat tables I believe the practical solution is to use VBA in a switchboard function to accomplish this task. I'm going through "Programming in Microsoft Access 2013 and VBA" video series on Youtube and have been quite consumed with the deluge of information. My hope is that there is either a better way to approach this issue or someone here might point me to specific VBA tutorial information more generalized to the task at hand. That is to say, I don't want to learn everything there is to know about VBA if I can find a solution without doing so.
Thank you for all advice.
Mark
I am not interested in redesigning the system. That project would be too overwhelming and the entire system will be replaced in 2-3 years anyhow, but I am trying to find a way to apply some consistency in this system. I have created a "Master List" of item numbers and their corresponding "approved" item description. When a user is done creating a new table I would like to run a process that creates a copy of the table, then deletes all the item descriptions and replaces them with the item description from the master list - leaving item descriptions blank that were not found in the master list.
To avoid creating relationships in my huge archive of flat tables I believe the practical solution is to use VBA in a switchboard function to accomplish this task. I'm going through "Programming in Microsoft Access 2013 and VBA" video series on Youtube and have been quite consumed with the deluge of information. My hope is that there is either a better way to approach this issue or someone here might point me to specific VBA tutorial information more generalized to the task at hand. That is to say, I don't want to learn everything there is to know about VBA if I can find a solution without doing so.
Thank you for all advice.
Mark
__._,_.___
Posted by: Mark <thgirblam@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar