Thoughts on breaking up main table into several?

[templeowls]

I have a DB full of customer complaints. My backend is SQL and I use Access as a front end. Each complaint is a record in my tblComplaints table. This table probably has 50 to 60 fields for each record.



I then have a form that looks at each complaint individually and allows the user to use 'next' and 'previous' to switch complaints.

When changing records in the form, the 'loading' cursor pops up for a second or two which is kind of annoying. I think because there's a lot of fields for each record and the form isn't filtered down at all. It's control source is the main table.

I am wondering if it would be advantageous to reconstruct my DB and split the main table into six or seven tables.

Ultimately, all that data will still need to be fields in this form so I'm mainly wondering if I should expect significant speed differences.

I know theoretically it should be organized better (I inherited this DB) but I want to ensure there will be some speed benefits too before embarking.

[June7]

There is no database platform called SQL. Do you mean SQLServer?

What are the fields? Are there any repetitious field names?

I doubt form performance would be impacted.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gicu]

The biggest improvement in speed to your form would be to only load one record at the time; I used to put record search subform at the top of the main form, that had a couple of combos for the search fields and a datasheet subform displaying the results (this was based on a local table that had just a few fields used for searching and identification and it was refreshed on database open and on request). And once to selected a record in the datasheet the main form would load it using something like "SELECT ...... FROM dboMyTable WHERE PK_ID = Forms!frmMyForm!sfrmSearch..... (reference to the selected record in datasheet). The results were loaded almost instantaneous and the main table had over 100 fields (it was a international student database with lots of information per record).

Cheers,

[NoellaG]

50 or 60 fields aren't that much. If the form isn't filtered, all records are retrieved from the database when loading the form. So loading the next or previous record should be fast once the recordset is loaded into the form. The following could slow-down de process:
* having lookup fields in the table
* using domain functions or other complex calculated fields on the form
* loading pictures or other object fields
* using subforms
* slow code in the on current event