Using Access with UPS Worldship

[Guest]

UPS Worldship can import shipment records from a single Access table that has
all fields: Name, Address, etc. as well as the package data: service, weight,
reference, etc. If there are to be multiple packages to the same
destination, Worldship needs a separate record for each. I would like to
start with a form/subform data access page that has the layout of a packing
slip: order number, name and address at the top and purchase line items with
quantities of boxes, etc. below. This must be a very common need. I asked
UPS support if they had this model. They knew right away what I wanted, but
hadn't done it. Has anyone done this. I want to develop an integrated
Access solution myself covering a number of areas, so a commercial product
like ozlink wouldn't do it. I would greatly appreciate assistance with this
and It may get wide usage.

 

[John Nurick]

This is a trivial task if your tables are correctly designed and
related. Just create a query that uses an outer join to return the
fields you need from both tables, with one record for each record in the
subform recordset.

On Tue, 10 May 2005 20:09:06 -0700, "Richard Sloane" <Richard

 

[Rich]

John,
Thank you for responding to my question. I see that I didn't ask the
difficult question I need help with. Imagine an order with ten boxes
of one line item and five boxes of a second line item. What I need is
to look at the field with the quantities and add ten records and then
five records to the resultant combined table for UPS. I think it would
use VBA code with "do loops". I don't know how to code VBA.
Thanks again.
Richard Sloane

 

[John Nurick]

Richard,

After reading this I am rather less confident that I understand the
situation. I really can't imagine what a box of a line item might be.

But from your first message it seemed that you were envisaging something
similar to the "Orders" form in the Northwind sample database, and its
"Orders Subform" subform.

The natural way to implement this in a relational database such as
Access would be with a table structure similar to that in Northwind
(Customers, Orders, [Order Details],etc.). If you do that, it is very
easy to create a query that joins the tables and returns the fields that
the UPS system needs. You would not need to use VBA for this.

 

[jlach]

Hi Rich,
Take a look at OZEXE Lite (http://www.ozdevelopment.com). Even if
you have no programming ability you should be able to create yourself a
solution to open an ODBC connection and select and create records.

Regards,
James