[AccessD] exporting one to many tables into flat file

[John R Bartow]

Thanks Stuart. 
I was hoping I could do it with a query but because of the variable number
of fields created by client's link to an unknown number of organizations, I
think I will have go through the record set as you suggested.
 
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Stuart McLachlan
Sent: Monday, March 20, 2017 4:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] exporting one to many tables into flat file

Hard to say without knowing what structure the CRM program needs for the
flat file.

Generally such application stipulate the required inputs in some detail.
Once you have that, it's just a case of building an appropriate query and
stepping through it as a recordset, using Print# to put the elements into
the output file.

If you mean that each record will have a variable number of fields
(potentially hundreds) then you need a function to build a string which can
be concatenated to the output string, something off the top of my head would
be:

Function Orgs(ClientKey as Long) as String Dim rs as DAO.Recordset DIm
strOut as string Set rs = CurrentDB.OpenRrecordset("Select OrgName from
qryCLientOrgList where ClientFK = " & CLientKey

While not rs.eof
 StrOut = StrOut & "," & rs(0)
 rs.movenext
Wend
 Orgs = strOut
End Function

On 19 Mar 2017 at 23:50, John R Bartow wrote:

> I have a project where I need to export some of the data from a 
> relational database (MDB) to a comma delimited file for import into 
> web based CRM program which utilizes a flat file. The client data will 
> now have one row with a "tag" field for each organization that it is 
> related to (some of these clients are linked to hundreds of 
> organizations). I'm l looking for suggestions on how best to approach
> this:
> 
> 
> 
> Client 1toM > Phone > Mto1 PhoneType
> 
> Client 1toM > ClientOrg <Mto1 Organization
> 
> 
> 
> Thanks in advance,
> 
> John B
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com