Too many fields?

[Tomtheappraiser]

In my databse I was try to add a field, and it said that I have too many
fields. But when I went and deleted one that I never use, it still says I
have too many. Even if I go to one and change the name and properties of an
existing field, it wont let me save.

Can anyone help with this?

 

[George Nicholson]

Tools>Utilities>Compact and Repair

Deleting fields doesn't free diskspace or remove them from the field count
until you do a Compact.

HTH,

 

[fredg]

In my databse I was try to add a field, and it said that I have too many
fields. But when I went and deleted one that I never use, it still says I
have too many. Even if I go to one and change the name and properties of an
existing field, it wont let me save.

Can anyone help with this?

Would it be too much to ask how many fields you have?

Did you read Access specifications?
Help + Specifications + Access Specifications + Tables

If you are even close to that limit, I would suspect your database is
not correctly designed.

 

[RBear3]

But....

If you have anywhere close to 255 fields in your table, you have a very
poorly designed database.

In a relational database, you would rarely find a table with more than 20
fields.

If you post back with more information on your structure, perhaps we can
advise you on how to normalize your data and get better performance and
easier functionality out of Access.

 

[Klatuu]

At what point does it say you have too many fields?
You are allowed 255 fields in a table; however, if you have that many, you
may need to rethink your design. If you need to delete fields, I would
suggest a compact and repair to ensure the field count is updated.

If this is happening at design time, the 255 limit may be the problem. If it
is happening in a report, then there is something there that I can't define,
but it will happen. I once created a very complex report. The row source did
not have nearly that many fields. It has about 45 fields. But, because of
the complexity of the report, Access must be creating fields on its own,
because I added one additional group level and got that error. I had to
drop that level and redo my report to get it to work.

 

[Tomtheappraiser]

I am VERY new to Access, and have been teaching myself from the help menu
when I can. And let me tell you, that help program is NOT helpful at all. For
instance when I got the error on too many fields, I tried to enter the error
message in the help menu and it was having none of it.

Anywho, I just started working for the local Assessors office, who have NO
automated systems. EVERYTHING is on hand written paper (I kid you not). So,
in order to speed up my work there and get out in the field quicker I brought
my laptop in and have been trying to build a database to use. The reason I
have so many fields is because there is so much interelating data that I need
to use in different forms and reports. I always needing address, year built,
square footage, and lto size. But sometiumes I need last years assessed value
on a form for the land, improvements and total. There's 3 fields right there.
Sometimes I need to perform a cot approach which usses price per square foot
built new, total cost new, have to show physical, functional and external
depreciation ratios, along with the phyiscal, functional and external
depreciation amounts. There's 8 more fields right there. Plus all the
different fields it takes to describe different types of commercial
buildings. (Nursing homes, warehouses, hotels, etc)

My problem is that I do not understand relationships at all. I have tried
reading about them in the help section, but I just don't get it. Mind you of
course I don't have internet access when I am at the office, so I have to
wait till I get home to ask questions on this forum. By the time I get home
I usually forget to do it.

 

[RBear3]

Well, this sounds very complex, but I will try to give a couple of examples
that might help you out.

The way I understand it each property would have some kind of identifying
number that never changes and some data that never changes. I am looking at
the Harris County (www.HCAD.ORG) for an example. Here is what I might do
for the main table...

TblPropertyMain
PropertyAccountNumber
ClassCode
LandUseCode
LandArea
Legal DescriptionLot
Legal DescriptionBlock
LegalDescriptionNeighborhood
LegalDescriptionSection
PropertyAddress1
PropertyAddress2
PropertyCity
PropertyState
PropertyZIP

That's about it!

Now, each property can have one (or more) improvements on it. I guess you
even have some tht have no improvements. This means you have a one-to-many
relationship. We need a new table for Improvements that is somehow related
to the Property. Maybe something like this...

TblImprovements
UniqueImprovementNumber
PropertyAccountNumber
ImprovementDescription
ImprovementSiteCode
ImprovementSquareFootage
etc.


Okay, now each property has an owner. Some owners have more than one
property. You need a table that stores each owner.

TblTaxPayer
UniqueOwnerNumber (SS#?)
OwnerLastName
OwnerFirstName
OwnerMiddle
OwnerSuffix
OwnerMailingAddress1
OwnerMailingAddress2
OwnerMailingCity
OwnerMailingState
OwnerMailingZIP
OwnerPhone
etc.

But, now you have to tie owners to properties. This requires a junction
table. You also need to know if they still own the property, so you may
need to include purchase dates.

TblPropertiesAndOwners
UniqueOwnerNumber
PropertyAccountNumber
DatePurchased
DateSold



This does not even get into the appraised values. Since they change each
year and you need to track history, you will have to create a table and make
a SEPARATE entry for each year that the property is appraised. You might
have to break this down into taxing authorites. This MIGHT look something
like...

TblAppraisalDetails
PropertyAccountNumber
AppraisalDate
TaxingAuthorityNumber (Probably need another table with the
numbers and the associated names)
Rate
AppraisedValue
ExemptAmount
etc.

Be careful not to do any math here. You don't need to store the total
value, the exempt amount, and the taxable amount. You can calcualte the
taxable amount using the other two values. Don't store the total tax. You
can calculate this by multiplying the TaxableAmount by the Rate. No need to
store the total.



As you can see, you have a very very complex puzzle ahead of you. I'd
recommend you sit down and map this out on paper before you start building
tables. You might also read up on normalization and relationships. My
guess would be that you have a situation that would really benefit from
paying a professional to come in and get you started. If you pay a pro for
a couple of weeks or a month, they could get your structure built and even
create some basic forms, reports, and queries.

 

[John W. Vinson]

My problem is that I do not understand relationships at all. I have tried
reading about them in the help section, but I just don't get it.

Check out some of the references at these websites, particularly the "Database
Design 101" links on Jeff's site.

You're making a very common beginner's mistake - "Committing Spreadsheet Upon
a Database". If you're assuming that you need to put all the information into
one record in one table, that assumption IS WRONG and is the source of your
problems! "Fields are expensive, records are cheap". You'll need multiple
tables, and each property will have zero, one, or many records in some tables.
RBear3 has given you an excellent starting point!

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html


John W. Vinson [MVP]