Maximum number of fields in a database

[Dave32]

I have over 270 data fields which are unique. I tried to store these all in one table but there seems to be a limit of 255 fields. I reduced the amount of fields to 230.

I then created another table to add more fields, but I could not add many before I got the message "too many fields"

Is the 255 limit for all fields regardless of how many tables you have? or is there a limit of 255 fields per table?

Yes, I do need to link the two tables together, each have a primary key. The field is called "Unique Identifier"

Thanks,

Dave

 

[namliam]

The number of fields in the DB is unlimited. The number of fields in 1 table/query/form tho is 255.

I have NEVER needed 255 fields in anything ever... and I have done a few odd things in my career.
Most likely this is a faulty design... Why do you need 270 datafields ???

 

[neileg]

I agree with namliam. It is almost cetain that you need 270 records in a related table, not 270 fields.

 

[Dave32]

All,

Thanks for your replies. The database is store details of Marketing Campaigns. Each marketing campaign may have several Marketing Activities.

So each row of data, i.e each record is represents one marketing activity.

The generic data I collect is about 105 fields (this is info about the marketing activity and budget stuff). Additionaly, I need to store data on how much is spent per activity per month per country. So this takes up 156 fields.

So that is 12 months and 13 countries.

Now is it clear why so many fields?

Each marketing activity(record) has a unique identifier.

Thanks,

Dave

 

[Dave32]

This is what I mean (see attached file). So current my fields are called " month + country code" ie FRANCE JULY etc... Ideally I want to create a form like the above as well. So you can input the data easily.

I have tried this but it looks messy in the form as the field names are long. Ideally, I don't want to display the field name as "France July", but hide the field names in the form, so you can create the above.

Thanks for both of your thoughs on all my problems!!!

Dave

 

[namliam]

No, you dont need 156 fields!
Months and countries are things you store in related records not in subsequent columns, thus eliminating 156 fields and creating a seperate table with 4 columns.

Make a relational table called tblSpending or something:
ActivetyID
Month
Country
Spendature

I am pretty confident that the 105 fields can also be normalized down

 

[boblarson]

I am pretty confident that the 105 fields can also be normalized down

Absolutely!