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/20/04


Date: Tue, 20 Apr 2021 12:30:29 -0400

Hello John

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.

Take care,
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 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
> >
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.



Relevant Pages

  • Re: Synchronise sql data to Access
    ... The data returned will be made available in sql ... format. ... the Access database is already set up, the form is yet to be designed. ... but the simplest is to use ODBC. ...
    (microsoft.public.access.queries)
  • Re: Fixed Field Length Issue
    ... Thanks Thomas...the access database approach does seem to work for the most ... In case nobody has a better solution, you can import your Excel ... can set the format of each column and define exactly how may characters wide ... Most of the Excel txt files are tab or comma delimited, ...
    (microsoft.public.excel)
  • Format() Function in MS SQL
    ... I'm pretty new to MS SQL, ... (Which is from my MS ACCESS database) ... How can I do this since format() does ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: VB or?
    ... I know you can use an Access database with it, but it's not the standard, instead using compact SQL for the desktop, or the free version of SQL would be the more "in the box" approaches. ... If you haven't been programming for 12 years, then I'd suggest try the .NET version and see how you go. ... You can buy a .NET standard version and downgrade I believe, but not sure on that status now as officially VB6 is no longer a supported product. ...
    (microsoft.public.vb.general.discussion)
  • Re: Anyone tell me whats wrong with this SQL statement?
    ... That sounds like an SQL error telling you that you are not accounting ... am also working on a Photo Album which uses an Access ... > PS - just to cover every area, the data types in Access database are as ...
    (microsoft.public.dotnet.languages.csharp)