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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Clark (acent1_at_nospamplease.com)
Date: 04/28/04


Date: Wed, 28 Apr 2021 15:36:41 -0400

Thank you John! It worked like a champ. There was very little modification
I had to do to get it to run in our environment, and everything is good with
the world. Thank you again for your assistance.

Take care,
Allen

"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
news:jhrr8013nvd94pipf0ff3tbcieu0os6l03@4ax.com...
> Hi Allen,
>
> The fConcatFld() function at
> http://www.mvps.org/access/modules/mdl0008.htm will do what you want.
> For an example, paste the function into a module in the Northwind sample
> database. Then create a new query and paste the following into its SQL
> view:
>
> SELECT DISTINCT [Order Details].OrderID, fConcatFld("Order
> Details","OrderID","ProductID","Long",[OrderID]) AS ProdList
> FROM [Order Details]
> WHERE ((([Order Details].OrderID)<10252))
> ORDER BY [Order Details].OrderID;
>
> The result has two fields: OrderID and a list of the ProductIDs in each
> order.
>
> In your database you'd need to start with a query (qryPhones) that
> concatenates the NUMBER and TYPE fields. The example below uses a
> semicolon delimiter; if you change that here you must also change it in
> the fConcatFld() code.
>
> SELECT ID, TYPE & ";" & NUMBER AS PhoneItem FROM phones;
>
> and then use a further query to concatenate them, something like:
>
> SELECT DISTINCT ID,
> fConcatFld("qryPhones","ID","PhoneItem",[ID]) AS PhoneItems
> FROM qryPhones;
>
> Then export this to a textfile.
>
>
>
>
> On Tue, 27 Apr 2021 00:15:35 -0400, "Allen Clark"
> <acent1@nospamplease.com> wrote:
>
> >John (and others),
> >
> >Please pardon the interruption, but I am still having problems with this
> >issue. The function that you pointed me to allows me to concatenate all
of
> >the "numbers" into a single tab (or comma or semicolon) delimited field
in a
> >new table. The problem that I have is that I need to put it into a loop
and
> >step through the entire table so that every record with a valid "ID"
> >generates the concatenated string for EACH "ID" in the table.
> >
> >table "phones"
> >
> >"PID","ID","NUMBER","TYPE"
> >1,34,(301) 698-0001, home
> >2,34,(301) 698-0002,fax
> >3,34,(301) 876-2100, cell
> >4,41,(703) 555-1234, home
> >5,41,(703) 555-2232, office
> >6,41,(703) 555-1222, fax
> >7,52,(702) 123-1456, office
> >8,66,(301) 222-2223, home
> >9,72,(202) 312-9999, office
> >10,72,(202) 312-0000, fax
> >...
> >
> >I would like to be able to create a temporary table that would have
fields
> >"ID", and "strConCat"
> >
> >i.e.
> >
> >table "temp_phone"
> >"ID","StrConCat"
> >34,"(301) 698-0001, home(301) 698-0002,fax,(301) 876-2100, cell"
> >41,"(703) 555-1234, home,(703) 555-2232, office,(703) 555-1222, fax"
> >52,"(702) 123-1456, office"
> >66,"(301) 222-2223, home"
> >72,"(202) 312-9999, office,(202) 312-0000, fax"
> >...
> >
> >With this I can do a join with another table that will contain all the
other
> >fields that need to be manipulated and work out the rest with Excel. The
> >only problem is that I need create this temp table to be able to complete
> >the conversion data and I haven't worked much with databases since
> >DBaseIII+, and DBaseIV
> >
> >In either of these, I could simply create a nested do loop in a command
> >file, then run it and all would have been great with the world. But lots
> >has changed since those days.
> >
> >Do While .not. EOF()
> > set memID = ID
> > Do While MemID = ID
> > @say "phone"+ chr(9)+ "type"+chr(9)
> > next record
> > loop
> > enddo
> > loop
> >enddo
> >
> >In this, I would use the @say command to output the phone and type
> >information, and the nested do loops would control the stepping through
the
> >database until the end was reached. I am sorry for not keeping current
with
> >VB scripting, etc. Most work has been done with networking, and now I am
> >taking the crash course on DBMS again.
> >
> >Any help is greatly appreciated.
> >
> >Thanks again,
> >
> >Allen
> >
> >
> >"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
> >news:dcg9801f3feqr05tdsmhb1tjhusd2r703j@4ax.com...
> >> 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.
> >
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.


Quantcast