Turning chaos into calm

Good evening all,
I am with a company in chaos and I am trying to make some sense of it
one chunk at a time. As emplyees we are required to fill out "Inquiry
forms" for each order...by hand. There are boat loads of room for error
in this process (as I have proven many times in my short tenure):
This is the first issue I would like to deal with: Customer Information.

The company assigns the customer a customer number and a shipping
number (the shipping number is not necessarily different from the
customer number if they are both the same address). Also, the customer
may have more than one shipping location. What I have a drop down box
that pulls up the customer # by typing in the first few letters of the
customers name. That triggers a look up of their: inside rep, outside
rep, cust name and cust number. From this I would like to have a field
with a drop down box of the specific customer's ship to numbers. How
can I accomplish this? Do I make a separate table occurence of the
customer ship to info? Thank you very much.
--

In article <2008100920281216807-steve@hgcafecom>, Dwight Yoakam
<steve.TakeThisOut@hgcafe.com> wrote:

> Good evening all,
> I am with a company in chaos and I am trying to make some sense of it
> one chunk at a time. As emplyees we are required to fill out "Inquiry
> forms" for each order...by hand. There are boat loads of room for error
> in this process (as I have proven many times in my short tenure):
> This is the first issue I would like to deal with: Customer Information.
>
> The company assigns the customer a customer number and a shipping
> number (the shipping number is not necessarily different from the
> customer number if they are both the same address). Also, the customer
> may have more than one shipping location. What I have a drop down box
> that pulls up the customer # by typing in the first few letters of the
> customers name. That triggers a look up of their: inside rep, outside
> rep, cust name and cust number. From this I would like to have a field
> with a drop down box of the specific customer's ship to numbers. How
> can I accomplish this? Do I make a separate table occurence of the
> customer ship to info? Thank you very much.

Without knowing the structure of your database it's difficult to give
an exact answer, but the options for the Pop-up List / Menu do need to
be in a separate Table.

The normal set-up for such a system would be (at least) something like:

Customers Table
Customer ID
Customer Name
etc.

Shipping Locations Table
Customer ID
Shipping Address
etc.

Orders Table
Customer ID
Order Number
Order Date
etc.

with Relationships:

Customers Table -> Shipping Locations Table
based on Customer ID

Orders Table -> Customers Table
based on Customer ID

To get a Pop-up List / Menu Field in the Orders Table Layout to show
just the Shipping Locations for the entered Customer ID, you need to
set-up another Relationship:

Orders Table -> Shipping Locations Table
based on Customer ID

Then you can define a Value List that gets its values from the
appropriate Shipping Locations Table Field via this Relationship.
Finally format the appropriate Orders Table Field to use this new Value
List.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)