Select a table to display within a form - Accesss 2016

[SandyC]

Admittedly I am new to Access. I have 11 tables that contain for the most part the same fields. I want create a form that the users will select the table they want to display and update (no new records). The user will update only one table at a time. Do I need to use a navigation form, a control, or a macro to do this?

[orange]

Hmmm. I suggest you write a brief description --5 or so lines of simple English - no jargon - telling us about the "business" this database is intended to support. When several of your initial tables have overlapping fields, it raises a "red flag" saying something may be amiss. Get your description together and let readers comment on possible refinements, tables, relationships etc. But starting with 11 similar tables suggests a rethink and review.
Welcome to the forum and good luck with your project.

[SandyC]

I thought I might get this comment! So I am tracking certificates of insurance (COI). We have 11 clients. Each client has between 7 to 15 vendors. Account Managers are tracking the COI expiration date of each vendor, currently in their own spreadsheets. The goal is to move tracking to a central and consistent system. My thoughts are to create a table for each client that will list their vendors and COI exp date for about 8 lines of coverage (fields). Does this help?

[Micron]

Sorry to say your plan is quite wrong. What you really must do first is figure out a properly normalized schema but that will likely require a bit of study on your part. If you do not grasp db normalization (at least the fundamentals) you will have a great struggle and it is quite likely that you'll invest a lot of time in something that becomes untenable. All that time will go down the tubes when you realize you'll have to start over and that wasted time will far exceed the time you could have spent learning. Perhaps you are one of those who are very very adept with Excel and make the fatal mistake of designing a db as if it were a spreadsheet. I made up an analogy last week that to me seemed better than the old hammer/screwdriver thing when it comes to Excel vs Access. One is like scissors, the other like a knife. They're both used for cutting but you would never use scissors to carve a roast would you? Nor should you create a db with an Excel mindset. I'm just going to throw everything I have at you and let you choose what to look at, but I recommend you read up on all these subjects. With Access it is too easy to create a poorly designed db.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

Somebody might chime in with a first cut at what tables you might need but I prefer to let you do your research because it might prompt you to rethink how you view the process.

EDIT - forgot to say that in regard to your original question, what you wanted to do is quite possible and is frequently done. It's just not done on what are virtually duplicates of tables.

[SandyC]

Micron -
Thank you for your time to share all this with me. I will begin my study!! Hopefully I can have this under my belt by the end of Dec. No pressure LOL. But I want to at least try to do it right. Thanks again.

[orange]

SandyC,
In response to your post #3--- sort of.
But we are not in the insurance business(at least not the majority of us who will read your posts), so clarity is key.
So there are Clients and Vendors and Account Managers. You track COI(certificates of Insurance).
There are 11 Clients.
Each Client has 7-15 Vendors. And there are about 8 lines of coverage.

How about a brief description of each of these things and how each is related to another.

It will help with identifying your tables and any normalization.

Here is an example of the level of detail for description (from RogersAccessLibrary).

Narrative

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.

An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

[Micron]

In addition to Orange's comments, a common approach is to sketch out a schema on paper once you think you understand normalization. When you've finished erasing and redrawing, you can post that narrative based on your understanding and get feedback. Or you can go ahead and build the tables, set the relationships and post a pic of the relationships along with that narrative. Responders can then vet your schema.

[orange]

SandyC,
You may find some of the sample data models here helpful,