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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |