A
Allen Clark
I have an access db that has grown from a small pup into something that
needs to run on a more robust platform. The powers that be have selected a
SQL application to take the place of this access database, however, they
would like to populate the SQL application with the data that is in the
Access DB. In order to accomplish this feat, I must convert the data into
formatted, tab delimited text files with the data properly formatted or
displayed. I have the format of the files that I must create and I have
successfully dumped most of the data, but now I am facing a problem in that
I need to create a file from two linked tables.
Table 1:
id, fname, mname, lname, dateadded, datemodified, street, city, state, zip
Table 2:
pid, id, phonenumber, type, startdate, enddate
These two tables are linked by the ID field in both tables, it is a
one(table 1) to many(table 2) link. The output that I need to create is
this tab delimited text file (or excel spreadsheet) that will have only one
row with the Table 1 data and then have the phone number(s) and type(s)
appended to the end of the row for up to eight phone numbers. The output
columns should appear something like:
id, fname, mname, lname, ,dateadded, datemodified, street, city, state, zip,
phone1, type1, phone2, type2, phone3, type3, phone4, type4, phone5, type5,
phone6, type6, phone7, type7,phone8, type8
It appears like this should be able to be done with the make-table query,
but I have not found enough documentation on it and I haven't found a way to
continue appending the additional phone numbers and types to the row in the
created table.
Does anyone have any recommendations or suggestions?
Thanks in advance,
Allen
needs to run on a more robust platform. The powers that be have selected a
SQL application to take the place of this access database, however, they
would like to populate the SQL application with the data that is in the
Access DB. In order to accomplish this feat, I must convert the data into
formatted, tab delimited text files with the data properly formatted or
displayed. I have the format of the files that I must create and I have
successfully dumped most of the data, but now I am facing a problem in that
I need to create a file from two linked tables.
Table 1:
id, fname, mname, lname, dateadded, datemodified, street, city, state, zip
Table 2:
pid, id, phonenumber, type, startdate, enddate
These two tables are linked by the ID field in both tables, it is a
one(table 1) to many(table 2) link. The output that I need to create is
this tab delimited text file (or excel spreadsheet) that will have only one
row with the Table 1 data and then have the phone number(s) and type(s)
appended to the end of the row for up to eight phone numbers. The output
columns should appear something like:
id, fname, mname, lname, ,dateadded, datemodified, street, city, state, zip,
phone1, type1, phone2, type2, phone3, type3, phone4, type4, phone5, type5,
phone6, type6, phone7, type7,phone8, type8
It appears like this should be able to be done with the make-table query,
but I have not found enough documentation on it and I haven't found a way to
continue appending the additional phone numbers and types to the row in the
created table.
Does anyone have any recommendations or suggestions?
Thanks in advance,
Allen