EDI 850 Export

[mattc66 via AccessMonster.com]

I need to create an EDI 850 Record Set.

The EDI 850 Record set I am creating consists of 2-5 seperate tables all
linked by the CustomerID and CustomerPO.

Tables -
tblOrder - >Link CustomerID and CustomerPO
-RecordType
-CustomerID ->
-OrderDate
-CustomerPO ->
-ShipDate
-ShipVia
-CarrierName
-ShipAddressID
-Dept
-OrderNum
-CustBuffer
-Source
tblDetail - >Link CustomerID and CustomerPO >Many Lines to one Order
-RecordType
-CustomerID ->
-CustomerPO ->
-Item
-CustomerItem
-OrderQty
-UnitPrice
-Tax1
-Tax2
-ShipDate
-Description
-UPC
-UM
-PO_Line_Number
-ContractNum
-Commission

These all need to be blended together into one comma deliminated text file
like below.

"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
"D","STEVE","PO1","WAGON",10,25.00,"N","N","060831 ","","","","",0
"D","STEVE","PO1","CYCLE",5,15.00,"N","N","060831" ,"","","","",0
"O","FRED","060802","PO2","060825","UPS GRND","","","","0005555","","JOE"
"D","FRED","PO2","WAGON",8,25.00,"N","N","060825", "","","","",0
"D","FRED","PO2","CYCLE",8,15.00,"N","N","060825", "","","","",0

Does anyone have any suggestions on how I could accomplish this task. If so,
please include suggested code examples.

Thanks

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

[Larry Linson]

Unless someone here is an EDI expert, I suspect most are in the same
situation as I am: not the remotest idea what the various items in your
comma-delimited file represent. And, without knowing, I don't see how
anyone could possibly give you detailed suggestions, _much less_ code
examples.

Depending on what those items are, it is possible that you could create a
Query to return the values you need from the Tables you have, and simply use
that Query as the basis for exporting to a delimited file. Formatting might
not be as straightforward as you'd like.

An alternative, would be to create that Query, and write VBA code to process
the values returned, and use File I/O to write the delimited file. If you
don't know VBA already, you'll have some learning curve (how much will
depend on your background in programming). You might find someone willing
to pursue defining the information and providing sample code -- you might
even find someone willing to put in the time and effort to write all the
code (but I'd think that is a bit much to ask in a newsgroup response).

Larry Linson
Microsoft Access MVP


"mattc66 via AccessMonster.com" <u16013@uwewrote in message
news:642d5691a7274@uwe...

>I need to create an EDI 850 Record Set.

The EDI 850 Record set I am creating consists of 2-5 seperate tables all
linked by the CustomerID and CustomerPO.

Tables -
tblOrder - >Link CustomerID and CustomerPO
-RecordType
-CustomerID ->
-OrderDate
-CustomerPO ->
-ShipDate
-ShipVia
-CarrierName
-ShipAddressID
-Dept
-OrderNum
-CustBuffer
-Source
tblDetail - >Link CustomerID and CustomerPO >Many Lines to one Order
-RecordType
-CustomerID ->
-CustomerPO ->
-Item
-CustomerItem
-OrderQty
-UnitPrice
-Tax1
-Tax2
-ShipDate
-Description
-UPC
-UM
-PO_Line_Number
-ContractNum
-Commission

These all need to be blended together into one comma deliminated text file
like below.

"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
"D","STEVE","PO1","WAGON",10,25.00,"N","N","060831 ","","","","",0
"D","STEVE","PO1","CYCLE",5,15.00,"N","N","060831" ,"","","","",0
"O","FRED","060802","PO2","060825","UPS GRND","","","","0005555","","JOE"
"D","FRED","PO2","WAGON",8,25.00,"N","N","060825", "","","","",0
"D","FRED","PO2","CYCLE",8,15.00,"N","N","060825", "","","","",0

Does anyone have any suggestions on how I could accomplish this task. If
so,
please include suggested code examples.

Thanks

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

[mattc66 via AccessMonster.com]

Hi Larry,

I am not asking anyone to do this for me. Rather I was hopeful that maybe
someone may have worked with the EDI 850 Standard and may be able to offer
some direction.

Regardless of the data sample I gave below.

The short question is how can I take 2 tables and combine them into one comma
dilim text file via ACCESS?

The answers I am finding are VBA, with a record set and Looping through the
set.

Thanks for your help I think.

Matt

Larry Linson wrote:

>Unless someone here is an EDI expert, I suspect most are in the same
situation as I am: not the remotest idea what the various items in your
comma-delimited file represent. And, without knowing, I don't see how
anyone could possibly give you detailed suggestions, _much less_ code
examples.

Depending on what those items are, it is possible that you could create a
Query to return the values you need from the Tables you have, and simply use
that Query as the basis for exporting to a delimited file. Formatting might
not be as straightforward as you'd like.

An alternative, would be to create that Query, and write VBA code to process
the values returned, and use File I/O to write the delimited file. If you
don't know VBA already, you'll have some learning curve (how much will
depend on your background in programming). You might find someone willing
to pursue defining the information and providing sample code -- you might
even find someone willing to put in the time and effort to write all the
code (but I'd think that is a bit much to ask in a newsgroup response).

Larry Linson
Microsoft Access MVP

>>I need to create an EDI 850 Record Set.

[quoted text clipped - 48 lines]

>>
Thanks

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

[David W. Fenton]

"mattc66 via AccessMonster.com" <u16013@uwewrote in
news:642e0d7566abc@uwe:

Regardless of the data sample I gave below.

The short question is how can I take 2 tables and combine them
into one comma dilim text file via ACCESS?

The answers I am finding are VBA, with a record set and Looping
through the set.

There's actually one very simple way to do it.

Use DoCmd.TransferText to export to two text files, then use a SHELL
command to run a DOS copy to concatenate the files. It still
requires VBA, but much simpler VBA than the VBA file I/O routines
(which I find obtuse and confusing, mostly because they date from
the days of QuickBasic and have never been updated to behave the way
"normal" VBA commands work).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"mattc66 via AccessMonster.com" <u16013@uwewrote

The short question is how can I take 2
tables and combine them into one comma
dilim text file via ACCESS?

You can retrieve data from two related tables by joining them in a Query.
It appears from your sample that you are only retrieving a subset of the
data from each Table, which you can do in the same Query. It is possible
that you can use that Query as the data source to export to the delimited
file you want, either manually or via code.

The answers I am finding are VBA, with a
record set and Looping through the set.

That would not necessarily be my first choice, if I could do what I need by
exporting from a Query.

And, though David finds File I/O not to his liking, I have no difficulty
with it and it has always worked-as-advertised for me.

Thanks for your help I think.

You are welcome if my comments have been helpful. If not, perhaps you could
clarify what you need and we could pursue the subject to more depth.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:rzrAg.15337$c11.1375@trnddc08:

"mattc66 via AccessMonster.com" <u16013@uwewrote

The short question is how can I take 2
tables and combine them into one comma
dilim text file via ACCESS?

You can retrieve data from two related tables by joining them in a
Query. It appears from your sample that you are only retrieving a
subset of the data from each Table, which you can do in the same
Query. It is possible that you can use that Query as the data
source to export to the delimited file you want, either manually
or via code.

This will not work if the resultsets do not have the same number of
columns. I assumed that was what was needed. Quickbooks does that,
as does the US Federal Election Commission's filing format for
fundraising reports.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Larry Linson]

"David W. Fenton" wrote

The short question is how can I take 2
tables and combine them into one comma
dilim text file via ACCESS?

You can retrieve data from two related
tables by joining them in a Query. It appears
from your sample that you are only
retrieving a subset of the data from each
Table, which you can do in the same Query.
It is possible that you can use that Query as
the data source to export to the delimited
file you want, either manually or via code.

This will not work if the resultsets do
not have the same number of columns.
I assumed that was what was needed.
Quickbooks does that, as does the US
Federal Election Commission's filing
format for fundraising reports.

You've confused me, now, David. Bear in mind that I disclaimed knowledge of
EDI (had an arm's-length nodding acquaintance with it, back in a previous
incarnation as a mainframer). Obviously, you are significantly more
knowledgeable about EDI than I am.

As mattc66 did not want to get into the details of what data from the tables
went into what list item in the delimited list, I tried to answer the
revised question: "The short question is how can I take 2 tables and combine
them into one comma dilim text file via ACCESS?"

My assumption was that the two Tablles were related, could be joined in a
Query, and there'd be one "resultset" that _might_ be usable as the source
for an export to comma-separated-variables (.CSV) file format. My qualms are
about that being directly usable because of the default formats that are
sometimes used for numeric values -- one way or another, the format of the
CSV list items may have to be forced.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:EqzAg.8949$cj7.3141@trnddc01:

"David W. Fenton" wrote

The short question is how can I take 2
tables and combine them into one comma
dilim text file via ACCESS?

You can retrieve data from two related
tables by joining them in a Query. It appears
from your sample that you are only
retrieving a subset of the data from each
Table, which you can do in the same Query.
It is possible that you can use that Query as
the data source to export to the delimited
file you want, either manually or via code.

This will not work if the resultsets do
not have the same number of columns.
I assumed that was what was needed.
Quickbooks does that, as does the US
Federal Election Commission's filing
format for fundraising reports.

You've confused me, now, David. Bear in mind that I disclaimed
knowledge of EDI (had an arm's-length nodding acquaintance with
it, back in a previous incarnation as a mainframer). Obviously,
you are significantly more knowledgeable about EDI than I am.

No, not at all. I just know that certain data exchange formats don't
have the same number of columns in all their sections.

As mattc66 did not want to get into the details of what data from
the tables went into what list item in the delimited list, I tried
to answer the revised question: "The short question is how can I
take 2 tables and combine them into one comma dilim text file via
ACCESS?"

You gave an answer. I gave an answer.

My assumption was that the two Tablles were related, could be
joined in a Query, and there'd be one "resultset" that _might_ be
usable as the source for an export to comma-separated-variables
(.CSV) file format. My qualms are about that being directly usable
because of the default formats that are sometimes used for numeric
values -- one way or another, the format of the CSV list items may
have to be forced.

I assumed they were two unrelated tables with different structures
that need to be combined into a single comma-delimited file, one
that has sections with different record types.

Since we don't know what the OP actually needed, all the answers
could be useful.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/