Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form from Multi Tables (2000+)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form from Multi Tables (2000+)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form from Multi Tables (2000+)

    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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form from Multi Tables (2000+)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form from Multi Tables (2000+)

    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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form from Multi Tables (2000+)

    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.
    Attached Files Attached Files

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Form from Multi Tables (2000+)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form from Multi Tables (2000+)

    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •