[AccessD] exporting one to many tables into flat file

[Arthur Fuller]

John,

Years back, I wrote a piece for TechRepublic which may or may not be
relevant. I used the COALESCE keyword to create a comma-delimited column in
the flat file containing all the keys of the child-tables.

Perhaps that is not relevant in your case. Perhaps you need instead to
create one query for each parent-child relationship and then UNION those
results.

Arthur

On Mon, Mar 20, 2017 at 3:25 PM, John R Bartow <jbartow at winhaven.net> wrote:

> This would actually add a field for every organization that is currently
> related. The scheme is to add Tag1, Tag2, Tag3, etc.
>
> I've done a lot of flat file to relational db migrations and quite a few
> normalizations of weak RDMSs but I haven't had to do this before. I started
> on System 36 which was a predecessor to the AS400 so that's my reference
> point. That's what they used to do on those systems - "tag" everything in a
> new field. I hated that idea. It hurts my brain to think this way which is
> why I'm asking.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Rocky Smolin
> Sent: Monday, March 20, 2017 9:54 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] exporting one to many tables into flat file
>
> 1toM is hard to represent in a flat file.  The many should, in theory be
> strung out in columns but the problem is how many columns to allow for? For
> a CSV file the problem is not as great because you can just create a string
> of the primary fields, and keep on concatenating the Many values (the
> Organizations) to the end of the string, separated by commas. You should
> end
> up with a nice CSV file with the records each containing a different number
> of fields.
>
> Which of course, means on the import, you need to parse the string and
> extract the n number of organizations.  Since it's a CRM app with , I
> assume, some kind of import function that may be problematical since an
> import function typically wants to see a consistent number of fields in an
> import file.
>
> If the CRM app is based on a database into which you can push data then a
> stand-alone importer would work.  But, of course, at that point you could
> just push the data right from the MDB into the CRM database.  Is that an
> option?
>
> If, however, you need to use the CRM imported, and the importer needs a
> consistent record with the same number of fields in the same place, , then
> I
> think your solution is to create an export record with one Organization per
> record.  Would that work?
>
> HTH
>
>
> Rocky Smolin
> Beach Access Software
> 760-683-5777
> www.bchacc.com
> www.e-z-mrp.com
> Skype: rocky.smolin
>
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> John R Bartow
> Sent: Sunday, March 19, 2017 9:50 PM
> To: DBA-Access
> Subject: [AccessD] exporting one to many tables into flat file
> Importance: High
>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Arthur