Re: Converting from Access to SQL2k via Excel- HELP!!!!!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 04/20/04


Date: Tue, 20 Apr 2021 07:41:45 +0100

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
fields.

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" <acent1@erols.com>
wrote:

>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 spread***) 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
>

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Quantcast