Create a linked table
in [General]
|
Prev: IIf return multiple categories
Next: Lost of CD
From: Lyndy on 31 Mar 2010 19:59 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? -- Lyndy
From: Steve on 31 Mar 2010 20:31 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 santus(a)penn.com "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message news:7734D615-E27A-460F-AADF-AC10251BB28E(a)microsoft.com... >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? > -- > Lyndy
From: Lyndy on 31 Mar 2010 22:03 Hi Steve, Some of the customers do have more than 1 account no. Do I need to use a look up column? -- Lyndy "Steve" wrote: > 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 > santus(a)penn.com > > > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > news:7734D615-E27A-460F-AADF-AC10251BB28E(a)microsoft.com... > >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? > > -- > > Lyndy > > > . >
From: Gina Whipp on 31 Mar 2010 22:28 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 "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message news:ADEBB50D-8C99-42DE-B0F9-4FDC34EEF3F4(a)microsoft.com... Hi Steve, Some of the customers do have more than 1 account no. Do I need to use a look up column? -- Lyndy "Steve" wrote: > 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 > santus(a)penn.com > > > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > news:7734D615-E27A-460F-AADF-AC10251BB28E(a)microsoft.com... > >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? > > -- > > Lyndy > > > . >
From: Lyndy on 31 Mar 2010 23:56
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 "Gina Whipp" wrote: > 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 > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > news:ADEBB50D-8C99-42DE-B0F9-4FDC34EEF3F4(a)microsoft.com... > Hi Steve, > Some of the customers do have more than 1 account no. Do I need to use a > look up column? > > -- > Lyndy > > > "Steve" wrote: > > > 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 > > santus(a)penn.com > > > > > > > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > > news:7734D615-E27A-460F-AADF-AC10251BB28E(a)microsoft.com... > > >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? > > > -- > > > Lyndy > > > > > > . > > |