 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Too Many Fields Defined
Hi - I have a large macro which takes codes and descriptions and makes tables. For each group, there are two tables. In one table, one field is the code the second is the description. In the second table, one field is the description, the second is the code. Each of these table is then transferred to Excel. There is no issue transferring any of the tables which have the code as the first field and the description of the second and begin inserting in column A. However, the second table, which has the description first and the code second, must go on the same worksheet in Excel in columns further down on the worksheet, e.g., columns D or E, etc. I have drilled down to where the errors are occurring in the macro and it is where the table is exported to the spreadsheet starting at columns D or E, etc. All of these fail. I receive the error Too Many Fields Defined from Access. Am I using incorrect naming convention? In the macro the Action is TransferSpreadsheet, the range in the transfers that work is APPLE!A2:C450. The range in the transfers that don't work is APPLE!D2:F450, etc. The tables are mirror images of each other, just the fields reversed. Any idea what I have done wrong? Thanks. >Hi - > >I have a large macro which takes codes and descriptions and makes tables. >For each group, there are two tables. In one table, one field is the code the >second is the description. In the second table, one field is the description, >the second is the code. It's almost certainly NOT necessary to make any new tables! You can TransferSpreadsheet from a Query just as easily as from a table. Can you perhaps create a single query (with fields for code, description, description and code, let's say) and export that query? John W. Vinson [MVP] Hi, John- The reason for code/description and description/code is that the first set is sorted alphabetically by code and the second set is sorted alphabetically by description. Same info just arranged differently. I cannot do it in the one query. The entire query sorts on the first field which requests a sort. > >Hi - > > [quoted text clipped - 9 lines] > > John W. Vinson [MVP] You should never make any assumptions about the order of the data in a table. Tables don't have any implicit order: they're "sacks of data", where Access puts the rows wherever it feels like. How does query sort on the first field which requests a sort?  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi, John- > The reason for code/description and description/code is that the first set [quoted text clipped - 17 lines] >> >description, >> >the second is the code. I could be approaching this all wrong... query reads description code code description Sort: ascending ascending The results of the query have the first column sorted in ascending order, no other fields are sorted. ...where did I go wrong? > You should never make any assumptions about the order of the data in a > table. Tables don't have any implicit order: they're "sacks of data", where [quoted text clipped - 23 lines] > >> >description, > >> >the second is the code. It would probably be a good idea to step back and explain exactly what you're trying to do. In general, having two identical tables would be wrong: you should never store data redundantly. What's wrong with having two separate queries? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I could be approaching this all wrong... > [quoted text clipped - 41 lines] >> >> >description, >> >> >the second is the code. I have approximately 5000 codes and accompanying descriptions which must be broken out into categories. This information is then transferred to an Excel workbook. On each page for each category must appear the code (in alphabetical order) and the accompanying description. Then a couple of columns over the description (in alphabetical order) and the code. Each category has its own worksheet. This workbook will be used as a reference for others who might have a code but no description or vice versa. > It would probably be a good idea to step back and explain exactly what > you're trying to do. [quoted text clipped - 47 lines] > >> >> >description, > >> >> >the second is the code. >I have approximately 5000 codes and accompanying descriptions which must be >broken out into categories. This information is then transferred to an Excel [quoted text clipped - 3 lines] >category has its own worksheet. This workbook will be used as a reference for >others who might have a code but no description or vice versa. It sounds like you should be able to create two queries on the same table - one sorted by code and the other by description - and export them, as queries (without a MakeTable). Or, it might be simpler to export just the first, and run a macro in Excel to copy the data to the second range and sort it in place. John W. Vinson [MVP] Thank you, all! > >I have approximately 5000 codes and accompanying descriptions which must be > >broken out into categories. This information is then transferred to an Excel [quoted text clipped - 11 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.