adding multiple shipping address to a customer ID

[joecamel9166]

I'm just looking for some creative input and best suggestions.

Right now I have a customer table which had bill to info, quote to info, and ship to info.

The problem I am seeing is a lot of customers we deal with are huge companies with multiple locations. So one customer may have 10 different shipping addresses to their one invoice address.

tables I have are:

tblCustID:
CustNumber (PK)
nameInv
AddInv
CityInv
NameQuote
AddQuote
CityQuote
Etc


tblCustShip
ID (PK)
CustNumber (FK)
CustNameShip
CustAddShip
Etc

I changed the field source in my form for those ship to fields. But when I open my form it prompts me to input all the ship field parameters.



Maybe my relationships are wrong?
I attached a copy of my DB if you want to take a look.
Any suggestions would be extremely helpful.

Repair1.zip

[orange]

Some of the issues may be related to your set up. What exactly is a Customer? That is, is it a "building" at a physical location, or is it a corporate entity that has multiple "affiliates" and each may have multiple 'buildings" at various locations.

Also, because of the importance of addresses, you may want to consider a construct with a table of Addresses and a related table of AddressTypes.
AddressTypes could include:
-physical location
-mailing general
-finance and accounting
-shipping
-other

[joecamel9166]

Sorry, but I'm not sure I really understand your question.
The customer sends a unit in to us for repair.
That customer may be an end user, or a big company, or even a distributer.
So lets say that "distributer1 Branch #21" sends the unit in. We invoice and quote to "distributer1 Branch #21"
but it needs to be shipped back to "Family Dentistry" (who is the end user)

As it is now this is all possible, But anytime I change the shipping address for "Distributer1 Branch #21"
all records that are associated with "Distributer1 Branch#21" will change to that new shipping address.

So what I am doing now is playing with different possibilities on how to do this.
I didn't realize this problem until I was much further along in the development of the DB, so its a lot to deal with.

[orange]

Your description helps put your post into context. So your company "repairs" items. You have processes to deal with "item repairs " such as review some description, provide a quote, accept physical item, repair the item, return/ship "repaired item" to the "end user".
Which seems to say that a Customer may or may not be the end user.

The problem you are dealing with often arises with Products and Sales. Bear with me on this.
Customer makes a purchase that involves several items. In many databases and models you see a set up with

Customer -->Order-->OrderItem<---Item

where the Item table has ItemId, ItemDesc and ItemPrice (possibly more fields)
in the OrderItem is the OrderID (FK), ItemID, QuantitySold
and 'they use the Item Price from the Item table to determine the Price'.
This looks good until `someone does a report or research on previous Orders.'

ISSUE!! All the existing orders show this latest Item Price. And Order Totals (prices) don't match reality.

1 solution, on the OrderItem record for a purchase include the Quantity and the AgreedToPrice.

Here is my rationalization for AgreedToPrice:

AgreedUponPrice is the negotiated Price that you sold this product to this Customer in this Order and that price could include sale price/clearance item/loyalty reward program/special discount.And it won't be affected if you change the current Product price in the product table.

The bottom line is if you want to change the Price of an Item you have to retain the selling price on the record of the sale.

In your case, it isn't necessarily the Customer's address you will be shipping the repaired item to. And the next Item for repair from that Customer (distributer...) may involve a different end user and shipping address. So, in the record that deals with the repair, you should identify to whom this repaired item will be shipped to ultimately.

It seems to me that that would be an attribute of the Unit, but you know you set up better than any reader.

Hope it helps.
Good luck.

[joecamel9166]

Ok, so heres what I did. For this aspect of the DB, I have 3 tables. ( I took out the Quote to info since its really not needed)
Units:
UnitID - PK
CustomerID - FK
ShipToID - FK
etc.

Customers
CustomerID - PK
Address
city
state
etc

ShipTO
ShipID - PK
CustomerID - FK
address
etc


on my main form I have 2 subforms. One for Invoice to (customer ID) and one for ship to (ship ID)

When I select my customer from a combo box (control source is CustomerID on Units table), the invoice to fields in the subform fills in correctly.
Now what I want to do is make another combo box for the Ship to, to be filtered by the customer ID that is selected.

So if I select Distributer1, their billing info will display on subInvoice. Now on the combo box that is tied to the ShipToID on Units table, I want that to display all address I have for Distributer1 and have the subform fill in accordingly, or have a selection for new shipping address.

Does this make sense to do it this way?
How do I filter the ShipTo based on the Invoice to?

[orange]

I have attached a model that may represent what you are doing. It always helps to get your tables and relationships set up so that you can test with some sample data. Hopefully this meets your requirement and business need to some degree. You can set up some test data and test it, and adjust your structure as required.

As I mentioned previously, I think the ShipTo info has to be stored with the Unit. That is do you ship the repaired unit to the Customer/Distributor or End User.

Good luck with your project.

[joecamel9166]

Well seeing it definitely puts what you are saying into perspective. It makes a lot more sense now.
Thanks!