Re: Turning chaos into calm

In article <2008101205071616807-steve@hgcafecom>, Dwight Yoakam
<steve@xxxxxxxxxx> wrote:

On 2008-10-11 22:34:47 -0400, Helpful Harry
<helpful_harry@xxxxxxxxxxxxxxxx> said:

In article <2008100920281216807-steve@hgcafecom>, Dwight Yoakam
<steve@xxxxxxxxxx> 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)

Once again Harry, helpful as always. I do have a follow up question:
Customers Table
Customer ID
Customer Name
etc.

Shipping Locations Table
Customer ID
Shipping Address
etc.
The shipping address is a separate number than the customer number
(though not necessarily). So should the Shipping Locations Table
contain:
Customer ID
Customer Shipping ID
Shipping address

Thank you again, Steve

I don't think you need a Customer Shipping ID Field since you're
linking things by the Customer ID.

The Pop-up Menu / List Field in the Orders Table can display the
Shipping Locations Table's Shipping Address data itself. When the user
chooses one of the addresses, it will be stored with the order details.
If the customer later changes their shipping addresses, the addresses
stored with the orders will not change - it gives you a historial
database.


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

Relevant Pages

  • Re: Powersellers get away with MURDER!
    ... > Bizrate isn't one of your better rating services. ... For example, when I look at Buy.com's complaints, most of them seem ... to be about slow shipping or Buy.com's use of USPS--which is often the same ... customer, and has the same effect on the overall rating. ...
    (alt.marketing.online.ebay)
  • Re: crappy seller, deliberate deception, misunderstanding? You tell me - Jeff McAfee AKA Classic
    ... I got an email from someone who bought a stencil set. ... customer ruined while applying in the wind. ... Can you not read between the lines that Erik is a PAIN ... I sell on ebay and when I screw up I pay for the shipping, ...
    (rec.games.pinball)
  • Re: Turning chaos into calm
    ... On 2008-10-11 22:34:47 -0400, Helpful Harry ... This is the first issue I would like to deal with: Customer Information. ... Do I make a separate table occurence of the ... Customers Table -> Shipping Locations Table ...
    (comp.databases.filemaker)
  • Re: Subtotals in reports
    ... If a customer makes 3 purchases that are shipped, ... >in the OrderDetails contains the shipping charge. ... >I have tried severals ways to group the report, ... >get is to have a footer on the purchase date, ...
    (microsoft.public.access.reports)
  • Subtotals in reports
    ... If a customer makes 3 purchases that are shipped, ... in the OrderDetails contains the shipping charge. ... cost, and the shipping. ... FROM Products INNER JOIN (((Customers INNER JOIN Orders ON ...
    (microsoft.public.access.reports)