Too Many Fields

[ddeering]

I have a table that has 99 fields. I am trying to create a form but am getting an error message saying that I have too many fields. Is there a way around this? I need all fields and cannot delete any.


Thank you!

[matthewspatrick]

It's pretty unusual to need 99 fields on a single table; usually, if a table has more than about a dozen fields, that's a sign that the design is flawed.

Why do you need so many fields?

[ddeering]

I am tracking performance of employees via a checklist, as well as points for an internal promotion. We have a lot of products and each one has a minimum of 4 entries each for tracking purposes. I originally did the tracking in two separate tables, but for efficiency I wanted to combine them. There must be a way!!

[matthewspatrick]

I am tracking performance of employees via a checklist, as well as points for an internal promotion. We have a lot of products and each one has a minimum of 4 entries each for tracking purposes. I originally did the tracking in two separate tables, but for efficiency I wanted to combine them. There must be a way!!

Actually, the multi-table approach is probably the right one. I am not privy to all the details of what you need to track, but based on your last post, this might be a possible schema:

tblEmployees
--------------------------------------------
EmpID (PK)
EmpLName
EmpFName
PosID (FK)
<other employee attributes>

tblPositions
--------------------------------------------
PosID (PK)
PosCode
PosTitle
<other position attributes>

tblProducts
--------------------------------------------
ProdID (PK)
ProdCode
ProdDescr
<other product attributes>

tblTrackingPoints
--------------------------------------------
TrackingID (PK)
TrackingDescr
<other attributes>

tblProductTracking
--------------------------------------------
ProdTrackID (PK)
ProdID (FK)
TrackingID (FK)

tblEmpTracking
--------------------------------------------
EmpID (PK, FK)
ProdTrackID (PK, FK)
Period (PK)
Score



This is just a start, and again, you have not given enough info for it to be definitive. Generally speaking, you are better off breaking up your data into more, narrower, and normalized tables because they do a better job of coping with future changes in needs and they make searching and querying much easier.

[ddeering]

It is really inefficient for me to have more than one table, as I mentioned. Here is what I am doing. Let's say we have 20 products. In one form I want to be able to list the customer name, employee name, date, team number, each product, whether each product was discussed, sold, existing, and enter the number of points associated with selling the product. I want my form to be set up similarly to the format of the checklist we use to write the information onto. Here is a small example.

Date
Team #


Customer Name Employee Name Branch

Discussed Sold Existing Points

_______ _____ _______ _______ Checking Account
_______ _____ _______ _______ Savings Account
_______ _____ _______ _______ ATM/Debit Card


and so on. I have the field formated for yes/no for the Discussed, Sold and Existing columns and a number format for points. So, as you can see, it does not make sense to have several tables for this. Any other advice?

[matthewspatrick]

Without seeing your database and getting into a detailed discussion of business requirements, I can say categorically that you are better of with a multi-table schema. One form using sub-forms can handle what you are trying to do.

In fact, it is much better. Under a schema similar to what I propose, you are able to add or remove products without having to change the form, the subforms, or the schema. If you try to do it all in one table, then any time you add or remove a product, you have to edit the form and the schema. That is bad design.

[ddeering]

Thanks, Patrick. I will attempt to follow your suggestions, although I am not very good at Access beyond the basics, but I'll try to figure it out. Thank you for your help!!

[iball1113]

The form is used to enter data to a table. Perhaps you need two or three tables that are related via a query. First, however, make sure you do not have redundancy in the 99-field-table.

[Missinglinq]

Just so that you know, matthewspatrick's suggestion doesn't simply reflect his opinion, it's what any experienced Access developer is going to tell you! What you're describing/trying to do is create a Spreadsheet, not a Relational Database, a not uncommon mistake with newbies.

Linq ;0)>

[June7]

I agree, use multiple related tables and form/subform arrangement for data entry/edit. I do think it is a balancing act between normalization and ease of date entry/edit, but what you describe screams for more normalization. If you want to have greatest flexibility for expanding the product selection, normalize the data structure.

BTW, table/query field limit is 255. The limit for number of controls on form/report is far greater than 99, so something else was wrong.

[ssanfu]

The limit for number of controls on form/report is far greater than 99, so something else was wrong.

From: http://office.microsoft.com/en-us/ac...010341462.aspx

"Number of controls and sections you can add over the lifetime of the form or report...... 754"

[Missinglinq]

As we all know, the Error Messages the Access Gnomes throw up are frequently not appropriate to the problem at hand. There is a limit as to how big a single Record can be, and with 99 Fields it's possible that this is the actual problem.

Linq ;0)>