If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relational Database Layout
What I would like to do is create a database to track sales, customer info
and customer demographics using 3 separate tables: Customer Info(name, address, phone, etc.), Sale info(what was purchased, date, size, etc), and Demographics(Gender, age, income, etc). I have the three tables populated with the needed fields but I'm having a problem creating the relationship setup so that I can say look up a customer or product and see what product that customer purchased or select a product and see which customers have purchased it. This is a beginner question, which I am, but would like a little help. |
#3
|
|||
|
|||
Relational Database Layout
Steve,
This looks like a very well laid out set of tables. I was having problems getting the relationships set up for three tables, this will surely drive me crazy. Can you take me one more step? I've been watching a tutorial but it goes over this pretty quickly and with a small set of tables. Rob "Steve" wrote: Rob, How about these tables ........ TblAgeGroup AgegroupID AgeGroup TblIncomeGroup IncomeGroupID IncomeGroup TblCustomer CustomerID name, address, phone, etc Gender M/F AgeGroupID IncomeGroupID TblProduct ProductID Product TblCustomerPurchase CustomerPurchaseID CustomerID CystomerPurchaseDate TblCustomerPurchaseDetail CustomerPurchaseDetailID CustomerPurchaseID ProductID Size Steve "Rob H" wrote in message ... What I would like to do is create a database to track sales, customer info and customer demographics using 3 separate tables: Customer Info(name, address, phone, etc.), Sale info(what was purchased, date, size, etc), and Demographics(Gender, age, income, etc). I have the three tables populated with the needed fields but I'm having a problem creating the relationship setup so that I can say look up a customer or product and see what product that customer purchased or select a product and see which customers have purchased it. This is a beginner question, which I am, but would like a little help. . |
#4
|
|||
|
|||
Relational Database Layout
Careful, steve is our own personal troll and will offer to do this at a
reasonable fee. He provides questionable results at unreasonable prices. What he has provided so far, is a basic outline with the hope that you will take the bait and ask for his personal help. The nature of these newsgroups are to offer FREE peer to peer help and there who are far more quilified than steve who will gladly help for free. John... Visio MVP "Rob H" wrote in message ... Steve, This looks like a very well laid out set of tables. I was having problems getting the relationships set up for three tables, this will surely drive me crazy. Can you take me one more step? I've been watching a tutorial but it goes over this pretty quickly and with a small set of tables. Rob "Steve" wrote: Rob, How about these tables ........ TblAgeGroup AgegroupID AgeGroup TblIncomeGroup IncomeGroupID IncomeGroup TblCustomer CustomerID name, address, phone, etc Gender M/F AgeGroupID IncomeGroupID TblProduct ProductID Product TblCustomerPurchase CustomerPurchaseID CustomerID CystomerPurchaseDate TblCustomerPurchaseDetail CustomerPurchaseDetailID CustomerPurchaseID ProductID Size Steve "Rob H" wrote in message ... What I would like to do is create a database to track sales, customer info and customer demographics using 3 separate tables: Customer Info(name, address, phone, etc.), Sale info(what was purchased, date, size, etc), and Demographics(Gender, age, income, etc). I have the three tables populated with the needed fields but I'm having a problem creating the relationship setup so that I can say look up a customer or product and see what product that customer purchased or select a product and see which customers have purchased it. This is a beginner question, which I am, but would like a little help. . |
#5
|
|||
|
|||
Relational Database Layout
Hi Rob,
Primary and foreign key fields all end in "ID". Primary keys should be autonumber and foreign keys should be Number - Long Integer. The primary key of each table is the first field under the table name. All other "ID" fields in each table are foreign keys and have the same name as their related primary key. So to set up the relationships connect all foreign keys to their appropriate primary key. Let me know if you don't understand. Steve "Rob H" wrote in message ... Steve, This looks like a very well laid out set of tables. I was having problems getting the relationships set up for three tables, this will surely drive me crazy. Can you take me one more step? I've been watching a tutorial but it goes over this pretty quickly and with a small set of tables. Rob "Steve" wrote: Rob, How about these tables ........ TblAgeGroup AgegroupID AgeGroup TblIncomeGroup IncomeGroupID IncomeGroup TblCustomer CustomerID name, address, phone, etc Gender M/F AgeGroupID IncomeGroupID TblProduct ProductID Product TblCustomerPurchase CustomerPurchaseID CustomerID CystomerPurchaseDate TblCustomerPurchaseDetail CustomerPurchaseDetailID CustomerPurchaseID ProductID Size Steve "Rob H" wrote in message ... What I would like to do is create a database to track sales, customer info and customer demographics using 3 separate tables: Customer Info(name, address, phone, etc.), Sale info(what was purchased, date, size, etc), and Demographics(Gender, age, income, etc). I have the three tables populated with the needed fields but I'm having a problem creating the relationship setup so that I can say look up a customer or product and see what product that customer purchased or select a product and see which customers have purchased it. This is a beginner question, which I am, but would like a little help. . |
#6
|
|||
|
|||
Relational Database Layout
"Steve" wrote in message
... Primary and foreign key fields all end in "ID". Primary keys should be autonumber and foreign keys should be Number - Long Integer. The primary key of each table is the first field under the table name. All other "ID" fields in each table are foreign keys and have the same name as their related primary key. So to set up the relationships connect all foreign keys to their appropriate primary key. Let me know if you don't understand. Steve subtle stevie. Let "me" now... These newsgroups are not your private domain for hunting victims. There are far more qualified people hear who will help John... Visio MVP |
#7
|
|||
|
|||
Relational Database Layout
Thanks for the heads up John
"John... Visio MVP" wrote: Careful, steve is our own personal troll and will offer to do this at a reasonable fee. He provides questionable results at unreasonable prices. What he has provided so far, is a basic outline with the hope that you will take the bait and ask for his personal help. The nature of these newsgroups are to offer FREE peer to peer help and there who are far more quilified than steve who will gladly help for free. John... Visio MVP "Rob H" wrote in message ... Steve, This looks like a very well laid out set of tables. I was having problems getting the relationships set up for three tables, this will surely drive me crazy. Can you take me one more step? I've been watching a tutorial but it goes over this pretty quickly and with a small set of tables. Rob "Steve" wrote: Rob, How about these tables ........ TblAgeGroup AgegroupID AgeGroup TblIncomeGroup IncomeGroupID IncomeGroup TblCustomer CustomerID name, address, phone, etc Gender M/F AgeGroupID IncomeGroupID TblProduct ProductID Product TblCustomerPurchase CustomerPurchaseID CustomerID CystomerPurchaseDate TblCustomerPurchaseDetail CustomerPurchaseDetailID CustomerPurchaseID ProductID Size Steve "Rob H" wrote in message ... What I would like to do is create a database to track sales, customer info and customer demographics using 3 separate tables: Customer Info(name, address, phone, etc.), Sale info(what was purchased, date, size, etc), and Demographics(Gender, age, income, etc). I have the three tables populated with the needed fields but I'm having a problem creating the relationship setup so that I can say look up a customer or product and see what product that customer purchased or select a product and see which customers have purchased it. This is a beginner question, which I am, but would like a little help. . . |
#8
|
|||
|
|||
Relational Database Layout
Hello, I may be brand new to access myself but the information you posted
about foreign keys and their properties is just plain wrong. "Steve" wrote: Hi Rob, Primary and foreign key fields all end in "ID". Primary keys should be autonumber and foreign keys should be Number - Long Integer. The primary key of each table is the first field under the table name. All other "ID" fields in each table are foreign keys and have the same name as their related primary key. So to set up the relationships connect all foreign keys to their appropriate primary key. Let me know if you don't understand. Steve "Rob H" wrote in message ... Steve, This looks like a very well laid out set of tables. I was having problems getting the relationships set up for three tables, this will surely drive me crazy. Can you take me one more step? I've been watching a tutorial but it goes over this pretty quickly and with a small set of tables. Rob "Steve" wrote: Rob, How about these tables ........ TblAgeGroup AgegroupID AgeGroup TblIncomeGroup IncomeGroupID IncomeGroup TblCustomer CustomerID name, address, phone, etc Gender M/F AgeGroupID IncomeGroupID TblProduct ProductID Product TblCustomerPurchase CustomerPurchaseID CustomerID CystomerPurchaseDate TblCustomerPurchaseDetail CustomerPurchaseDetailID CustomerPurchaseID ProductID Size Steve "Rob H" wrote in message ... What I would like to do is create a database to track sales, customer info and customer demographics using 3 separate tables: Customer Info(name, address, phone, etc.), Sale info(what was purchased, date, size, etc), and Demographics(Gender, age, income, etc). I have the three tables populated with the needed fields but I'm having a problem creating the relationship setup so that I can say look up a customer or product and see what product that customer purchased or select a product and see which customers have purchased it. This is a beginner question, which I am, but would like a little help. . . |
Thread Tools | |
Display Modes | |
|
|