dealing with max number of fields Phredd 10.08.2006 12:48:01 For research and productivity purposes we have been entering and uploading pt testing information in an Access db. It is not intended for use on the Web. Recently we’ve joined a research Network that will utilize some of our data. Instead of re-entering data, I would like to upload it, but have encountered problems and don’t know how to handle it. The other sites enter directly into the Network db and do not track the other info we do. Our Access db has over 300 fields, but because the data is not collected in the same table, or queried at the same time, the 255 field limit was never an issue. But now that each record we’re trying to upload will have 278 fields, it has become a huge issue. And I don’t know how to get around it. Is there a better db to use? I looked at File Maker – they have unlimited fields, but they don’t have a combined date/time field – and most of the source data we import into Access comes with date and time in the same field. Oracle is too big & expensive for our purposes. What can I use to export smaller amounts of data to re-join into one table?. I don't have any experience with SQL or macros. Excel has the same issue with number of fields as Access does. The final product ready for import should be in .csv or .xml format. Expense is an issue. Any suggestions? (And please don't start the lectures I've seen on other posts about the max number of fields, ie, normalization or 'we've never seen so many fields in one db, so you must being doing something wrong'. The data points are unique and many, there's a lot of things to track when patients come to the hopital for testing.) The bottom line our data is in Access and we need to import 278 fields in one record to another computer.

Re: dealing with max number of fields "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 10.08.2006 13:02:44 If you're going to be supplying the data as a CSV or XML file, I'm not sure why the limit on the number of fields in a JET table should be a problem for you? You could, for example, open multiple recordsets to retrieve the data from the JET tables, loop through them, and print the data to a text file. It's a bit of a chore to write the necessary code to loop through the recordsets and keep them in synch with one another, but there are no great technical hurdles to overcome. -- Brendan Reynolds Access MVP "Phredd" <Phredd[ at ]discussions.microsoft.com> wrote in message news:EC24E292-6BEE-452C-AD83-296FDB4EEAE8[ at ]microsoft.com... [Quoted Text] > For research and productivity purposes we have been entering and uploading > pt > testing information in an Access db. It is not intended for use on the > Web. > Recently we've joined a research Network that will utilize some of our > data. > Instead of re-entering data, I would like to upload it, but have > encountered > problems and don't know how to handle it. The other sites enter directly > into > the Network db and do not track the other info we do. Our Access db has > over > 300 fields, but because the data is not collected in the same table, or > queried at the same time, the 255 field limit was never an issue. But now > that each record we're trying to upload will have 278 fields, it has > become a > huge issue. And I don't know how to get around it. > > Is there a better db to use? I looked at File Maker - they have unlimited > fields, but they don't have a combined date/time field - and most of the > source data we import into Access comes with date and time in the same > field. > Oracle is too big & expensive for our purposes. > > What can I use to export smaller amounts of data to re-join into one > table?. > I don't have any experience with SQL or macros. Excel has the same issue > with number of fields as Access does. The final product ready for import > should be in .csv or .xml format. Expense is an issue. > > Any suggestions? > > (And please don't start the lectures I've seen on other posts about the > max > number of fields, ie, normalization or 'we've never seen so many fields in > one db, so you must being doing something wrong'. The data points are > unique > and many, there's a lot of things to track when patients come to the > hopital > for testing.) > > The bottom line our data is in Access and we need to import 278 fields in > one record to another computer.

Re: dealing with max number of fields Phredd 10.08.2006 14:59:01 Well, there is one big technical hurdle to overcome - I have no idea what you said! What is a JET table? What type of loop are you taking about? I have never used the SQL or macro writting end of Access an know nothing about it. "Brendan Reynolds" wrote: [Quoted Text] > If you're going to be supplying the data as a CSV or XML file, I'm not sure > why the limit on the number of fields in a JET table should be a problem for > you? You could, for example, open multiple recordsets to retrieve the data > from the JET tables, loop through them, and print the data to a text file. > It's a bit of a chore to write the necessary code to loop through the > recordsets and keep them in synch with one another, but there are no great > technical hurdles to overcome. > > -- > Brendan Reynolds > Access MVP > > "Phredd" <Phredd[ at ]discussions.microsoft.com> wrote in message > news:EC24E292-6BEE-452C-AD83-296FDB4EEAE8[ at ]microsoft.com... > > For research and productivity purposes we have been entering and uploading > > pt > > testing information in an Access db. It is not intended for use on the > > Web. > > Recently we've joined a research Network that will utilize some of our > > data. > > Instead of re-entering data, I would like to upload it, but have > > encountered > > problems and don't know how to handle it. The other sites enter directly > > into > > the Network db and do not track the other info we do. Our Access db has > > over > > 300 fields, but because the data is not collected in the same table, or > > queried at the same time, the 255 field limit was never an issue. But now > > that each record we're trying to upload will have 278 fields, it has > > become a > > huge issue. And I don't know how to get around it. > > > > Is there a better db to use? I looked at File Maker - they have unlimited > > fields, but they don't have a combined date/time field - and most of the > > source data we import into Access comes with date and time in the same > > field. > > Oracle is too big & expensive for our purposes. > > > > What can I use to export smaller amounts of data to re-join into one > > table?. > > I don't have any experience with SQL or macros. Excel has the same issue > > with number of fields as Access does. The final product ready for import > > should be in .csv or .xml format. Expense is an issue. > > > > Any suggestions? > > > > (And please don't start the lectures I've seen on other posts about the > > max > > number of fields, ie, normalization or 'we've never seen so many fields in > > one db, so you must being doing something wrong'. The data points are > > unique > > and many, there's a lot of things to track when patients come to the > > hopital > > for testing.) > > > > The bottom line our data is in Access and we need to import 278 fields in > > one record to another computer. > > >

Re: dealing with max number of fields "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 10.08.2006 21:02:42 Ah, sorry, I see that you did say that you 'don't have any experience with SQL or macros', I'm afraid I over-looked that. I'm afraid I'm not sure what to tell you. The solution I had in mind does require a working knowledge of both SQL and VBA. -- Brendan Reynolds Access MVP "Phredd" <Phredd[ at ]discussions.microsoft.com> wrote in message news:7360B5E8-6F6B-44C1-AF7E-C977AEB3104F[ at ]microsoft.com... [Quoted Text] > Well, there is one big technical hurdle to overcome - I have no idea what > you > said! > > What is a JET table? What type of loop are you taking about? I have > never > used the SQL or macro writting end of Access an know nothing about it. > > > "Brendan Reynolds" wrote: > >> If you're going to be supplying the data as a CSV or XML file, I'm not >> sure >> why the limit on the number of fields in a JET table should be a problem >> for >> you? You could, for example, open multiple recordsets to retrieve the >> data >> from the JET tables, loop through them, and print the data to a text >> file. >> It's a bit of a chore to write the necessary code to loop through the >> recordsets and keep them in synch with one another, but there are no >> great >> technical hurdles to overcome. >> >> -- >> Brendan Reynolds >> Access MVP >> >> "Phredd" <Phredd[ at ]discussions.microsoft.com> wrote in message >> news:EC24E292-6BEE-452C-AD83-296FDB4EEAE8[ at ]microsoft.com... >> > For research and productivity purposes we have been entering and >> > uploading >> > pt >> > testing information in an Access db. It is not intended for use on the >> > Web. >> > Recently we've joined a research Network that will utilize some of our >> > data. >> > Instead of re-entering data, I would like to upload it, but have >> > encountered >> > problems and don't know how to handle it. The other sites enter >> > directly >> > into >> > the Network db and do not track the other info we do. Our Access db has >> > over >> > 300 fields, but because the data is not collected in the same table, or >> > queried at the same time, the 255 field limit was never an issue. But >> > now >> > that each record we're trying to upload will have 278 fields, it has >> > become a >> > huge issue. And I don't know how to get around it. >> > >> > Is there a better db to use? I looked at File Maker - they have >> > unlimited >> > fields, but they don't have a combined date/time field - and most of >> > the >> > source data we import into Access comes with date and time in the same >> > field. >> > Oracle is too big & expensive for our purposes. >> > >> > What can I use to export smaller amounts of data to re-join into one >> > table?. >> > I don't have any experience with SQL or macros. Excel has the same >> > issue >> > with number of fields as Access does. The final product ready for >> > import >> > should be in .csv or .xml format. Expense is an issue. >> > >> > Any suggestions? >> > >> > (And please don't start the lectures I've seen on other posts about the >> > max >> > number of fields, ie, normalization or 'we've never seen so many fields >> > in >> > one db, so you must being doing something wrong'. The data points are >> > unique >> > and many, there's a lot of things to track when patients come to the >> > hopital >> > for testing.) >> > >> > The bottom line our data is in Access and we need to import 278 fields >> > in >> > one record to another computer. >> >> >>

Re: dealing with max number of fields John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 11.08.2006 06:38:54 If programming is ruled out, the simplest thing I can think of is to export two delimited textfiles (let's call them T1.txt and T2.txt). T1 contains the first n fields of the desired output, and T2 contains the reminder. The files have the same number of lines, and line x in T1 contains fields from the same record as line x in T2. Two files like that can be merged into one wider file by using the "paste" utility, part of the Windows version of the Gnu utilities (free download from http://unxutils.sourceforge.net ). A command line like this paste T1.txt T2.txt > Widefile.txt will do the job for tab-delimited files; or paste -d, T1.txt T2.txt >Widefile.csv for CSV. On Thu, 10 Aug 2006 22:02:42 +0100, "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> wrote: [Quoted Text] > >Ah, sorry, I see that you did say that you 'don't have any experience with >SQL or macros', I'm afraid I over-looked that. > >I'm afraid I'm not sure what to tell you. The solution I had in mind does >require a working knowledge of both SQL and VBA. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.

Re: dealing with max number of fields "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 11.08.2006 08:51:15 Sounds good to me, John, thanks. I hope it solves the original poster's problem. -- Brendan Reynolds Access MVP "John Nurick" <j.mapSoN.nurick[ at ]dial.pipex.com> wrote in message news:v09od29526444hg0ofpf8jmqamkjh1tcq0[ at ]4ax.com... [Quoted Text] > If programming is ruled out, the simplest thing I can think of is to > export two delimited textfiles (let's call them T1.txt and T2.txt). > > T1 contains the first n fields of the desired output, and T2 contains > the reminder. The files have the same number of lines, and line x in T1 > contains fields from the same record as line x in T2. > > Two files like that can be merged into one wider file by using the > "paste" utility, part of the Windows version of the Gnu utilities (free > download from http://unxutils.sourceforge.net ). A command line like > this > > paste T1.txt T2.txt > Widefile.txt > > will do the job for tab-delimited files; or > > paste -d, T1.txt T2.txt >Widefile.csv > > for CSV. > > > > On Thu, 10 Aug 2006 22:02:42 +0100, "Brendan Reynolds" > <brenreyn[ at ]discussions.microsoft.com> wrote: > >> >>Ah, sorry, I see that you did say that you 'don't have any experience with >>SQL or macros', I'm afraid I over-looked that. >> >>I'm afraid I'm not sure what to tell you. The solution I had in mind does >>require a working knowledge of both SQL and VBA. > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email.