Create a linked table

[Lyndy]

I need to create a 2nd table that will link to my cust table. The 2nd table
should contain info on freight companys and the cust account numbers to be
used for shipping. How do I format the 2nd table?

 

[Steve]

Hello Lyndy,

I think what you want are the following tables:


TblCustomer
CustomerID
CustomerAccountNumber
<customer fields>

TblFreightCompany
FreightCompanyID
<freight company fields>

TblFreightCompanyCustomerShipping
FreightCompanyCustomerShippingID
FreightCompanyID
CustomerID

In TblFreightCompanyCustomerShipping for each freight company you build a
list of what customers the freight company ships to.

In any forms or reports where you need to show customer account numbers a
freight company ships to, use a query that includes the three above tables.

NOTE - the above tables assume a customer only has one customer account
number. If a customer has more than one customer account number, the design
of the tables is more complex.

Steve
(e-mail address removed)

 

[Lyndy]

Hi Steve,
Some of the customers do have more than 1 account no. Do I need to use a
look up column?

 

[Gina Whipp]

Lyndy,

Let me tweak those tables for you...

tblCustomer
cCustomerID - PK, Autonumber
cAccountID - FK Long
<customer fields>

tblAccountNumbers
anAccountID - PK, Text and only if you don't want duplications. So as long
as no Account ID's are shared between Customers.
anCustomerID - FK, Long

tblFreightCompany
fcFreightCompanyID - PK, Autonumber
<freight company fields>

TblFreightCompanyCustomerShipping
fccsFreightCompanyCustomerShippingID - PK, Autonumber
fccsFreightCompanyID - FK, Long
fccsCustomerID - FK, Long

However, you didn't mention is the Account ID tied to the Customer or the
Freight Company, if the Freight Company the tables ned to be altered.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Steve,
Some of the customers do have more than 1 account no. Do I need to use a
look up column?

 

[Lyndy]

Hi Gina,
I have created a table for Freight, Customers and a 3rd table that contains
CustomerID - Text, FreightCoID - Text and FreightCustmShipID - Autonumber.
This table also has a column for Account nos.
The account numbers refer to Customers who wish to use their own Freight Co
and have their own acc no. However the problem arises as to customers who
have more than 1 preferred freight company, hence the more than one account
no column. Will my 3rd table still work?

 

[Gina Whipp]

Lyndy,

Okay... still nt clear to me so...

Customer - shipping Freight - is the Account Number attached to the Customer
or the Customers Freight Company?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina,
I have created a table for Freight, Customers and a 3rd table that contains
CustomerID - Text, FreightCoID - Text and FreightCustmShipID - Autonumber.
This table also has a column for Account nos.
The account numbers refer to Customers who wish to use their own Freight Co
and have their own acc no. However the problem arises as to customers who
have more than 1 preferred freight company, hence the more than one account
no column. Will my 3rd table still work?

 

[Lyndy]

Hi Gina,
The account no refers to the customers freight account! Some customers use
more than one freight company, hence two or more account nos.

 

[Lyndy]

Hi Gina,
I have run a simple query using my 3 tables and I have been able to access
all the necessary info. Many thanks for both your and steve's help. I think I
will be able to base all my reports on this too. If you have any other
suggestions i am open to all.
Once again many thanks

 

[Gina Whipp]

Lyndy,

Glad you got it working but I would move Account number as it is not related
to the Customer but the Customers Freight Company and therefore that should
have it's own table. However, that said, if you feel comfortable the way
you have it and it works then go for it.

Good Luck!
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina,
I have run a simple query using my 3 tables and I have been able to access
all the necessary info. Many thanks for both your and steve's help. I think
I
will be able to base all my reports on this too. If you have any other
suggestions i am open to all.
Once again many thanks

 

[Steve]

Lyndy,

I don't think your tables are corret yet. What do you think of these .......
TblCustomer
CustomerID
CustomerAccountNumber
<customer fields>

TblFreightCompany
FreightCompanyID
<freight company fields>

TblFreightCompanyCustomerShipping
FreightCompanyCustomerShippingID
FreightCompanyID
CustomerID
CustomerFreightAccountNumber

In TblFreightCompanyCustomerShipping you can record a customer who only uses
one freight company and customers who have more than 1 preferred freight
company.

Note also that FreightCompanyID and CustomerID in
TblFreightCompanyCustomerShipping are both Number - Long Integer data type.


Steve
(e-mail address removed)

 

[Risse]

I need to create a 2nd table that will link to my cust table. The 2nd table
should contain info on freight companys and the cust account numbers to be
used for shipping. How do I format the 2nd table?