A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

exceed limit of 255 fields



 
 
Thread Tools Display Modes
  #11  
Old October 20th, 2005, 11:57 AM
Vincent Johns
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

Tim Ferguson wrote:

"Douglas J. Steele" wrote in
:

Sounds like a job for Excel to me too...


Except Excel can only go up to column IV, or 256 columns...


Hmmm... I think we are all agreed that some major design thinking is
required. Doing calculations in SQL is so horrid that my instinct is always
to take the whole lot off to something intended for the task, like SPSS or
Excel etc etc. Even if the storage method is still Jet, of course.

All the best

Tim F


I agree wholeheartedly. Excel can do some amazing arithmetic, and if
you have a copy handy, why not use it? It's easy for Access to link to
Excel to utilize the results.

-- Vincent Johns
Please feel free to quote anything I say here.

  #12  
Old October 20th, 2005, 12:11 PM
blackcat
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

thanks for the advice and i can see where your coming from of course,
unfortunatly that is not an option, the powers that be want this in Access
for reporting purpases mainly! There are literlly about a hundred calcs on
this database, and i do think that maybe it has outgrown Access, (as clever
as Access is)

"Vincent Johns" wrote:

Tim Ferguson wrote:

"Douglas J. Steele" wrote in
:

Sounds like a job for Excel to me too...

Except Excel can only go up to column IV, or 256 columns...


Hmmm... I think we are all agreed that some major design thinking is
required. Doing calculations in SQL is so horrid that my instinct is always
to take the whole lot off to something intended for the task, like SPSS or
Excel etc etc. Even if the storage method is still Jet, of course.

All the best

Tim F


I agree wholeheartedly. Excel can do some amazing arithmetic, and if
you have a copy handy, why not use it? It's easy for Access to link to
Excel to utilize the results.

-- Vincent Johns
Please feel free to quote anything I say here.


  #13  
Old October 20th, 2005, 01:14 PM
Vincent Johns
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

blackcat wrote:

i have title information which holds no cass,cds, roylty information,
production info, etc on table a, other tables which store costs etc,


My suggestion would be to set up several Tables, each of which holds
related information, rather than one Table in which each record tells
you all you ever want to know about a given title.

Apparently, each record in your current main Table, [table a], describes
a work of art, the storage medium on which it's stored (cassette, CD),
information about the copyright holder, production company, etc.
Another Table, [costs], includes info on costs of production, I guess.
(There might be others which you did not mention.)

i then
us a dlookup to pull this info into table a,


If you already have a Query that uses DLookup(), is there really a need
to store the results? Your Query may already give you what you need
there, and many times you can use a Query just as if it were a Table.
(For example, you can base other Queries on its results, or use it as
the basis for a Form or a Report, etc.)

i then have a macro which stores
all the calculations and belive me there are loads, this database started out
being fairly simple with just a few calculations and has grown so much!


If the calculations really are complex (for example, matrix operations
or differential equations, though my guess is you're not doing any of
these), you might want to have Excel do them. You can still use Access
to do database stuff like storing and looking up data.

i
need to do it in access as not excel as we need to report on the titles and
recall them etc. thanks for your help and advice on this


Although it would help if you could list the fields you currently have
in [table a], including a brief description if the field name isn't
suggestive of what the field contains, I'm going to make a couple of
guesses here. If I guess wrong, that will invalidate some of what I
suggest, but not necessarily all. But please consider the rest of my
recommendations to implicitly begin with the caveat "Assuming that I
have guessed correctly about the current contents of your Tables, I
suggest that you do this...".

Incidentally, it might not hurt you to look at the Northwind Traders
sample database for examples (although the products there are food
items, not works of art, but they still have production companies,
shipping companies, distributors, consumers, etc.). You don't have to
memorize or imitate what Microsoft did there, but that database has lots
of good ideas that at least you should be aware of.

Anyway, in your database, for a given work of art, you know its title;
that's a good field to leave in [table a], and you might call the field
[Title]. (Incidentally, could you come up with a more descriptive name
for your [table a] Table, such as, maybe, [Titles]? That would help to
give a better idea of what's inside the Table.)

Besides the [Title] field in [table a], you might need some unique
identifier by which you can refer to a record. The [Title] field may be
unique not, but what happens if you later wind up with, e.g., a song
called "Oklahoma!" and a musical show on CDs also called "Oklahoma!" and
a movie on DVD also called "Oklahoma!"? An Autonumber (or similar type)
field, which in this case I'd call [table aID], would allow you to
distinguish records with similar or identical [Title] fields and would
save space and time in linking other Tables to [table a]. (Yes, I'm
assuming you'll have lots of Tables, even though you have only one or
two right now.)

Some of your fields seem to deal with production companies. Especially
if the same company appears more than once in [table a], but even if
not, I'd put compamy information into a [Production company] Table, to
include stuff like [Company name], [Contact person first name], [Contact
person telephone], [ZIP code].

If you're selling these works, you might have customers. In that case,
you could use a [Customers] Table, with fields such as [Company name],
[Contact person first name], etc.

If you have info on storage media, you might want to set up a [CDs]
Table including such fields as [ISBN] (or whatever you use to identify a
CD title), [PublisherID] (this could be a link to a [Publishers] Table,
which in turn might include fields such as [Company name], etc.), [CD
location in my library], [Catalog number], etc.

If you have multiple storage media, you could either devote one field in
[table a] to a link to a [CDs] Table, another field to a [Cassettes]
Table, etc., or to have just one link to a [Media] Table. If you have
similar types of information for CDs and cassettes, the [Media] approach
would be better. In [Media], a [MediaID] field would identify a
specific storage medium for a specific work of art; a [PublishersID]
field could link to the contact information in the [Publishers] Table; a
[Type] field would identify the physical medium, such as "CD" or "cass";
a [Condition] field could contain values such as "scratched", "unused",
"barely readable", "junk", etc.

Your current [Costs] Table might also be broken down into categories, if
you have lots of fields there, but not necessarily (depends on how much
detail you're storing). At the least, you probably want to link each
record in [Costs] to one in [table a] by including a [table aID] field
in each record in [Costs]. Otherwise, you'll know the amount of money,
perhaps, but not what it means.

Please bear in mind that Access (like any RDBMS) is pretty flexible.
Your decisions don't have to be permanent, and it should not be very
difficult to modify your database to accommodate new information.

Anyway, without more details about what information you have and what
you want to do with it, it's difficult to make more specific
suggestions, but I hope you can get an idea from what I said here about
some of the possibilities. And I barely mentioned possible Queries --
they can be used to do all sorts of things with your information once
your Tables are set up in a convenient manner.


"Rick B" wrote:


You would base your form on a QUERY which can contain several related
tables. Just breaking this into two pieces is not the answer though.
Sounds like your structure is badly flawed. Sounds like you should have
several one-to-many relationships.

What is in this table? What is the structure of your data? If you have
things like...


[...]


"blackcat" wrote in message
...

thanks for your response, so if i create a new table, how can i pull this
into the existing form that i have, ie how do i use two tables on one form
or
report. my calculations are stored in a macro, hense the reason for
needed
to have all fields on one form


Not a sufficient reason. What you put on a Form should be whatever it's
convenient for a human being to look at on the Form. To display many
controls, you can use Tabs, or Subforms, or similar devices, to allow a
user to select a small collection of related data to view or update or
input.

The calculations in your Macro may be difficult to use anywhere else.
Not everyone agrees with me on this, but my suggestion is to move some
of those calculations to named Queries that you can thereafter use in
Forms, Reports, or other Queries. You might be able to have your Macro
refer to the Query instead of doing the calculation right there.

[...]

"Rick B" Anonymous wrote in message
...

Ummm- you should redesign your table. At the most, you should have 20
or
30 fields in a table.


I agree with Rick B., but this is a guideline. What I think is more
important at first is to put into one Table only closely related
information, such as whatever you need to know about a specific work of
art. If some of that information identifies its publisher, you can
include a link -- one short field -- to another Table that contains
details about the publisher. You might end up with six fields or 36;
what's important is that they logically belong with the subject matter
of your Table. The main idea here is to help you avoid doing
unnecessary work and to avoid mistakes (which can cause much unnecessary
work).

-- Vincent Johns
Please feel free to quote anything I say here.


[...]

"blackcat" wrote in message
news
i have created a database which has huge table, i need to add more
fields
to
this table (really does need to be in the same table as i have some
very
complex calculations going on too!) only i now get the message
'exceeds
limit
of 255 fields' is there any way i can over wright this?


  #14  
Old October 20th, 2005, 01:24 PM
Vincent Johns
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

blackcat wrote:

thanks for the advice and i can see where your coming from of course,
unfortunatly that is not an option, the powers that be want this in Access
for reporting purpases mainly! There are literlly about a hundred calcs on
this database, and i do think that maybe it has outgrown Access, (as clever
as Access is)


Sorry, a message I just now posted got linked to the wrong one of your
messages -- I guess I was careless.

Anyway, what would be your customers' (managers') objections to using
Access to create Reports based (partly, and probably invisibly) on
Excel? Or Access Modules (written in VBA)? The Reports would look
identical.

Of course, from your point of view, the internal structure would be way
different, and you'd need to be concerned about how to maintain it.
That would be of some possible interest to your customer, as it might
affect your maintenance costs, but only indirectly (for example, your
customer doesn't want to waste money on database stuff). What I'd guess
is most important is what the Reports look like, how accurate they are,
etc. But you have a better understanding of your situation than I do,
and that's why everything I say is in the nature of a suggestion. Its
purpose is to help you produce those Reports (or whatever) as easily and
cheaply and reliably as you can. As you can probably guess, I think
that any Table containing hundreds of fields per record gets in the way
of that goal.

-- Vincent Johns
Please feel free to quote anything I say here.
  #15  
Old October 20th, 2005, 01:48 PM
blackcat
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

thank you all for your advice - i can see i need to re-build my database and
tidy up my tables - could also do with some advance training on this so if
anyone knows of a good Access training course please let me know. thanks
again

"Vincent Johns" wrote:

blackcat wrote:

i have title information which holds no cass,cds, roylty information,
production info, etc on table a, other tables which store costs etc,


My suggestion would be to set up several Tables, each of which holds
related information, rather than one Table in which each record tells
you all you ever want to know about a given title.

Apparently, each record in your current main Table, [table a], describes
a work of art, the storage medium on which it's stored (cassette, CD),
information about the copyright holder, production company, etc.
Another Table, [costs], includes info on costs of production, I guess.
(There might be others which you did not mention.)

i then
us a dlookup to pull this info into table a,


If you already have a Query that uses DLookup(), is there really a need
to store the results? Your Query may already give you what you need
there, and many times you can use a Query just as if it were a Table.
(For example, you can base other Queries on its results, or use it as
the basis for a Form or a Report, etc.)

i then have a macro which stores
all the calculations and belive me there are loads, this database started out
being fairly simple with just a few calculations and has grown so much!


If the calculations really are complex (for example, matrix operations
or differential equations, though my guess is you're not doing any of
these), you might want to have Excel do them. You can still use Access
to do database stuff like storing and looking up data.

i
need to do it in access as not excel as we need to report on the titles and
recall them etc. thanks for your help and advice on this


Although it would help if you could list the fields you currently have
in [table a], including a brief description if the field name isn't
suggestive of what the field contains, I'm going to make a couple of
guesses here. If I guess wrong, that will invalidate some of what I
suggest, but not necessarily all. But please consider the rest of my
recommendations to implicitly begin with the caveat "Assuming that I
have guessed correctly about the current contents of your Tables, I
suggest that you do this...".

Incidentally, it might not hurt you to look at the Northwind Traders
sample database for examples (although the products there are food
items, not works of art, but they still have production companies,
shipping companies, distributors, consumers, etc.). You don't have to
memorize or imitate what Microsoft did there, but that database has lots
of good ideas that at least you should be aware of.

Anyway, in your database, for a given work of art, you know its title;
that's a good field to leave in [table a], and you might call the field
[Title]. (Incidentally, could you come up with a more descriptive name
for your [table a] Table, such as, maybe, [Titles]? That would help to
give a better idea of what's inside the Table.)

Besides the [Title] field in [table a], you might need some unique
identifier by which you can refer to a record. The [Title] field may be
unique not, but what happens if you later wind up with, e.g., a song
called "Oklahoma!" and a musical show on CDs also called "Oklahoma!" and
a movie on DVD also called "Oklahoma!"? An Autonumber (or similar type)
field, which in this case I'd call [table aID], would allow you to
distinguish records with similar or identical [Title] fields and would
save space and time in linking other Tables to [table a]. (Yes, I'm
assuming you'll have lots of Tables, even though you have only one or
two right now.)

Some of your fields seem to deal with production companies. Especially
if the same company appears more than once in [table a], but even if
not, I'd put compamy information into a [Production company] Table, to
include stuff like [Company name], [Contact person first name], [Contact
person telephone], [ZIP code].

If you're selling these works, you might have customers. In that case,
you could use a [Customers] Table, with fields such as [Company name],
[Contact person first name], etc.

If you have info on storage media, you might want to set up a [CDs]
Table including such fields as [ISBN] (or whatever you use to identify a
CD title), [PublisherID] (this could be a link to a [Publishers] Table,
which in turn might include fields such as [Company name], etc.), [CD
location in my library], [Catalog number], etc.

If you have multiple storage media, you could either devote one field in
[table a] to a link to a [CDs] Table, another field to a [Cassettes]
Table, etc., or to have just one link to a [Media] Table. If you have
similar types of information for CDs and cassettes, the [Media] approach
would be better. In [Media], a [MediaID] field would identify a
specific storage medium for a specific work of art; a [PublishersID]
field could link to the contact information in the [Publishers] Table; a
[Type] field would identify the physical medium, such as "CD" or "cass";
a [Condition] field could contain values such as "scratched", "unused",
"barely readable", "junk", etc.

Your current [Costs] Table might also be broken down into categories, if
you have lots of fields there, but not necessarily (depends on how much
detail you're storing). At the least, you probably want to link each
record in [Costs] to one in [table a] by including a [table aID] field
in each record in [Costs]. Otherwise, you'll know the amount of money,
perhaps, but not what it means.

Please bear in mind that Access (like any RDBMS) is pretty flexible.
Your decisions don't have to be permanent, and it should not be very
difficult to modify your database to accommodate new information.

Anyway, without more details about what information you have and what
you want to do with it, it's difficult to make more specific
suggestions, but I hope you can get an idea from what I said here about
some of the possibilities. And I barely mentioned possible Queries --
they can be used to do all sorts of things with your information once
your Tables are set up in a convenient manner.


"Rick B" wrote:


You would base your form on a QUERY which can contain several related
tables. Just breaking this into two pieces is not the answer though.
Sounds like your structure is badly flawed. Sounds like you should have
several one-to-many relationships.

What is in this table? What is the structure of your data? If you have
things like...


[...]


"blackcat" wrote in message
...

thanks for your response, so if i create a new table, how can i pull this
into the existing form that i have, ie how do i use two tables on one form
or
report. my calculations are stored in a macro, hense the reason for
needed
to have all fields on one form


Not a sufficient reason. What you put on a Form should be whatever it's
convenient for a human being to look at on the Form. To display many
controls, you can use Tabs, or Subforms, or similar devices, to allow a
user to select a small collection of related data to view or update or
input.

The calculations in your Macro may be difficult to use anywhere else.
Not everyone agrees with me on this, but my suggestion is to move some
of those calculations to named Queries that you can thereafter use in
Forms, Reports, or other Queries. You might be able to have your Macro
refer to the Query instead of doing the calculation right there.

[...]

"Rick B" Anonymous wrote in message
...

Ummm- you should redesign your table. At the most, you should have 20
or
30 fields in a table.


I agree with Rick B., but this is a guideline. What I think is more
important at first is to put into one Table only closely related
information, such as whatever you need to know about a specific work of
art. If some of that information identifies its publisher, you can
include a link -- one short field -- to another Table that contains
details about the publisher. You might end up with six fields or 36;
what's important is that they logically belong with the subject matter
of your Table. The main idea here is to help you avoid doing
unnecessary work and to avoid mistakes (which can cause much unnecessary
work).

-- Vincent Johns
Please feel free to quote anything I say here.


[...]

"blackcat" wrote in message
news
i have created a database which has huge table, i need to add more
fields
to
this table (really does need to be in the same table as i have some
very
complex calculations going on too!) only i now get the message
'exceeds
limit
of 255 fields' is there any way i can over wright this?



  #16  
Old October 20th, 2005, 01:55 PM
Rick B
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

I still don't see how that would all be one table. Also, calculations
should rarely be stored in a table.

--
Rick B



"blackcat" wrote in message
...
i have title information which holds no cass,cds, roylty information,
production info, etc on table a, other tables which store costs etc, i
then
us a dlookup to pull this info into table a, i then have a macro which
stores
all the calculations and belive me there are loads, this database started
out
being fairly simple with just a few calculations and has grown so much! i
need to do it in access as not excel as we need to report on the titles
and
recall them etc. thanks for your help and advice on this

"Rick B" wrote:

You would base your form on a QUERY which can contain several related
tables. Just breaking this into two pieces is not the answer though.
Sounds like your structure is badly flawed. Sounds like you should have
several one-to-many relationships.

What is in this table? What is the structure of your data? If you have
things like...

Salary2004 Salary2005 Slaary2006

or

Hobby1 Hobby2 Hobby3

etc,

Then you want to stop, step back, and rethink your design. Those
examples
point to cases where a one-to-many relationship should be used. This
will
involve addind a new table with two fields. One for the person's
identification number, and one for their Hobby. Or, in the Salray
example,
three fields. One for the ID, one for the amount, and one for the date.
These new tables would be related to the original table where the
person's
information is stored.

Give us some detials about your data and we can give you some ideas.

--
Rick B



"blackcat" wrote in message
...
thanks for your response, so if i create a new table, how can i pull
this
into the existing form that i have, ie how do i use two tables on one
form
or
report. my calculations are stored in a macro, hense the reason for
needed
to have all fields on one form

"Rick B" wrote:

Just one more thought. You say it really needs to be in the same
table
due
to complex calculations. Not sure what that has to do with it. You
can
pull data from many tables into a query, report, and form to perform
calculations.

Hopefully you are not storing any of these calculated values in the
table.
With few exceptions, that is not recommended. Calculations should be
performed in the queries, reports, or forms. Storing them in the
table
can
lead to problems if you need to change one of the numbers upon which
your
calculation is based.

--
Rick B



"Rick B" Anonymous wrote in message
...
Ummm- you should redesign your table. At the most, you should have
20
or
30 fields in a table.

You may want to explain your structure so we can recommend how you
can
improve it. Sounds like you are using Access as a Spreadsheet, not
as
a
relational database. Doing so will cause you many many headaches.

--
Rick B



"blackcat" wrote in message
news i have created a database which has huge table, i need to add more
fields
to
this table (really does need to be in the same table as i have some
very
complex calculations going on too!) only i now get the message
'exceeds
limit
of 255 fields' is there any way i can over wright this?










  #17  
Old October 20th, 2005, 03:05 PM
Vincent Johns
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

blackcat wrote:

thank you all for your advice - i can see i need to re-build my database and
tidy up my tables - could also do with some advance training on this so if
anyone knows of a good Access training course please let me know. thanks
again


Check Microsoft's Web site. Also, isn't there an "Access for Dummies"
book out? (I think there's even a "Dummies for Dummies" book, for
ventriloquists.) Also, look at Access Help for "Sample databases
included with Microsoft Access" and "Northwind database".

I suggest you not "re-build" your database, but rather create new Tables
and Queries as needed and copy data to the new Tables from your existing
Tables. I see no obvious need to throw away anything, just reorganize
what you have. Well, if you have redundant information, stuff that can
be calculated, you can probably get rid of that (but keep backup copies
of your database, just in case you sometime delete a bit more than you
intend).

-- Vincent Johns
Please feel free to quote anything I say here.


  #18  
Old October 20th, 2005, 06:50 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

"=?Utf-8?B?YmxhY2tjYXQ=?=" wrote in
:

thanks for the advice and i can see where your coming from of course,
unfortunatly that is not an option, the powers that be want this in
Access for reporting purpases mainly! There are literlly about a
hundred calcs on this database, and i do think that maybe it has
outgrown Access, (as clever as Access is)


We all comprehend the difficulties of persuading "the powers that be"
that what they think they want is stupid, shortsighted, ill-informed and
frequently infeasible and when they hold the purse strings then
everything gets worse. Nevertheless, it's worth having a go at getting
them to learn to define the outputs (the "whats") and leaving the methods
(the "hows") to you.

This really does not sound like a job for Access _on its own_ : you have
a toolbox with many options (Word for report writing, PowerPoint for the
presentation afterwards, etc) and you can't be expected to do a decent
job if you are not allowed to access (pun intended!) the ones you need.

Best of luck!


Tim F

  #19  
Old December 20th, 2005, 07:29 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default exceed limit of 255 fields

Blackcat I agree with Rick B...
I can't tell you how many times I heard this...
"Hopefully you are not storing any of these calculated values in the table.
With few exceptions, that is not recommended. Calculations should be
performed in the queries, reports, or forms. Storing them in the table can
lead to problems if you need to change one of the numbers upon which your
calculation is based."

Rick is right.

Bernard


"Rick B" wrote:

Just one more thought. You say it really needs to be in the same table due
to complex calculations. Not sure what that has to do with it. You can
pull data from many tables into a query, report, and form to perform
calculations.

Hopefully you are not storing any of these calculated values in the table.
With few exceptions, that is not recommended. Calculations should be
performed in the queries, reports, or forms. Storing them in the table can
lead to problems if you need to change one of the numbers upon which your
calculation is based.

--
Rick B



"Rick B" Anonymous wrote in message
...
Ummm- you should redesign your table. At the most, you should have 20 or
30 fields in a table.

You may want to explain your structure so we can recommend how you can
improve it. Sounds like you are using Access as a Spreadsheet, not as a
relational database. Doing so will cause you many many headaches.

--
Rick B



"blackcat" wrote in message
news
i have created a database which has huge table, i need to add more fields
to
this table (really does need to be in the same table as i have some very
complex calculations going on too!) only i now get the message 'exceeds
limit
of 255 fields' is there any way i can over wright this?






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate Merge Fields via VB.NET ben Mailmerge 5 June 21st, 2005 05:43 PM
Outlook contacts doesn't allow all fields from Excel? gnmcewen Contacts 0 January 9th, 2005 05:21 AM
Additional fields for form based parameter query/null fields geeksdoitbetter Running & Setting Up Queries 2 January 7th, 2005 11:05 PM
Populate fields in Form Header based on header fields in startup f Pat Dools Using Forms 0 January 7th, 2005 04:19 PM
Automatic filling of fields in table two from table one Jim Kelly Database Design 1 September 27th, 2004 10:16 PM


All times are GMT +1. The time now is 12:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.