[AccessD] exporting one to many tables into flat file

[John R Bartow]

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