Break Excel into Header/Footer

[Maureen]

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO has
8-10 products. I would like to stuff it into our EDI tables for autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when you
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Maureen]

Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in the EDI
code but I am at a loss since I know very very little about VB code. I can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader... all of
the other details will be added when it is processed it gets matched with
the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app stuffs into
our order entry system. It also validates customer and product numbers with
an e-mail to summarize any problems. The spreadsheet was extracted from a
database to start and I am confident that the data is clean these PO's are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will be
one OrderNumber (CODAT1) for each of these. perhaps the header records could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for the next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same number to
be used twice.

This all may be a little unorthodox, but if I can get this info stuffed in I
can save hours manually entering almost 10,000 lines of orders Monday AM!

M

Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when you
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO has
8-10 products. I would like to stuff it into our EDI tables for autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in the EDI
code but I am at a loss since I know very very little about VB code. I can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader... all of
the other details will be added when it is processed it gets matched with
the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app stuffs into
our order entry system. It also validates customer and product numbers with
an e-mail to summarize any problems. The spreadsheet was extracted from a
database to start and I am confident that the data is clean these PO's are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will be
one OrderNumber (CODAT1) for each of these. perhaps the header records could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for the next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same number to
be used twice.

This all may be a little unorthodox, but if I can get this info stuffed in I
can save hours manually entering almost 10,000 lines of orders Monday AM!

M

Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when you
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO has
8-10 products. I would like to stuff it into our EDI tables for autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

Tom,

Thanks for your reply. I have put off entering these just yet since we will
not recieve the inventory to fill them until Friday... I have until Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order whrn they
change, a new order has started. I spent a few minutes looking at what I
have and see that it is easy to group into unique CUSTID + PO combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e. start at
88,000 for header record 1 header record 2 would be 88,001 and so on) from
the completed header records I could match trhe unique CUSTID + PO to the
data and stuff in the footer records with the correct order#. I don't know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in the EDI
code but I am at a loss since I know very very little about VB code. I can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader... all of
the other details will be added when it is processed it gets matched with
the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app stuffs into
our order entry system. It also validates customer and product numbers with
an e-mail to summarize any problems. The spreadsheet was extracted from a
database to start and I am confident that the data is clean these PO's are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will be
one OrderNumber (CODAT1) for each of these. perhaps the header records could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for the next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same number to
be used twice.

This all may be a little unorthodox, but if I can get this info stuffed in I
can save hours manually entering almost 10,000 lines of orders Monday AM!

M

Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when you
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO has
8-10 products. I would like to stuff it into our EDI tables for autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks for your reply. I have put off entering these just yet since we will
not recieve the inventory to fill them until Friday... I have until Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order whrn they
change, a new order has started. I spent a few minutes looking at what I
have and see that it is easy to group into unique CUSTID + PO combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e. start at
88,000 for header record 1 header record 2 would be 88,001 and so on) from
the completed header records I could match trhe unique CUSTID + PO to the
data and stuff in the footer records with the correct order#. I don't know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in the EDI
code but I am at a loss since I know very very little about VB code. I can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader... all of
the other details will be added when it is processed it gets matched with
the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app stuffs into
our order entry system. It also validates customer and product numbers with
an e-mail to summarize any problems. The spreadsheet was extracted from a
database to start and I am confident that the data is clean these PO's are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will be
one OrderNumber (CODAT1) for each of these. perhaps the header records could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for the next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same number to
be used twice.

This all may be a little unorthodox, but if I can get this info stuffed in I
can save hours manually entering almost 10,000 lines of orders Monday AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when you
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO
has
8-10 products. I would like to stuff it into our EDI tables for autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

Tom,

The current order number is part of the VB order processing code and looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks for your reply. I have put off entering these just yet since we will
not recieve the inventory to fill them until Friday... I have until Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order whrn they
change, a new order has started. I spent a few minutes looking at what I
have and see that it is easy to group into unique CUSTID + PO combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e. start at
88,000 for header record 1 header record 2 would be 88,001 and so on) from
the completed header records I could match trhe unique CUSTID + PO to the
data and stuff in the footer records with the correct order#. I don't know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in the EDI
code but I am at a loss since I know very very little about VB code. I can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader...

all
of

the other details will be added when it is processed it gets matched with
the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app stuffs into
our order entry system. It also validates customer and product numbers with
an e-mail to summarize any problems. The spreadsheet was extracted

from
a

database to start and I am confident that the data is clean these PO's are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will be
one OrderNumber (CODAT1) for each of these. perhaps the header records could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for

the
next

number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same

number
to

be used twice.

This all may be a little unorthodox, but if I can get this info

stuffed
in I

can save hours manually entering almost 10,000 lines of orders Monday AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when yo u
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO
has
8-10 products. I would like to stuff it into our EDI tables for autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

The current order number is part of the VB order processing code and looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks for your reply. I have put off entering these just yet since we will
not recieve the inventory to fill them until Friday... I have until Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order whrn they
change, a new order has started. I spent a few minutes looking at what I
have and see that it is easy to group into unique CUSTID + PO combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e. start at
88,000 for header record 1 header record 2 would be 88,001 and so on) from
the completed header records I could match trhe unique CUSTID + PO to the
data and stuff in the footer records with the correct order#. I don't know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in the EDI
code but I am at a loss since I know very very little about VB code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader... all
of
the other details will be added when it is processed it gets matched with
the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app stuffs into
our order entry system. It also validates customer and product numbers
with
an e-mail to summarize any problems. The spreadsheet was extracted from
a
database to start and I am confident that the data is clean these PO's
are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order
number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will be
one OrderNumber (CODAT1) for each of these. perhaps the header records
could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for the
next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same number
to
be used twice.

This all may be a little unorthodox, but if I can get this info stuffed
in I
can save hours manually entering almost 10,000 lines of orders Monday AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when yo u
link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each PO
has
8-10 products. I would like to stuff it into our EDI tables for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

This code is from a Visual Basic project the is not yet been made into an
exe file. The edi app is an exe program that sits running waiting for the
communication part to say "GO" There is no "Public Function" that I can see,
There is 100's or even 1,000's of lines of code in the project. I can't be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

The current order number is part of the VB order processing code and looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks for your reply. I have put off entering these just yet since we will
not recieve the inventory to fill them until Friday... I have until Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order

whrn
they

change, a new order has started. I spent a few minutes looking at what I
have and see that it is easy to group into unique CUSTID + PO combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e.

start
at

88,000 for header record 1 header record 2 would be 88,001 and so on) from
the completed header records I could match trhe unique CUSTID + PO to the
data and stuff in the footer records with the correct order#. I don't know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom the edi app has a table to issue order numbers. Each order that comes
take the next number and resets it. I can see whats happening in

the
EDI

code but I am at a loss since I know very very little about VB code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader... all
of
the other details will be added when it is processed it gets

matched
with

the main Customer Table. The same is true for Product I only need product
code and qty everything else gets dealt with when the EDI app

stuffs
into

our order entry system. It also validates customer and product numbers
with
an e-mail to summarize any problems. The spreadsheet was extracted from
a
database to start and I am confident that the data is clean these PO's
are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order
number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there

will
be

one OrderNumber (CODAT1) for each of these. perhaps the header records
could
be sequentially numbered and this number added to the last Used CODAT1.
After I import all of these I could then reset CODAT1 to update for the
next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same number
to
be used twice.

This all may be a little unorthodox, but if I can get this info stuffed
in I
can save hours manually entering almost 10,000 lines of orders

Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I don't
see from where you're going to get the Order#. Also, I see some other
problems here:

- In a good database design, you would probably not have Name. There
would be a separate table giving Name based on CustID. Doesn't CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were different
for two different lines within the same order. A database can easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when

yo
u

link it into Access. The name you create there should be substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and

each
PO

has
8-10 products. I would like to stuff it into our EDI tables for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

This code is from a Visual Basic project the is not yet been made into an
exe file. The edi app is an exe program that sits running waiting for the
communication part to say "GO" There is no "Public Function" that I can see,
There is 100's or even 1,000's of lines of code in the project. I can't be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

The current order number is part of the VB order processing code and looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Thanks for your reply. I have put off entering these just yet since we
will
not recieve the inventory to fill them until Friday... I have until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order whrn
they
change, a new order has started. I spent a few minutes looking at what I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e. start
at
88,000 for header record 1 header record 2 would be 88,001 and so on)
from
the completed header records I could match trhe unique CUSTID + PO to the
data and stuff in the footer records with the correct order#. I don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order ends and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom the edi app has a table to issue order numbers. Each order that
comes
take the next number and resets it. I can see whats happening in the
EDI
code but I am at a loss since I know very very little about VB code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader...
all
of
the other details will be added when it is processed it gets matched
with
the main Customer Table. The same is true for Product I only need
product
code and qty everything else gets dealt with when the EDI app stuffs
into
our order entry system. It also validates customer and product numbers
with
an e-mail to summarize any problems. The spreadsheet was extracted
from
a
database to start and I am confident that the data is clean these PO's
are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order
number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO# there will
be
one OrderNumber (CODAT1) for each of these. perhaps the header records
could
be sequentially numbered and this number added to the last Used
CODAT1.
After I import all of these I could then reset CODAT1 to update for
the
next
number. I have had to do this on one or two occasions when the EDI app
crashed and the number did not update. It willnot allow the same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this info
stuffed
in I
can save hours manually entering almost 10,000 lines of orders Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different ProdCode and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I
don't
see from where you're going to get the Order#. Also, I see some
other
problems here:

- In a good database design, you would probably not have Name.
There
would be a separate table giving Name based on CustID. Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A database can
easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when yo
u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and each
PO
has
8-10 products. I would like to stuff it into our EDI tables for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

Sorry to say that qryOCONT is a table three fields and one entry only as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine. All I
know is that it is stable and works great processing 1000's of lines of
orders every day. I have a copy of the code on my laptop as a VB project and
as an exe file that runs. I know that id i an get these orders stuffed into
the EDI tables from there all is well since I can run the 'process' function
that stuffs them into our live app. I have tried this by entering manually
the data and processing it.

M

Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

This code is from a Visual Basic project the is not yet been made into an
exe file. The edi app is an exe program that sits running waiting for the
communication part to say "GO" There is no "Public Function" that I can see,
There is 100's or even 1,000's of lines of code in the project. I can't be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

The current order number is part of the VB order processing code and looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom,

Thanks for your reply. I have put off entering these just yet since we
will
not recieve the inventory to fill them until Friday... I have until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order whrn
they
change, a new order has started. I spent a few minutes looking at

what
I

have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add a new field
for each record that auto increments from a predefined start (i.e. start
at
88,000 for header record 1 header record 2 would be 88,001 and so on)
from
the completed header records I could match trhe unique CUSTID + PO

to
the

data and stuff in the footer records with the correct order#. I don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number until
a new order is begin. Given your description of the spreadsheet data,
it isn't clear to me exactly how you can tell where one order

ends
and

another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom the edi app has a table to issue order numbers. Each order that
comes
take the next number and resets it. I can see whats happening in the
EDI
code but I am at a loss since I know very very little about VB code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader...
all
of
the other details will be added when it is processed it gets matched
with
the main Customer Table. The same is true for Product I only need
product
code and qty everything else gets dealt with when the EDI app stuffs
into
our order entry system. It also validates customer and product numbers
with
an e-mail to summarize any problems. The spreadsheet was extracted
from
a
database to start and I am confident that the data is clean

these
PO's

are
being transferred to us. I can see how the distinct will look after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the order
number
issued for each header and them match it with the corresponding footer
records. The relationship will be based on the CustID + PO#

there
will

be
one OrderNumber (CODAT1) for each of these. perhaps the header records
could
be sequentially numbered and this number added to the last Used
CODAT1.
After I import all of these I could then reset CODAT1 to update for
the
next
number. I have had to do this on one or two occasions when the

EDI
app

crashed and the number did not update. It willnot allow the same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this info
stuffed
in I
can save hours manually entering almost 10,000 lines of orders Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from which
you have a "Header" each with (potentially) a different

ProdCode
and

Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I
don't
see from where you're going to get the Order#. Also, I see some
other
problems here:

- In a good database design, you would probably not have Name.
There
would be a separate table giving Name based on CustID. Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A database can
easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet

when
yo

u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers

and
each

PO
has
8-10 products. I would like to stuff it into our EDI tables for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Sorry to say that qryOCONT is a table three fields and one entry only as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine. All I
know is that it is stable and works great processing 1000's of lines of
orders every day. I have a copy of the code on my laptop as a VB project and
as an exe file that runs. I know that id i an get these orders stuffed into
the EDI tables from there all is well since I can run the 'process' function
that stuffs them into our live app. I have tried this by entering manually
the data and processing it.

M

Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

This code is from a Visual Basic project the is not yet been made into an
exe file. The edi app is an exe program that sits running waiting for the
communication part to say "GO" There is no "Public Function" that I can see,
There is 100's or even 1,000's of lines of code in the project. I can't be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

The current order number is part of the VB order processing code and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom,

Thanks for your reply. I have put off entering these just yet since we
will
not recieve the inventory to fill them until Friday... I have until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order
whrn
they
change, a new order has started. I spent a few minutes looking at what
I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add a new
field
for each record that auto increments from a predefined start (i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001 and so on)
from
the completed header records I could match trhe unique CUSTID + PO to
the
data and stuff in the footer records with the correct order#. I don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could do
the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number
until
a new order is begin. Given your description of the spreadsheet
data,
it isn't clear to me exactly how you can tell where one order ends
and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending on your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each order that
comes
take the next number and resets it. I can see whats happening in
the
EDI
code but I am at a loss since I know very very little about VB
code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader...
all
of
the other details will be added when it is processed it gets
matched
with
the main Customer Table. The same is true for Product I only need
product
code and qty everything else gets dealt with when the EDI app
stuffs
into
our order entry system. It also validates customer and product
numbers
with
an e-mail to summarize any problems. The spreadsheet was extracted
from
a
database to start and I am confident that the data is clean these
PO's
are
being transferred to us. I can see how the distinct will look
after
creating a unique header for each order and how the footer will get
populated with all of the entries. The only issue is to get the
order
number
issued for each header and them match it with the corresponding
footer
records. The relationship will be based on the CustID + PO# there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the header
records
could
be sequentially numbered and this number added to the last Used
CODAT1.
After I import all of these I could then reset CODAT1 to update for
the
next
number. I have had to do this on one or two occasions when the EDI
app
crashed and the number did not update. It willnot allow the same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this info
stuffed
in I
can save hours manually entering almost 10,000 lines of orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from
which
you have a "Header" each with (potentially) a different ProdCode
and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that I
don't
see from where you're going to get the Order#. Also, I see some
other
problems here:

- In a good database design, you would probably not have Name.
There
would be a separate table giving Name based on CustID. Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A database can
easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet when
yo
u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in
more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and
each
PO
has
8-10 products. I would like to stuff it into our EDI tables for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

Tom,

Yes, this will work, I am planning to prceed as follows starting in off
hours
1) Backup
2) E-mail to advise EDI users that we will be offline for maintenace
3) Take EDI offline
4) Stuff these into the EDI tables with thier own order numbers (start at
200000 is fine)
5) Process these orders into our live order entry we have an
'offline/manual' EDI order process function that processed orders into order
entry in the event of a lock-up
6) Review a sample of the porcessed orders
7) Bring EDI app back online and continue

This will take any interaction with the EDI app out of the picture from your
perspective. I appreciate greatly your help here and I am confident that
this will work. If I can get it done tonight, we're good if not, I have data
entery overtime scheduled as a backup plan... they will rekey this stuff.

M

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Sorry to say that qryOCONT is a table three fields and one entry only as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine. All I
know is that it is stable and works great processing 1000's of lines of
orders every day. I have a copy of the code on my laptop as a VB project and
as an exe file that runs. I know that id i an get these orders stuffed into
the EDI tables from there all is well since I can run the 'process' function
that stuffs them into our live app. I have tried this by entering manually
the data and processing it.

M

Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This code is from a Visual Basic project the is not yet been made into an
exe file. The edi app is an exe program that sits running waiting for the
communication part to say "GO" There is no "Public Function" that I

can
see,

There is 100's or even 1,000's of lines of code in the project. I

can't
be

more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom,

The current order number is part of the VB order processing code and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO, adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you experiment
with this.

Do you get the ascending order numbers this way? Once you do, change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom,

Thanks for your reply. I have put off entering these just yet

since
we

will
not recieve the inventory to fill them until Friday... I have until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order
whrn
they
change, a new order has started. I spent a few minutes looking

at
what

I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add a new
field
for each record that auto increments from a predefined start (i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001 and

so
on)

from
the completed header records I could match trhe unique CUSTID +

PO
to

the
data and stuff in the footer records with the correct order#. I don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you

could
do

the same, perhaps calling the function that assigns the order number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number
until
a new order is begin. Given your description of the spreadsheet
data,
it isn't clear to me exactly how you can tell where one order ends
and
another starts. Is that based on CustID / PO? That is, every line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just using
the existing function that assigns order numbers. Depending

on
your

answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each

order
that

comes
take the next number and resets it. I can see whats happening in
the
EDI
code but I am at a loss since I know very very little about VB
code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into tblEDIHeader...
all
of
the other details will be added when it is processed it gets
matched
with
the main Customer Table. The same is true for Product I only need
product
code and qty everything else gets dealt with when the EDI app
stuffs
into
our order entry system. It also validates customer and product
numbers
with
an e-mail to summarize any problems. The spreadsheet was extracted
from
a
database to start and I am confident that the data is clean these
PO's
are
being transferred to us. I can see how the distinct will look
after
creating a unique header for each order and how the footer

will
get

populated with all of the entries. The only issue is to get the
order
number
issued for each header and them match it with the corresponding
footer
records. The relationship will be based on the CustID + PO# there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the header
records
could
be sequentially numbered and this number added to the last Used
CODAT1.
After I import all of these I could then reset CODAT1 to

update
for

the
next
number. I have had to do this on one or two occasions when

the
EDI

app
crashed and the number did not update. It willnot allow the same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this info
stuffed
in I
can save hours manually entering almost 10,000 lines of orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from
which
you have a "Header" each with (potentially) a different ProdCode
and
Qty. You should have only one "Header" per order, but multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except

that
I

don't
see from where you're going to get the Order#. Also, I see some
other
problems here:

- In a good database design, you would probably not have Name.
There
would be a separate table giving Name based on CustID. Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A database can
easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the

spreadsheet
when

yo
u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in
more
than one way.

There are ways of detecting these problems, and even of creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers and
each
PO
has
8-10 products. I would like to stuff it into our EDI

tables
for

autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

I'm glad if you feel this creates a starting point for you.

Please come back if you have any specific questions in doing this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Yes, this will work, I am planning to prceed as follows starting in off
hours
1) Backup
2) E-mail to advise EDI users that we will be offline for maintenace
3) Take EDI offline
4) Stuff these into the EDI tables with thier own order numbers (start at
200000 is fine)
5) Process these orders into our live order entry we have an
'offline/manual' EDI order process function that processed orders into order
entry in the event of a lock-up
6) Review a sample of the porcessed orders
7) Bring EDI app back online and continue

This will take any interaction with the EDI app out of the picture from your
perspective. I appreciate greatly your help here and I am confident that
this will work. If I can get it done tonight, we're good if not, I have data
entery overtime scheduled as a backup plan... they will rekey this stuff.

M

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Sorry to say that qryOCONT is a table three fields and one entry only as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine. All I
know is that it is stable and works great processing 1000's of lines of
orders every day. I have a copy of the code on my laptop as a VB project and
as an exe file that runs. I know that id i an get these orders stuffed into
the EDI tables from there all is well since I can run the 'process' function
that stuffs them into our live app. I have tried this by entering manually
the data and processing it.

M


Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This code is from a Visual Basic project the is not yet been made into an
exe file. The edi app is an exe program that sits running waiting for the
communication part to say "GO" There is no "Public Function" that I can
see,
There is 100's or even 1,000's of lines of code in the project. I can't
be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom,

The current order number is part of the VB order processing code and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO,
adding
this column as a calculated function. In the Query Design Grid, for
Field, put in "OrderNumber: = FunctionName()" where FunctionName is
replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you
experiment
with this.

Do you get the ascending order numbers this way? Once you do,
change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in
the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 22 Mar 2004 20:54:33 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

Thanks for your reply. I have put off entering these just yet since
we
will
not recieve the inventory to fill them until Friday... I have until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order
whrn
they
change, a new order has started. I spent a few minutes looking at
what
I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add a new
field
for each record that auto increments from a predefined start (i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001 and so
on)
from
the completed header records I could match trhe unique CUSTID + PO
to
the
data and stuff in the footer records with the correct order#. I
don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could
do
the same, perhaps calling the function that assigns the order
number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number
until
a new order is begin. Given your description of the spreadsheet
data,
it isn't clear to me exactly how you can tell where one order
ends
and
another starts. Is that based on CustID / PO? That is, every
line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just
using
the existing function that assigns order numbers. Depending on
your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each order
that
comes
take the next number and resets it. I can see whats happening in
the
EDI
code but I am at a loss since I know very very little about VB
code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into
tblEDIHeader...
all
of
the other details will be added when it is processed it gets
matched
with
the main Customer Table. The same is true for Product I only
need
product
code and qty everything else gets dealt with when the EDI app
stuffs
into
our order entry system. It also validates customer and product
numbers
with
an e-mail to summarize any problems. The spreadsheet was
extracted
from
a
database to start and I am confident that the data is clean
these
PO's
are
being transferred to us. I can see how the distinct will look
after
creating a unique header for each order and how the footer will
get
populated with all of the entries. The only issue is to get the
order
number
issued for each header and them match it with the corresponding
footer
records. The relationship will be based on the CustID + PO#
there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the header
records
could
be sequentially numbered and this number added to the last Used
CODAT1.
After I import all of these I could then reset CODAT1 to update
for
the
next
number. I have had to do this on one or two occasions when the
EDI
app
crashed and the number did not update. It willnot allow the same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this info
stuffed
in I
can save hours manually entering almost 10,000 lines of orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns from
which
you have a "Header" each with (potentially) a different
ProdCode
and
Qty. You should have only one "Header" per order, but
multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that
I
don't
see from where you're going to get the Order#. Also, I see
some
other
problems here:

- In a good database design, you would probably not have Name.
There
would be a separate table giving Name based on CustID.
Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for
each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A database can
easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet
when
yo
u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered in
more
than one way.

There are ways of detecting these problems, and even of
creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers
and
each
PO
has
8-10 products. I would like to stuff it into our EDI tables
for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

Tom,

Can you please clarify how I can create the field with the incremental order
number in an append query. There will be a new number issued for each
CUSTID/PO whenever this combination changes the next number will be used.

M


Can you clarify how to create the

Dear Maureen:

I'm glad if you feel this creates a starting point for you.

Please come back if you have any specific questions in doing this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Yes, this will work, I am planning to prceed as follows starting in off
hours
1) Backup
2) E-mail to advise EDI users that we will be offline for maintenace
3) Take EDI offline
4) Stuff these into the EDI tables with thier own order numbers (start at
200000 is fine)
5) Process these orders into our live order entry we have an
'offline/manual' EDI order process function that processed orders into order
entry in the event of a lock-up
6) Review a sample of the porcessed orders
7) Bring EDI app back online and continue

This will take any interaction with the EDI app out of the picture from your
perspective. I appreciate greatly your help here and I am confident that
this will work. If I can get it done tonight, we're good if not, I have data
entery overtime scheduled as a backup plan... they will rekey this stuff.

M

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Sorry to say that qryOCONT is a table three fields and one entry only as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine.

All
I

know is that it is stable and works great processing 1000's of lines of
orders every day. I have a copy of the code on my laptop as a VB

project
and

as an exe file that runs. I know that id i an get these orders stuffed into
the EDI tables from there all is well since I can run the 'process' function
that stuffs them into our live app. I have tried this by entering manually
the data and processing it.

M


Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

This code is from a Visual Basic project the is not yet been made

into
an

exe file. The edi app is an exe program that sits running waiting

for
the

communication part to say "GO" There is no "Public Function" that I can
see,
There is 100's or even 1,000's of lines of code in the project. I can't
be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Tom,

The current order number is part of the VB order processing code and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order number"
then start with a select query that provides the CustID and PO,
adding
this column as a calculated function. In the Query Design

Grid,
for

Field, put in "OrderNumber: = FunctionName()" where

FunctionName
is

replaced by the name of the function that assigns an order number.

I recommend using a scratch copy of the database while you
experiment
with this.

Do you get the ascending order numbers this way? Once you do,
change
the query to an append query, adding rows to your table of orders.
Note that the 3 columns from this query must match the columns in
the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 22 Mar 2004 20:54:33 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

Thanks for your reply. I have put off entering these just yet since
we
will
not recieve the inventory to fill them until Friday... I have until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an order
whrn
they
change, a new order has started. I spent a few minutes

looking
at

what
I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add

a
new

field
for each record that auto increments from a predefined start (i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001

and
so

on)
from
the completed header records I could match trhe unique CUSTID

+
PO

to
the
data and stuff in the footer records with the correct order#. I
don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you could
do
the same, perhaps calling the function that assigns the order
number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once, when
inserting a new header. The footer must then follow that number
until
a new order is begin. Given your description of the spreadsheet
data,
it isn't clear to me exactly how you can tell where one order
ends
and
another starts. Is that based on CustID / PO? That is, every
line
that is from the same customer and has the same PO is an order.

I'm trying to get you a query based solution, founded on just
using
the existing function that assigns order numbers.

Depending
on

your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each order
that
comes
take the next number and resets it. I can see whats

happening
in

the
EDI
code but I am at a loss since I know very very little

about
VB

code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into
tblEDIHeader...
all
of
the other details will be added when it is processed it gets
matched
with
the main Customer Table. The same is true for Product I only
need
product
code and qty everything else gets dealt with when the EDI app
stuffs
into
our order entry system. It also validates customer and product
numbers
with
an e-mail to summarize any problems. The spreadsheet was
extracted
from
a
database to start and I am confident that the data is clean
these
PO's
are
being transferred to us. I can see how the distinct will look
after
creating a unique header for each order and how the footer will
get
populated with all of the entries. The only issue is to

get
the

order
number
issued for each header and them match it with the corresponding
footer
records. The relationship will be based on the CustID + PO#
there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the header
records
could
be sequentially numbered and this number added to the last Used
CODAT1.
After I import all of these I could then reset CODAT1 to update
for
the
next
number. I have had to do this on one or two occasions when the
EDI
app
crashed and the number did not update. It willnot allow

the
same

number
to
be used twice.

This all may be a little unorthodox, but if I can get this info
stuffed
in I
can save hours manually entering almost 10,000 lines of orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of

columns
from

which
you have a "Header" each with (potentially) a different
ProdCode
and
Qty. You should have only one "Header" per order, but
multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could

create
the

Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except that
I
don't
see from where you're going to get the Order#. Also, I see
some
other
problems here:

- In a good database design, you would probably not have Name.
There
would be a separate table giving Name based on CustID.
Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for
each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A

database
can

easily
constrain this with proper design, but your spreadsheet won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the spreadsheet
when
yo
u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is

entered
in

more
than one way.

There are ways of detecting these problems, and even of
creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers
and
each
PO
has
8-10 products. I would like to stuff it into our EDI tables
for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

If you make the Order Number an autonumber, you can just omit it when
you perform the append query. It will issue the numbering for you
automatically. The only trick is setting up the initial value. I
haven't done this in recent years, so I suggest you ask a question in
this newsgroup. I'm sure you'll get the information that way, if you
don't find it in online help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Can you please clarify how I can create the field with the incremental order
number in an append query. There will be a new number issued for each
CUSTID/PO whenever this combination changes the next number will be used.

M


Can you clarify how to create the

Dear Maureen:

I'm glad if you feel this creates a starting point for you.

Please come back if you have any specific questions in doing this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Yes, this will work, I am planning to prceed as follows starting in off
hours
1) Backup
2) E-mail to advise EDI users that we will be offline for maintenace
3) Take EDI offline
4) Stuff these into the EDI tables with thier own order numbers (start at
200000 is fine)
5) Process these orders into our live order entry we have an
'offline/manual' EDI order process function that processed orders into order
entry in the event of a lock-up
6) Review a sample of the porcessed orders
7) Bring EDI app back online and continue

This will take any interaction with the EDI app out of the picture from your
perspective. I appreciate greatly your help here and I am confident that
this will work. If I can get it done tonight, we're good if not, I have data
entery overtime scheduled as a backup plan... they will rekey this stuff.

M

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Sorry to say that qryOCONT is a table three fields and one entry only as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine. All
I
know is that it is stable and works great processing 1000's of lines of
orders every day. I have a copy of the code on my laptop as a VB project
and
as an exe file that runs. I know that id i an get these orders stuffed
into
the EDI tables from there all is well since I can run the 'process'
function
that stuffs them into our live app. I have tried this by entering
manually
the data and processing it.

M


Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

This code is from a Visual Basic project the is not yet been made into
an
exe file. The edi app is an exe program that sits running waiting for
the
communication part to say "GO" There is no "Public Function" that I
can
see,
There is 100's or even 1,000's of lines of code in the project. I
can't
be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is, but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Mar 2004 21:49:18 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

The current order number is part of the VB order processing code
and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order
number"
then start with a select query that provides the CustID and PO,
adding
this column as a calculated function. In the Query Design Grid,
for
Field, put in "OrderNumber: = FunctionName()" where FunctionName
is
replaced by the name of the function that assigns an order
number.

I recommend using a scratch copy of the database while you
experiment
with this.

Do you get the ascending order numbers this way? Once you do,
change
the query to an append query, adding rows to your table of
orders.
Note that the 3 columns from this query must match the columns in
the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT
query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 22 Mar 2004 20:54:33 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

Thanks for your reply. I have put off entering these just yet
since
we
will
not recieve the inventory to fill them until Friday... I have
until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an
order
whrn
they
change, a new order has started. I spent a few minutes looking
at
what
I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add a
new
field
for each record that auto increments from a predefined start
(i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001 and
so
on)
from
the completed header records I could match trhe unique CUSTID +
PO
to
the
data and stuff in the footer records with the correct order#. I
don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you
could
do
the same, perhaps calling the function that assigns the order
number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once,
when
inserting a new header. The footer must then follow that
number
until
a new order is begin. Given your description of the
spreadsheet
data,
it isn't clear to me exactly how you can tell where one order
ends
and
another starts. Is that based on CustID / PO? That is, every
line
that is from the same customer and has the same PO is an
order.

I'm trying to get you a query based solution, founded on just
using
the existing function that assigns order numbers. Depending
on
your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each
order
that
comes
take the next number and resets it. I can see whats happening
in
the
EDI
code but I am at a loss since I know very very little about
VB
code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into
tblEDIHeader...
all
of
the other details will be added when it is processed it gets
matched
with
the main Customer Table. The same is true for Product I only
need
product
code and qty everything else gets dealt with when the EDI app
stuffs
into
our order entry system. It also validates customer and
product
numbers
with
an e-mail to summarize any problems. The spreadsheet was
extracted
from
a
database to start and I am confident that the data is clean
these
PO's
are
being transferred to us. I can see how the distinct will
look
after
creating a unique header for each order and how the footer
will
get
populated with all of the entries. The only issue is to get
the
order
number
issued for each header and them match it with the
corresponding
footer
records. The relationship will be based on the CustID + PO#
there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the
header
records
could
be sequentially numbered and this number added to the last
Used
CODAT1.
After I import all of these I could then reset CODAT1 to
update
for
the
next
number. I have had to do this on one or two occasions when
the
EDI
app
crashed and the number did not update. It willnot allow the
same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this
info
stuffed
in I
can save hours manually entering almost 10,000 lines of
orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns
from
which
you have a "Header" each with (potentially) a different
ProdCode
and
Qty. You should have only one "Header" per order, but
multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create
the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except
that
I
don't
see from where you're going to get the Order#. Also, I see
some
other
problems here:

- In a good database design, you would probably not have
Name.
There
would be a separate table giving Name based on CustID.
Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line for
each
order, there would be a mess whenever the CustID or PO were
different
for two different lines within the same order. A database
can
easily
constrain this with proper design, but your spreadsheet
won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the
spreadsheet
when
yo
u
link it into Access. The name you create there should be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered
in
more
than one way.

There are ways of detecting these problems, and even of
creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers
and
each
PO
has
8-10 products. I would like to stuff it into our EDI
tables
for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Maureen]

Tom... worked without a hitch - all done. To get the auotnumber to start a
setting of my choice I just appended a single record to the table and it
started from there when the heders were appended.

M

Dear Maureen:

If you make the Order Number an autonumber, you can just omit it when
you perform the append query. It will issue the numbering for you
automatically. The only trick is setting up the initial value. I
haven't done this in recent years, so I suggest you ask a question in
this newsgroup. I'm sure you'll get the information that way, if you
don't find it in online help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Can you please clarify how I can create the field with the incremental order
number in an append query. There will be a new number issued for each
CUSTID/PO whenever this combination changes the next number will be used.

M


Can you clarify how to create the

Dear Maureen:

I'm glad if you feel this creates a starting point for you.

Please come back if you have any specific questions in doing this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Yes, this will work, I am planning to prceed as follows starting in off
hours
1) Backup
2) E-mail to advise EDI users that we will be offline for maintenace
3) Take EDI offline
4) Stuff these into the EDI tables with thier own order numbers (start at
200000 is fine)
5) Process these orders into our live order entry we have an
'offline/manual' EDI order process function that processed orders into order
entry in the event of a lock-up
6) Review a sample of the porcessed orders
7) Bring EDI app back online and continue

This will take any interaction with the EDI app out of the picture

from
your

perspective. I appreciate greatly your help here and I am confident that
this will work. If I can get it done tonight, we're good if not, I

have
data

entery overtime scheduled as a backup plan... they will rekey this stuff.

M

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Sorry to say that qryOCONT is a table three fields and one entry

only
as

below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed

and
the

table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing

twine.
All

I
know is that it is stable and works great processing 1000's of

lines
of

orders every day. I have a copy of the code on my laptop as a VB project
and
as an exe file that runs. I know that id i an get these orders stuffed
into
the EDI tables from there all is well since I can run the 'process'
function
that stuffs them into our live app. I have tried this by entering
manually
the data and processing it.

M


Dear Maureen:

I haven't figured out yet how best to help you further at this point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that

is
a

query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

This code is from a Visual Basic project the is not yet been

made
into

an
exe file. The edi app is an exe program that sits running

waiting
for

the
communication part to say "GO" There is no "Public Function" that I
can
see,
There is 100's or even 1,000's of lines of code in the project. I
can't
be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it

is,
but

I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Mar 2004 21:49:18 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

The current order number is part of the VB order processing code
and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order
number"
then start with a select query that provides the CustID and PO,
adding
this column as a calculated function. In the Query Design Grid,
for
Field, put in "OrderNumber: = FunctionName()" where FunctionName
is
replaced by the name of the function that assigns an order
number.

I recommend using a scratch copy of the database while you
experiment
with this.

Do you get the ascending order numbers this way? Once you do,
change
the query to an append query, adding rows to your table of
orders.
Note that the 3 columns from this query must match the

columns
in

the
orders table you're building, and that they must do so in order.
Alternatively, you can do the work of associating the SELECT
query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 22 Mar 2004 20:54:33 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

Thanks for your reply. I have put off entering these just yet
since
we
will
not recieve the inventory to fill them until Friday... I have
until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an
order
whrn
they
change, a new order has started. I spent a few minutes looking
at
what
I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can

add
a

new
field
for each record that auto increments from a predefined start
(i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001 and
so
on)
from
the completed header records I could match trhe unique

CUSTID
+

PO
to
the
data and stuff in the footer records with the correct

order#.
I

don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you
could
do
the same, perhaps calling the function that assigns the order
number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once,
when
inserting a new header. The footer must then follow that
number
until
a new order is begin. Given your description of the
spreadsheet
data,
it isn't clear to me exactly how you can tell where one order
ends
and
another starts. Is that based on CustID / PO? That is, every
line
that is from the same customer and has the same PO is an
order.

I'm trying to get you a query based solution, founded on just
using
the existing function that assigns order numbers. Depending
on
your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each
order
that
comes
take the next number and resets it. I can see whats happening
in
the
EDI
code but I am at a loss since I know very very little about
VB
code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into
tblEDIHeader...
all
of
the other details will be added when it is processed it gets
matched
with
the main Customer Table. The same is true for Product I only
need
product
code and qty everything else gets dealt with when the

EDI
app

stuffs
into
our order entry system. It also validates customer and
product
numbers
with
an e-mail to summarize any problems. The spreadsheet was
extracted
from
a
database to start and I am confident that the data is clean
these
PO's
are
being transferred to us. I can see how the distinct will
look
after
creating a unique header for each order and how the footer
will
get
populated with all of the entries. The only issue is to get
the
order
number
issued for each header and them match it with the
corresponding
footer
records. The relationship will be based on the CustID + PO#
there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the
header
records
could
be sequentially numbered and this number added to the last
Used
CODAT1.
After I import all of these I could then reset CODAT1 to
update
for
the
next
number. I have had to do this on one or two occasions when
the
EDI
app
crashed and the number did not update. It willnot allow the
same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this
info
stuffed
in I
can save hours manually entering almost 10,000 lines of
orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of columns
from
which
you have a "Header" each with (potentially) a different
ProdCode
and
Qty. You should have only one "Header" per order, but
multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could create
the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except
that
I
don't
see from where you're going to get the Order#. Also,

I
see

some
other
problems here:

- In a good database design, you would probably not have
Name.
There
would be a separate table giving Name based on CustID.
Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one

line
for

each
order, there would be a mess whenever the CustID or

PO
were

different
for two different lines within the same order. A database
can
easily
constrain this with proper design, but your spreadsheet
won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the
spreadsheet
when
yo
u
link it into Access. The name you create there

should
be

substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is entered
in
more
than one way.

There are ways of detecting these problems, and even of
creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000 customers
and
each
PO
has
8-10 products. I would like to stuff it into our EDI
tables
for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M

 

[Tom Ellison]

Dear Maureen:

That's such good news. I was concerned whether we would get this done
to meet your schedule. I know there were significant details we
didn't cover, but you obviously handled them. Congratulations!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom... worked without a hitch - all done. To get the auotnumber to start a
setting of my choice I just appended a single record to the table and it
started from there when the heders were appended.

M

Dear Maureen:

If you make the Order Number an autonumber, you can just omit it when
you perform the append query. It will issue the numbering for you
automatically. The only trick is setting up the initial value. I
haven't done this in recent years, so I suggest you ask a question in
this newsgroup. I'm sure you'll get the information that way, if you
don't find it in online help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom,

Can you please clarify how I can create the field with the incremental order
number in an append query. There will be a new number issued for each
CUSTID/PO whenever this combination changes the next number will be used.

M


Can you clarify how to create the
Dear Maureen:

I'm glad if you feel this creates a starting point for you.

Please come back if you have any specific questions in doing this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Yes, this will work, I am planning to prceed as follows starting in off
hours
1) Backup
2) E-mail to advise EDI users that we will be offline for maintenace
3) Take EDI offline
4) Stuff these into the EDI tables with thier own order numbers (start at
200000 is fine)
5) Process these orders into our live order entry we have an
'offline/manual' EDI order process function that processed orders into
order
entry in the event of a lock-up
6) Review a sample of the porcessed orders
7) Bring EDI app back online and continue

This will take any interaction with the EDI app out of the picture from
your
perspective. I appreciate greatly your help here and I am confident that
this will work. If I can get it done tonight, we're good if not, I have
data
entery overtime scheduled as a backup plan... they will rekey this stuff.

M

Dear Maureen:

It's one thing for me to give you technical information about which
I'm confident. It's another for me to try to guess my way through the
inner workings of your existing EDI system, which could put your
existing business operations at risk. So, I'm going to go with a
suggestion for an alternative approach that avoids this risk.

Would it work out if we created another series of order numbers,
starting perhaps with 200000, and use those numbers for orders
generated through this alternative route for order generation?

What is bothering me is that the VB code you showed indicates that the
CODAT2 column in the ORDER-EDI-OL record is not always "Used" and
causes the system to differ in how it generates order numbers. I
don't want to screw with this. It's hard to see and predict what
might result.

Let's say we create another record in qryOCONT (that's a table -
wierd, the prefix to the name is like it's a query! One more scary
feature!) Put in our own key and write a function to return this
number and increment it each time. Then we can call that from the new
query work and number our invoices.

Alternatively, we can keep a table of our generated orders, using an
Autonumber for the order number. Set it to start from 200000 and then
just let it do it's thing. We can then append from that to the EDI
table. That really makes it easier.

Please respond whether any of these ideas appeals.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

wrote:

Sorry to say that qryOCONT is a table three fields and one entry only
as
below

COCKEY
CODAT1
CODAT2

ORDER-EDI-OL
81869
Used



Each order that comes in gets the next number as it is processed and
the
table is updated +1

The EDI app is VB code that was written 10 years ago and has been jury
rigged, held together with duct tape, paper clips and bailing twine.
All
I
know is that it is stable and works great processing 1000's of lines
of
orders every day. I have a copy of the code on my laptop as a VB
project
and
as an exe file that runs. I know that id i an get these orders stuffed
into
the EDI tables from there all is well since I can run the 'process'
function
that stuffs them into our live app. I have tried this by entering
manually
the data and processing it.

M


Dear Maureen:

I haven't figured out yet how best to help you further at this
point.
Much head scratching going on.

OK, maybe start out sending me the SQL of qryOCONT (assuming that is
a
query). How is 'ocont' dimensioned? As a Recordset, yes?

Hope this gets us somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 23 Mar 2004 16:52:49 -0500, "Maureen"
<[email protected]>
wrote:

This code is from a Visual Basic project the is not yet been made
into
an
exe file. The edi app is an exe program that sits running waiting
for
the
communication part to say "GO" There is no "Public Function" that I
can
see,
There is 100's or even 1,000's of lines of code in the project. I
can't
be
more help... it's rather greek to me.

M

Dear Maureen:

The code needs to be in a function. I'm thinking perhaps it is,
but
I'm not sure. You didn't show the context.

Is there a line of code saying Public Function somewhere just
above
what you quoted? Please quote everything from this point to End
Function. I'm hoping this is the situation.

So please quote more of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Mar 2004 21:49:18 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

The current order number is part of the VB order processing code
and
looks
like this:

'Open AS400/OCONT
frmMain.Caption = "Opening Control File..."
Set ocont = db4.OpenRecordset("qryOCONT")
ocont.MoveFirst

'pick up current order number
frmMain.Caption = "Getting Order Control Number..."
orderstr = Trim(ocont("CODAT1"))
ordernum = Val(orderstr)
'Use as Order number

inheader.Edit
inheader("INORDN") = Trim(Str(ordernum))
inheader.Update
'update AS400/OCONT
frmMain.Caption = "Resetting Order Control
Number..."
If ocont("CODAT2") = "Used" Then
ocont.Edit
ocont("CODAT1") = Trim(Str(ordernum + 1))
ocont.Update
Else
ocont.Edit
ocont("CODAT2") = "Used"
ocont.Update

Not at all sure how to get this to be 'called' from a query?
M

If there is a function provided that assigns the "next order
number"
then start with a select query that provides the CustID and
PO,
adding
this column as a calculated function. In the Query Design
Grid,
for
Field, put in "OrderNumber: = FunctionName()" where
FunctionName
is
replaced by the name of the function that assigns an order
number.

I recommend using a scratch copy of the database while you
experiment
with this.

Do you get the ascending order numbers this way? Once you do,
change
the query to an append query, adding rows to your table of
orders.
Note that the 3 columns from this query must match the columns
in
the
orders table you're building, and that they must do so in
order.
Alternatively, you can do the work of associating the SELECT
query
columns with the table columns.

Let me know how you get along with this, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 22 Mar 2004 20:54:33 -0500, "Maureen"
<[email protected]>
wrote:

Tom,

Thanks for your reply. I have put off entering these just yet
since
we
will
not recieve the inventory to fill them until Friday... I have
until
Thursday
to come up with a way to stuff them in!

You are correct the combination CUSTID + PO are unique to an
order
whrn
they
change, a new order has started. I spent a few minutes
looking
at
what
I
have and see that it is easy to group into unique CUSTID + PO
combinations
to get the header records I need. I can not see how i can add
a
new
field
for each record that auto increments from a predefined start
(i.e.
start
at
88,000 for header record 1 header record 2 would be 88,001
and
so
on)
from
the completed header records I could match trhe unique CUSTID
+
PO
to
the
data and stuff in the footer records with the correct order#.
I
don't
know
how really to do this but it seems logical to me.

M

Dear Maureen:

Given that the Order Numbers are being assigned in code you
could
do
the same, perhaps calling the function that assigns the
order
number
from your insert query. But this is only a guess. Without
examining
what it is doing, that's the best I can do from here.

However, this assignment function would only be used once,
when
inserting a new header. The footer must then follow that
number
until
a new order is begin. Given your description of the
spreadsheet
data,
it isn't clear to me exactly how you can tell where one
order
ends
and
another starts. Is that based on CustID / PO? That is,
every
line
that is from the same customer and has the same PO is an
order.

I'm trying to get you a query based solution, founded on
just
using
the existing function that assigns order numbers.
Depending
on
your
answers to the above, it just might be possible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Mar 2004 15:23:02 -0500, "Maureen"
<[email protected]>
wrote:

Tom the edi app has a table to issue order numbers. Each
order
that
comes
take the next number and resets it. I can see whats
happening
in
the
EDI
code but I am at a loss since I know very very little
about
VB
code. I
can
link to the table it is called qryOCONT (it is a table)
COCKEY CODAT1 CODAT2
ORDER-EDI-OL 81859 Used


I only need to get the CUSTID and PO to be put into
tblEDIHeader...
all
of
the other details will be added when it is processed it
gets
matched
with
the main Customer Table. The same is true for Product I
only
need
product
code and qty everything else gets dealt with when the EDI
app
stuffs
into
our order entry system. It also validates customer and
product
numbers
with
an e-mail to summarize any problems. The spreadsheet was
extracted
from
a
database to start and I am confident that the data is
clean
these
PO's
are
being transferred to us. I can see how the distinct will
look
after
creating a unique header for each order and how the footer
will
get
populated with all of the entries. The only issue is to
get
the
order
number
issued for each header and them match it with the
corresponding
footer
records. The relationship will be based on the CustID +
PO#
there
will
be
one OrderNumber (CODAT1) for each of these. perhaps the
header
records
could
be sequentially numbered and this number added to the last
Used
CODAT1.
After I import all of these I could then reset CODAT1 to
update
for
the
next
number. I have had to do this on one or two occasions when
the
EDI
app
crashed and the number did not update. It willnot allow
the
same
number
to
be used twice.

This all may be a little unorthodox, but if I can get this
info
stuffed
in I
can save hours manually entering almost 10,000 lines of
orders
Monday
AM!

M



Dear Maureen:

I think I can see roughly what you're after.

You probably have multiple instance of the set of
columns
from
which
you have a "Header" each with (potentially) a different
ProdCode
and
Qty. You should have only one "Header" per order, but
multiple
"lines" to each order. Is that the idea?

If you link these spreadsheets as tables, you could
create
the
Header
as follows:

SELECT DISTINCT XXX AS [Order#], CustID, Name, PO
FROM Spreadsheet

This is close to what is needed for your "header" except
that
I
don't
see from where you're going to get the Order#. Also, I
see
some
other
problems here:

- In a good database design, you would probably not have
Name.
There
would be a separate table giving Name based on CustID.
Doesn't
CustID
determine what Name should be?

- In the spreadsheet, where you have more than one line
for
each
order, there would be a mess whenever the CustID or PO
were
different
for two different lines within the same order. A
database
can
easily
constrain this with proper design, but your spreadsheet
won't
necessarily follow this rule in all cases.

For the Footer, you could use:

SELECT Order#, ProdCode, Qty
FROM Spreadsheet

In all the above, you will create a name for the
spreadsheet
when
yo
u
link it into Access. The name you create there should
be
substituted
in both queries above.

To create the customers table I recommended, use:

SELECT DISTINCT CustID, Name
FROM Spreadsheet

Again, there is a problem if any customer's name is
entered
in
more
than one way.

There are ways of detecting these problems, and even of
creating
solutions for them with minimal user intervention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sat, 20 Mar 2004 23:30:36 -0500, "Maureen"
<[email protected]>
wrote:

Not sure if I should ask this in "query" but...

I have an excel sheet with
| CustID | Name | PO | ProdCode | Qty |

The single excel sheet has orders for over 1,000
customers
and
each
PO
has
8-10 products. I would like to stuff it into our EDI
tables
for
autmatic
processing into OrderEntry.

I need to "separate" & "stuff" in to EDItables as
follows:

tbl.EDIHeader has | Order# | CUSTID | Name | PO |
tbl.EDIFooter has | Order# | ProdCode | Qty |

Any pointers or code samples?

M