Form from Multi Tables (2000+)

Topic

New Reply

Hello – I have a database that for historical (read: hysterical) reasons used one table.

Now the number of fields has grown to about 190 and although this is unwieldy when creating or modifying the form has been acceptable. To reduce the chance of the dreaded error message about too many fields I decided to split the table into five tables. Then I redesigned the query that was the basis for sorting certain fields on the form.

When I created a new form based on the new query I cannot see any fields on the form. Even the query when run shows no data.

Any suggestions? I suspect it has something to do with relationships but have been unable to fix the problem.

TIA, Leigh

Reply | Quote

Replies

A table can have 255 fields. Although I would start thinking about a drastic redesign long before I reached that number, a table with 190 fields is not a problem in itself. Just splitting the table into severall tables with a one-to-one relationship is not an improvement – on the contrary, it makes things more difficult.

If your query returns no records, the tables it’s based on probably haven’t been joined correctly; you wouldn’t have this problem if you stored the data in a single table.
Without knowing how you set up the tables and query, it is impossible to give a more detailed answer.

Reply | Quote

Thanks for your reply Hans

I had a bit of a play – as you do – and new db attached seems to work.

However, any comments on the relationships would be appreciated.

BTW, it currently only has one extra table but the others will be along the same lines as tblResults.

TIA, Leigh

Reply | Quote

The way it’s set up now, there can be several tblResult records corresponding to the same tblStudents record. If that is what you intended, you should create a main form based on tblStudents and a subform based on tblResults, linked on tblStudentID vs StudentID. Other tables would correspond to other subforms – you could use a tab control with a page for each subform to keep things manageable.

Reply | Quote

Good – using a tabbed form was the way I was going to go in view of the large number of fields. It was getting messy using using code to hide various year levels on the original form and a devil when any form field needed a minor adjustment.

Thanks again Hans

Reply | Quote

Regretably, despite good advice from Hans, my skills are falling short on this one.

Can someone tell me more about the sort of relationships that should be established for multiple tables to work as I intended?

I.e., the form will be for data input and a query is used to enable the form to be created. Is that clear or make sense? The tables are going to be 7 in number (incl. the main table) and there will be way too many fields required in each to have a single table. The form will be multi-tabbed using the tab control as I have laboured previously with a form that had multiple controls placed one over another and using code to hide or display the relevant control.

Attached is the non-working version where I have played with relationships.

Any assistance to resolve my dilemma will be appreciated.

Reply | Quote

Why so many tables?
What are the tables holding?
As Hans noted you should use subforms on a main form.

Reply | Quote

In your new sample database, the tables tblP-Y2, tblY3-Y5 and tblY6-Y7 have the same structure, so there is no need to have three tables. You can combine them into one table, with an extra field to identify the type of record (year level?), if necessary.
The way you’re doing it now is not an improvement over one table, since you’re including all fields from all tables in the query. A query cannot have more than 255 fields, just like a table, so you’d be getting stuck at exactly the same point.
I have attached a very plain version of a form with a subform based on one of the results tables.

Reply | Quote