Creating comma dilimited text file with one-to-many relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This project exports data from Quickbooks and formats them in Access to send
a text file to a warehouse. The data contain customer and order detail
information, whereby one customer can have more than one item on order. I
can create a report that does summarize the detail records under each
customer portion without repeating the customer info, but I cannot find a way
to export this to a clean comma seperated text file with double quotes around
each text field. On the other hand, when generating the text file from the
query, each order detail record repeats all the customer info, which should
only show up once at the beginning of each text record. Each text record is
identified by "SO" at the start, and each order detail record with "ITEM" at
the beginning of this portion. How can i suppress the unwanted repetition of
customer info at the beginning of each order detail record for the same
customer and export this to a text file?
 
It not clear in your description if you actually trying to export repeating
data, but for each additional line of data, you don't want the fields
repeated.

This seems *very* strange request. Are the additional repeating lines simply
to suppress only *SOME* fields of data?

eg:

CompanyName SalesMonth Amount
IBM 1
$1000
IBM 2 $1200
IBM 3
$900

is to become:

CompanyName SalesMonth Amount
IBM 1
$1000
2
$1200
3
$900

So, we are to export the above with rows 2 and 3 surpessing the company name
as a csv file?

eg:

"ibm","1","$1000"
"","2","$1200"
"","3","$900"

That does seem a bit strange.

Perhaps you want to just the 'totals" and ONE LINE of data to be exported?
If yes, then simply try using the summation symbol in the query builder
(that is the group by). This should allow you to produce something like

"ibm","$3100"

So, you can "group by" and summary total in a query.

However, if you actually need the first example in which multiple lines of
data is exported for one customer but you need ONLY SOME COLUMNS OF the
export to repeat, then you have to write code to do the export. Check out
the help for open, write, print commands.

A basic output to a text file looks like:

Dim strFile As String
Dim intF As Integer

strFile = "c:\my data\MyData.txt"

intF = FreeFile()
Open strFile For output As #intF

print #intf,"hello"
Close intF

So, you can use code to read a table..and loop and write out the contents of
the data any way you want.

So , if you can't use the query builder and "group by" with the query to
produce one line of summary data, then you may very well have to write some
custom output code.
 
Here is a sample of the text file that I try to create:

"SO","2157","Chris Evans","16120 SE Clackamas RD
PMB152",,"Clackamas","OR","97015",,"Chris
Evans",,"Clackamas","OR","97015",,"5037204866","US Mail",5.25,"Non",5/2/2007
0:00:00,0.00,"item","250","Picture This Travel Mug",3.00,12.95
"SO","2152","Claire Louisius","P.O. Box
640377",,"Miami","FL","33164",,"Claire
Louisius",,"Plantation","FL","33324",,"954 474 8278","Federal
Express",3.25,"Non",5/2/2007 0:00:00,0.00,"item","130","wake up words -
Happiness",1.00,2.95,"item","131","wake up words
-Virtues",1.00,2.95,"item","132","wake up words -
Spirit",1.00,2.95,"item","100","Giving Notes Joy",5.00,1.50
"SO","436-web","elizabeth curtis","12516 del vino court",,"san
diego","CA","92130",,"elizabeth curtis",,"san
diego","CA","92130",,"8584994555","Method not set",6.86,"Tax",5/10/2007
0:00:00,0.08,"item","250R","Classic Travel Mug Web Retail",1.00,12.95

Each new order is prefixed by "SO", and each item in the order with "Item".
I do not want to duplicate the "SO" information if there is more than one
item.

Thanks,
Arie
 
"SO","2157","Chris Evans","16120 SE Clackamas RD
PMB152",,"Clackamas","OR","97015"

,,"Chris Evans",,"Clackamas","OR","97015",,"5037204866","US
Mail",5.25,"Non",5/2/2007
0:00:00,0.00,"item","250","Picture This Travel Mug",3.00,12.95

Why in the above is the field that follows the name different for the 2nd
record? (no wonder you can't export!!! the columns from one record to the
next don't match up!!!).

You have to explain why the columns in the above two records don't match.
(or, split it above to make things more clear. I certainly see now what you
mean about the "so". Is the "so" part of the actual data in a record, or are
you planning to add this column?

If the so column is not present in the data,, then simply add it to the
query we are building for this export.

You can make the first column a "fixed" constant:

SoCollum:"so"

The above will make the first column a "so".

So, what we will do is send the data to a temp table. We can then run a loop
to "empty out" the repeating "so" column. However, you not explained why in
the sample data that the first two records have different fields data? I
understand that some data should be different, but what is the address
information diffent? (should not those be the same? -- if not, then you
complete 100% have lost me!!).

Further, what (or how) are you setting the order the export (this is
critical information if we are to process the data in this temp table to
remove the repeating so).
 
As stated, and please trust me, "SO" starts customer information for each
order. The first records are for billing information, and the second set,
often - but not always- identical, contains shipping information. These are
all contained within one record.
Arie
 
Albert,

Perhaps it will be helpful to get you a copy of the underlying Access data?
I will try and send a copy to the email address you provide below.
Thanks,
Arie
 
Hello,
I have tried to send you the database, but can't find a way to do that.
You do mention setting up a temp table and running a loop to delete the
duplicate beginnings of records. Can you please guide me in this direction.
Regards,
Arie Niernberger
 
ArieBen said:
Albert,

Perhaps it will be helpful to get you a copy of the underlying Access data?
I will try and send a copy to the email address you provide below.
Thanks,
Arie
Click to expand...
 
Back
Top