Create a linked table
in [General]
|
Prev: IIf return multiple categories
Next: Lost of CD
From: Gina Whipp on 1 Apr 2010 00:31 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 "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message news:E7ABF6BA-8BD6-44D2-AF44-59106952ACBB(a)microsoft.com... 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 > > > > > > . > >
From: Lyndy on 1 Apr 2010 01:20 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 "Gina Whipp" wrote: > 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 > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > news:E7ABF6BA-8BD6-44D2-AF44-59106952ACBB(a)microsoft.com... > 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 > > > > > > > > > . > > >
From: Lyndy on 1 Apr 2010 01:26 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 -- Lyndy "Lyndy" wrote: > 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 > > > "Gina Whipp" wrote: > > > 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 > > > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > > news:E7ABF6BA-8BD6-44D2-AF44-59106952ACBB(a)microsoft.com... > > 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 > > > > > > > > > > > > . > > > >
From: Gina Whipp on 1 Apr 2010 11:16 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 "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message news:F253EE8D-9942-46DD-B978-C2701AB2A223(a)microsoft.com... 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 -- Lyndy "Lyndy" wrote: > 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 > > > "Gina Whipp" wrote: > > > 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 > > > > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message > > news:E7ABF6BA-8BD6-44D2-AF44-59106952ACBB(a)microsoft.com... > > 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 > > > > > > > > > > > > . > > > >
From: Steve on 1 Apr 2010 11:30
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 santus(a)penn.com "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message news:F253EE8D-9942-46DD-B978-C2701AB2A223(a)microsoft.com... > 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 > > -- > Lyndy > > > "Lyndy" wrote: > >> 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 >> >> >> "Gina Whipp" wrote: >> >> > 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 >> > >> > "Lyndy" <Lyndy(a)discussions.microsoft.com> wrote in message >> > news:E7ABF6BA-8BD6-44D2-AF44-59106952ACBB(a)microsoft.com... >> > 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 >> > > > >> > > > >> > > > . >> > > > |