I am helping a freind develop a DB for housing inspections.
They currently have about 40 fields. I have added about 60 combo boxes with 4 - 6 choices and about 60 memo boxes. I will need about 40 more. When I went to save the table I got a memory error #3309. Although it appears to have saved everything i'm wondering if I am pushing the envelope here. Can someone tell me what the limitations are in a table & the best way to achieve what I am trying to do.

Thanks

JGC

Why do you need 60 memo fields, are you developing an encyclopaedia?

You don't say whether you are in AC97 or AC2K. This makes a difference in what you look up in the Help Files.

In AC97, look up database limits, table limits, query limits, or just look up limits and follow some links around.

In AC2K, look up database specifications, table specifications, etc. or just look up specifications.

If you have 60 memo fields that are populated, you will have a limit of about 30-33 characters each (depending on the size of other fields that AREN'T memos). This is because Access places a limit of 2000 bytes per record in AC97 and (I think) AC2K.

That is, to put it bluntly, too darn many memo fields. You will never make that work exactly like you want it to. Break this table up into at least two or three parts, each of which has the same identifying primary key. Then you can make a one-to-one relationship among the tables.

When you split the tables, try to decide which fields logically should stay together based on likely usage. If your answer really is "All of them must be together" then it is quite possible that Access isn't the product for you.

Or, if some of the fields are descriptive in nature and the descriptions can, in part, be very repetitive, perhaps you should consider storing descriptions once and linking them to your base table with a description code number.

You need to change your approach somewhat. You have a many-to-many relationship between properties and inspection points. The first table will hold the property information. Owner, address, etc. The second table will hold the inspection point descriptions. The third table is a junction table that uses both the propertyID and InspectionPoint as its primary key. This junction table will also contain additional information regarding the outcome of the inspection. So you will need a result field - with at least three possible values, pass, failed, null (or incomplete) and a memo field for comments.

This structure is pretty simplistic and you may need a more sophisticated solution depending on your business rules. For example, if an inspection may be done multiple times, you'll probably need a fourth table that defined an inspection. This table will have a primary key that is an autonumber and a foreign key to relate to the property table. It would include inspection date and if the same person does all the work, you would include an inspectorID so you could tell who did the inspection and when. Then the junction table would connect the Inspection table with the inspection points table rather than directly connecting the property to the inspection points. If each point may be inspected by a different person, at a different time, the date/time and inspector need to go in the junction table rather than the inspector table.

Lots to think about. But without knowing the business rules, it is impossible to suggest a complete solution.

You do need to do some reading on relational database design since your first attemt violates first normal form.

Pat-

I hope that you'll devote a chapter in your book to overcoming the Excel/spreadsheet mindset.

Given 40 fields, 60 combo boxes, 60 memo fields, with the prospect of even more--it's just mind-boggling, but yet something we see time and time-again!

Apparently, if one's initial automation exposure is via spreadsheet, it tends to inhibit future learning.

Bob

Pat:
Thank you for the help, I am working with Access 2K, I created the table that tracks all of the property & inspection information such as tenant name, owners name, address, inspection date, inspector, ect: aprox 43 fields with an auto # primary key. I also created the needed queries & forms to sort the info and send out the many letters associated with the busuness rules. We also created results reports published on a web site for the Housing Authorities to use and an ASP that collects request for new inspections wich is dumped into the table via FTP. I have had alot of fun doing this project and was just starting to think I new a little bit about what I was doing, oops. Just recently I was asked we could add the fields contained in a standard Section 8 Inspection form, ie: Living Room Walls, Living Room Floors, Living Room Electric, ect: aprox. 60 combo boxes with 4-6 choices and a comment field for each. I tried to put theese on the same table but got a memory error, so I tried to copy the fields to a new table and link the tables together. My new table has an auto # ID Primary key and the fields for the Section 8 info. I tried forming a relationship between my original table's ID# and my new table's ID# but none of the records show up on my new table. Im starting to feel stupid.

Originally posted by Pat Hartman
You need to change your approach somewhat. You have a many-to-many relationship between properties and inspection points. The first table will hold the property information. Owner, address, etc. The second table will hold the inspection point descriptions. The third table is a junction table that uses both the propertyID and InspectionPoint as its primary key. This junction table will also contain additional information regarding the outcome of the inspection. So you will need a result field - with at least three possible values, pass, failed, null (or incomplete) and a memo field for comments.

This structure is pretty simplistic and you may need a more sophisticated solution depending on your business rules. For example, if an inspection may be done multiple times, you'll probably need a fourth table that defined an inspection. This table will have a primary key that is an autonumber and a foreign key to relate to the property table. It would include inspection date and if the same person does all the work, you would include an inspectorID so you could tell who did the inspection and when. Then the junction table would connect the Inspection table with the inspection points table rather than directly connecting the property to the inspection points. If each point may be inspected by a different person, at a different time, the date/time and inspector need to go in the junction table rather than the inspector table.

Lots to think about. But without knowing the business rules, it is impossible to suggest a complete solution.

You do need to do some reading on relational database design since your first attemt violates first normal form.

Apparently I wasn't any help at all. It doesn't sound like you followed my advice. If you had, NOTHING would have had to be changed to add the Section8 items. Their descriptions would have just been added by the user to the InspectionPoints table. Creating a new table with a 1-to-1 relationship to the orginal table is only compounding your problem.

I know you hate the thought of starting from scratch, but given what you have learned about the application requirements and how the data is being used, you will be supprised at how little time it will actually take. You have tried to implement a spreadsheet as an Access db. You would truely have much less trouble just using Excel. If you want to continue to develop in Access, you really need to learn about table normalization. Access is not a spreadsheet, it is a relational database. It needs properly normalized tables to work efficiently.

I have been developing applications for more years than I care to mention and I have made many design and programming errors. Yes, even I have days when I should never have come to work. But, the one lesson that I have learned is the importance of actually fixing the problem as soon as it is discovered. Band-aids and work-arounds just compond the problem.

Yes, you can add the Section8 fields as a separate 1-to-1 table, but you still need to understand something about relational tables to get that solution to work. The Section8 table cannot have an autonumber primary key. Its primary key needs to be a long integer and the value of the key must be equal to the value of its parent table's primary key. When you join the two tables, you need to use a left join since not every row in your original table will have a Section8 row related to it.