Relationship/Pop up Menu issues

[Database Administrator]

Hello all,
Harry had helped me on this before, but it has gotten more complicated
and I have lost focus:
I will preface by saying my goal for this project is to give the inside
sales reps better tools to work with so we become more efficient (ie,
for now, I would keep this as a seperate tool to be used without going
through our already in place system).
I work for a company that has customers that purchase steel for certain
parts. For each part, there is supposed to be a form called an AQP
filled out that has the customers exact specifications. Once this is
entered into our very clunky system, it is assigned what is called an
AQP ID (the AQP ID is the customers Ship To Number + the number of
ordersthey have in our system (If they had 3 parts in the system and
ordered a fourth, then their new AQP ID for this part becomes
SXYZQ0004). In this form there are some very crucial elements that
could make our day easier using look-ups to fill the majority of the
information. On this form there are several "Key Fields" that are used:

Customer ID (assigned by my company (eg, 005247))
Customer Ship To ID (assigned by my company (eg, S652470))
AQP ID (assigned by my company (eg, S6524700007))
Customer Part Number (not assigned by my company (not a key field
because two companies may have the same part number for two different
items but an identifier none the less))

From this AQP form, we get all of the customer's specs for that part.
If they order that part, we have to fill out an inquiry form that
requires us to hand write a lot information that lead to many erroneous
orders being placed (things like width, width tolerance, processes the
steel needs to go through...etc). These would be considered "Line
Items." In the line items I need:
Pieces
Qty
Customer Part Number
gauge
gauge tolerances
Width
Width Tolerances
Length
Length Tolerances
Processes
Grade of steel
Notes
Customer Application
All of these items are already specified in the AQP form and I can, and
have gotten, an excel spreadsheet with all of the AQP information
including the Key Fields. The way I figure it, I need several tables:

Customer Info
Cust ID
Address
Phone
Ship To Info (Harry disagrees on this but I am still not sure why)
Ship To ID
Ship Address
Ship Phone
AQP Info
Ship To ID
Part Number
Part Specifications
Inquiry
Inquiry Id
Cust Name
Cust ID
Ship to ID
Customer Part Number
Pieces
Qty
Customer Part Number
gauge
gauge tolerances
Width
Width Tolerances
Length
Length Tolerances
Processes
Grade of steel
Notes
Customer Application
Other various look up items based on AQP or Cust ID
Line Items
Pieces
Qty
Customer Part Number
gauge
gauge tolerances
Width
Width Tolerances
Length
Length Tolerances
Processes
Grade of steel
Price

Here is ideally what I would like to make happen:
1) On the AQP, I would like to be able to do a look up using a pop up
menu that would contain customer name/customer number. From this I
would like automatically looked up things we have done in the past for
packaging, how much can their forklift lift...etc (all stuff already on
prior AQPs).

2) Pop up menu on the Inquiry form based on Customer Name/Customer ID
that would:
a) lookup customer's Outside Sales Rep and inside sales rep
b) show in a separate field the customers and only the customers Ship
To Id/Customer Address so I can pick the correct location, that would
in turn look up customer's City, State, Zip.

3) On the Inquiries, I would like to fill the line items based on a pop
up menu with the Customer Part Number/Customer ID (or Customer Name)
(only showing the customers part numbers). This would then
automatically fill the line items with the appropriate info for that
part.

I have been trying to get this to work out right for at least 10hrs but
have had limited success. If you have any advice on extra table
occurances, more tables, anything would be great. I keep thinking
because all of the Key Fields are already given, it shoud be a
breeze...but all I feel is winded. Thank you,
Steve


--

[Database Administrator]

"Dwight Yoakam" wrote in message
news:2008110222285816807-steve@hgcafecom...
> Hello all,
> Harry had helped me on this before, but it has gotten more complicated
> and I have lost focus:
> I will preface by saying my goal for this project is to give the inside
> sales reps better tools to work with so we become more efficient (ie,
> for now, I would keep this as a seperate tool to be used without going
> through our already in place system).
> I work for a company that has customers that purchase steel for certain
> parts. For each part, there is supposed to be a form called an AQP
> filled out that has the customers exact specifications. Once this is
> entered into our very clunky system, it is assigned what is called an
> AQP ID (the AQP ID is the customers Ship To Number + the number of
> ordersthey have in our system (If they had 3 parts in the system and
> ordered a fourth, then their new AQP ID for this part becomes
> SXYZQ0004). In this form there are some very crucial elements that
> could make our day easier using look-ups to fill the majority of the
> information. On this form there are several "Key Fields" that are used:


I am trying to look at this one, but it's difficult being stuck on this old
machine with no printer and no way to transfer files back to my Mac. I
really need to be able to print out your long message so that I can read
properly, but it does look like you might have some over-complication in
there (although I'm not 100% sure what are Tables and what are Fields in
that list).

Helpful Harry
One week and one day ... and still no fixed Internet on my Mac. xo(