Re: Converting from Access to SQL2k via Excel- HELP!!!!!
From: Allen Clark (acent1_at_nospamplease.com)
Date: Tue, 20 Apr 2021 12:30:29 -0400
Thank you for your direction. I believe this should allow me to resolve the
table creation process. In both writing, and rereading my original post, I
failed to see any comment about the new table structure. I simply lamented
the fact that I had to go from an old/reliable access database to a new SQL
application and that there were a great deal of hoops to jump through to
complete the transition. I in no way meant to imply any offense to either
the old or new system in use.
Again, thank you for the information and assistance. It has tremendously
helped in the completion of this phase of this project and I am in your debt
for the assistance.
"John Nurick" <j.mapSoN.email@example.com> wrote in message
> Hi Allen,
> Let's not pass comment about the new table structure!
> If you go to http://www.mvps/org.access and search for "concatenate"
> you'll find a couple of VBA functions that will concatenate a field in
> multiple child records into a single calculated query. I think the one
> you want is called fConcatChild(). If you modify it to use a tab -
> Chr(9) - instead of a comma between each child item, the result can be
> included in a tab-delimited file, where it will show up as multiple
> Start with a query (qryA) on Table2 that returns the
> phonenumber, type
> pair as a single field with the two things separated by a tab:
> PhonePair: [phonenumber] & Chr(9) & [Type]
> Then build another query that joins qryA to Table1 and uses the
> concatenation function. The fields in this will be the ones you want
> from Table1 plus a calculated field using fConcatChild() on the
> PhonePair field from qryA.
> On Mon, 19 Apr 2021 19:05:44 -0400, "Allen Clark" <firstname.lastname@example.org>
> >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
> >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
> >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
> >I need to create a file from two linked tables.
> >Table 1:
> >id, fname, mname, lname, dateadded, datemodified, street, city, state,
> >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
> >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,
> >phone1, type1, phone2, type2, phone3, type3, phone4, type4, phone5,
> >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
> >continue appending the additional phone numbers and types to the row in
> >created table.
> >Does anyone have any recommendations or suggestions?
> >Thanks in advance,
> John Nurick [Microsoft Access MVP]
> Please respond in the newgroup and not by email.