Create Input Form for Database with 88 tables (same structure)

[Alortiz]

Hello,



I have a database with 88 tables that have 5 fields each. Each field has the same name in every table. The ideal situation would be to have a single table to hold the data currently stored in the 88 tables but each table holds different information. Each of the 88 tables is related to 3 additional tables that hold header information. Needless to say the header info will be identical for each entry of the 88 tables. Access is limited to 256 fields per form so having all fields in a single form has not worked this far. Splitting the forms into groups of tables somewhat works but the header info needs to be inputed every time and it creates duplicate info. Creating a single form with multiple tabs does not work due to the 256 field limitation. Any suggestions?

Al

[enquiries]

Try creating one main form and then multiple sub-forms in tabs. May I ask what sort of information you're working with? Just curious as to why you need 88 tables with the same field names. Might pay to re-look the design of the tables & relationships.

[orange]

Please tell us more about your tables and fields. Sounds very much like a design problem, but we await your rationale.

[Alortiz]

We tried the single main form with multiple sub-forms but once we reach 256 fields it just freezes. We are creating a temporary data collection system for a building assessment program. Each table contains a section of the building being inspected, the data that we are collecting is: Code Number (unique), rating (0-5), Type, and comments. rating and type are selected and populated from separate tables, code number is programmically populated, and comments are typed in. If we were to use a single table with 4 fields and list all 88 lines (with 5 fields) on a single form the data repeats itself in every field (that is the issue), thus the need for 88 separate tables.

[Alortiz]

It might very well be a design problem. I know that with code I could capture the data on a form and hitting the save button I could save each field into a single table but don't even know where to start :-(. We are trying to develop a temporary data collection system for a Building Assessment Application. On the surface is quite simple. This can be done with an excel spreadsheet but we have inspectors out in the field that need to have a web form to fill out (this is what we are trying to develop). On the spreadsheet we list all the fields and type in the required data. It calculates the totals and that finishes the assessment. On access we can create the form to simulate the access spreadsheet but when we we reach the 256 field limitation it does not allow us to do anything else. Programmically we could create an array and have the system automatically move the data from the field collection into the table but we don't have the talent inhouse to do that.

[June7]

If data 'repeats', sounds like need another table. The 'repeat' data would be a 'parent' (header) record and the ID of this record would be foreign key in 'child' table. Then form/subform arrangement for data entry/edit to synchronize parent and child records.

[Alortiz]

We do have a parent table (header) which pulls data from another system to populate the building information. Then each of the 88 tables is a child table linked to the parent via the ID (unique). The problem is when you relate all tables using this ID then you exceed the 256 field limitation. So then what we have tried (and failed) is to link each of the 88 tables using it's own ID (which should match the ID of the parent table). This seems to work up to a point but then we have sections where we have we reach the 256 limit and then back to square one.

[June7]

Sorry, not making sense to me. Do you want to provide db for analysis? Follow instructions at bottom of my post.

[Alortiz]

Sorry, not making sense to me. Do you want to provide db for analysis? Follow instructions at bottom of my post.

I'm attaching a pdf of the spreadsheet so you may see what we are trying to duplicate. The Header area contains information that is pulled from an external source after we enter the Building name and room number. On the detail area the only information that we are trying to capture is the Code Number, Score (even though in excel is shown as 6 fields, we only need one to capture a score of 0 to 5), Type, and comments. The rest are just labels. Let me know if this clarifies things a bit. Thanks for your help!

[June7]

Doesn't help me understand 88 tables. I see two tables represented in that image. Where are Code Number and Score?

[Alortiz]

I'm attaching a pdf of the spreadsheet so you may see what we are trying to duplicate. The Header area contains information that is pulled from an external source after we enter the Building name and room number. On the detail area the only information that we are trying to capture is the Code Number, Score (even though in excel is shown as 6 fields, we only need one to capture a score of 0 to 5), Type, and comments. The rest are just labels. Let me know if this clarifies things a bit. Thanks for your help!

Aside from the header, each row represents one table. We tried creating the form with only two tables (the header and the detail) but unfortunately when we enter data in the first row it reproduces the entry in all the subsequent fields. As another person said, it might be a design issue, but I just can pin point the issue . That is why we resorted to using a table for each row. In doing so we created another problem which is Access' 256 field limitation . Your help is greatly appreciated!

[June7]

I still don't understand 'reproduces the entry'.

Think I have a db with similar structure. It is to track laboratory samples. Each sample can have an assortment of tests associated but not always the same tests. Tests are different data and therefore different fields. Each test has its own data table because otherwise would exceed 255 fields for all possible tests. Each sample can have only one record in any test tables but will not be in every table. I manage the 255 field limit by use of subforms and subreports.

I am just not understanding the issue as you have described.

[Alortiz]

I still don't understand 'reproduces the entry'.

Think I have a db with similar structure. It is to track laboratory samples. Each sample can have an assortment of tests associated but not always the same tests. Tests are different data and therefore different fields. Each test has its own data table because otherwise would exceed 255 fields for all possible tests. Each sample can have only one record in any test table but will not be in every table. I manage the 255 field limit by use of subforms and subreports.

I am just not understanding the issue as you have described.

I appreciate your patience! I'll create the input screens and post later so you can see how it functions.

[Alortiz]

I appreciate your patience! I'll create the input screens and post later so you can see how it functions.

Attached are the sample dbs. Sample 1 will show how the form is supposed to work but it does not because of the 256 field limit. Sample 2 will show how is it that the records are duplicated by using a single table detail. Please keep in mind that I only included a few examples of each. If I was to add the remaining 80+ tables then it would not work. If I were able to use a single table it would be ideal. Hope this clarifies the issue.

[June7]

CRUD, web database.

Why are you including all the tables in the form RecordSource? Normally a form can enter/edit data for only one table. Use form/subform arrangement. Main form should have only the parent table, not the 'child' tables. Child tables should be in subforms. Each subform on a page of tab control. A subform container control SourceObject can be table, query, form, report. 88 subforms would be a lot.

A Navigation form/control instead of tab control might be better suited (and I don't even like navigation form). If you intend to publish this db to web, might have to use navigation form anyway. I don't know if there is a limit on how many subforms can be associated with a Navigation form.

In my db I mentioned earlier, some samples have dozens of tests that can be associated. During sample login, tests are selected and records are immediately created in test tables. When testing is completed another form is opened for test data entry. Because these records are all 1-to-1 relationship, one query that joins all the relevant test tables can be the RecordSource of form. I have about 200 tests but the tests available for each sample depends on the type of sample. In most cases, a single form serves. A couple of cases results in too many tables for the query so I have to use subform.