Max objects

[teiben]

I have a large spreadsheet that I want to move into a database. There are in one table 125 fields. I broke down the form into 2 data entry screens, since I knew there was a limit to the number of controls on a form. I was hoping there wasn't a limit on reports, but there appears to be, other than breaking a report down into smaller sections any ideas? How many objects can you have in access report version 97?

 

[boblarson]

First thing I need to ask is why are there 125 fields in a table? If you follow database normalization rules it is highly unlikely that you would have that many. It all starts with your database design as to how easy it is to do other things. If you don't design it correctly then you will be plagued with trying to come up with "workarounds" for problems that wouldn't exist if it were designed properly.

It would seem that the approach you've taken is in thinking in "spreadsheet" terms, in other words short and wide. You should be thinking in relational database terms of long and narrow.

So, yes you've found out the hard way that there are limits to what you can do with forms and reports. I would highly suggest rethinking your design and breaking your table up to normalize and make it manageable.

If you would be able to list your fields in the table, we might be able to make some suggestions as to how you might go about this.

 

[teiben]

Reason

The db is a plant summary, so for each dept. and process (hr, quality, delivery) there are 10 fields. The problem isn't the table, since I can store 125 vallues, but the reports they want to display like its' being display now, which is in excel, with a date on top and ALL plants and field displayed in rows, as well as a Month to Date column. The month to date column I can do in a subreport and insert it.
Any idea's

 

[boblarson]

Actually, your table IS the problem as you should have a table that has something like:
tblDepts
DeptID Primary Key
HR
Quality
Delivery
etc.

You should not have ANY repeating fields. If you have more than one column for HR, Quality, Delivery, etc. then it should be broken up. I'm sorry but you don't seem to understand that what you are asking for is NOT, NOT, NOT something that Access is meant to be able to do. As Simon Cowell would say, "I'm not being rude, but..." all of the Access MVP's would also agree with me on this one. You can't set up Access to be a spreadsheet and expect to be able to use it easily. It isn't built for that. If you want to do what you want to do, then link an Excel spreadsheet to your Access "database" (loosely applied here) and use Excel.

 

[teiben]

Thank you for the feedback. I took your advise and broke down the tables. (See attached). I will have the problem, in reporting. This Db will have to accomodate many plants, and each plant has different processes (molding, paint, etc.)

I was looking for ideas as to a work around for the reporting. It seems like it will have to be broken out into the area(s).