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 (3) |
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