[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

__._,_.___

---

Posted by: winberry.doyce@con-way.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

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.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___