Database of Like Tables?

[DWEvans]

I am designing a DB that I started with a table that has 27 records and 17 fields. Then I had to make 27 Tables that have the same 17 fields. The reason for the 27 tables is because I need to track the Record History of each time a Record is saved for that Table. I started my Relationships by showing the Primary Key of Table 1 related to a common field in all 27 of the other fields which corresponds to the ID of Table 1. I am trying to get a Form to add data to 16 fields in the 27 individual tables? I have tried to use Queries and always get the error that I am including too many fields for the wizard to use?

Any help will be grateful!

[CJ_London]

think you are going about this all wrong - google 'audit trails' or similar. There are a number of different ways of tracking changes - which is the right one depends on the reason why yoy you are tracking changes and what you want to do with the information.

As a start look at this link http://allenbrowne.com/AppAudit.html

[DWEvans]

It is too hard to explain all the complexities of the Database on here, which is why I need to chat with someone one on one, without exposing all my info to the world on an Open Forum! BUT I am NOT Tracking Changes! I NEED a database of Inspections completed on 27 say Locations with the same 17 fields of entry! These Inspections will be varying in time lapses IAW a combination of the field entries. The Last Entry is in fact "Next Due" which will be different for each of the 27 Tables. I just need to find the correct relationships which will allow me to set up a form to Log the Entries to be posted to the 16 fields on 27 different tables, If ACCESS can handle it?

Thanks again for any help offered! But if available I'd like to have an actual conversation? I'd like to establish an email link, followed by a Phone Link if possible?

[CJ_London]

Sorry, I'm not in a position to help in this way. Suggest provide a sample db with some sample data and I or someone else can help. Alternatively look to pay for the resource - perhaps someone local to you?

[orange]

You may want to review this data model which is quite generic. Some pieces may not apply to your situation.

You should at least post your table designs so readers get some understanding of what you are doing. Readers are not interested in your data, but if it is confidential/proprietary, then make up 10 records with Porky Pig from Smallville etc, etc.

You do NOT need 27 tables.

You should work through this tutorial from RogersAccessLibrary - you will learn about table design and relationships that can be applied to your own situation.

It is too hard to explain all the complexities of the Database

If you can't describe it, nobody can build it.

Good luck.

[HiTechCoach]

I am designing a DB that I started with a table that has 27 records and 17 fields. Then I had to make 27 Tables that have the same 17 fields. The reason for the 27 tables is because I need to track the Record History of each time a Record is saved for that Table. I started my Relationships by showing the Primary Key of Table 1 related to a common field in all 27 of the other fields which corresponds to the ID of Table 1. I am trying to get a Form to add data to 16 fields in the 27 individual tables? I have tried to use Queries and always get the error that I am including too many fields for the wizard to use?

Any help will be grateful!

I do this a lot with only two tables.

Your method sounds like a spreadsheet not a Relational Database.

In a well designed and properly normalized relational database you should rarely, if ever, have multiple tables with the same exact same structures (fields). 27 identical tables can easily be a single table with one additional field needed to identify the "group".

Ponder this:

It sounds like you have created a new table for each of the 27 records. If that is correct, what happens when you add record 28? Does that mean you will need to create a new table? If yes, then the design is not correct. When designed correctly adding a record in a table will not requite a new table to be created.

[DWEvans]

yes, in fact the design could be a Spreadsheet! But I am using Access for the simple use of a form to easily update the database. There will NEVER be a Record 28! In essence we will call the 27 records "Buildings" that get inspected. One table for each building! The tables have say 14 items about the inspection, Start Time, End Time, number of inspectors, next inspection due, etc. The database is kept so we can see the history of the inspections! ACCESS Should be able to Input to the tables from one form if the relationships are set right? I just need to figure how to set up the relationships? Maybe start simple, how to Update two tables from one form? I have started MINI db TO SHARE SHOWING THE CONCEPT!

[orange]

Sounds like this

Building---->Inspection<-----Inspector

Here is a research paper on Field Inspection that may have some info you can use.

[HiTechCoach]

yes, in fact the design could be a Spreadsheet! But I am using Access for the simple use of a form to easily update the database. There will NEVER be a Record 28! In essence we will call the 27 records "Buildings" that get inspected. One table for each building! The tables have say 14 items about the inspection, Start Time, End Time, number of inspectors, next inspection due, etc. The database is kept so we can see the history of the inspections! ACCESS Should be able to Input to the tables from one form if the relationships are set right? I just need to figure how to set up the relationships? Maybe start simple, how to Update two tables from one form? I have started MINI db TO SHARE SHOWING THE CONCEPT!

I find it best to always expect that things will change.

In a spreadsheet you would make the 27 tabs, with each tab named the building. Most people do this because a spreadsheet does not have any way to query the data for a single building if was all on a single worksheet. . In a database this would all be in a single table with an additional field for building. Now you can you a query to get all the records for a single building.

In a database you enter the data with format in a relational design of multiple tables. ,. You use queries and report to reformat the data are needed need. The forms do not have to, and often don't, look like the reports. With a database you can print he same data in many different layouts. You can has as many forms as need to handle data entry to match the workflow. Same for report. With a spreadsheet you have a single layout that is how it will print. You are forces to enter the data in that format.

This may help: http://www.access-diva.com/dm18.html