Relational Database Layout
in [Tables]
|
From: Rob H on 7 Mar 2010 12:20 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.
From: Steve on 7 Mar 2010 13:01 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 santus(a)penn.com "Rob H" <RobH(a)discussions.microsoft.com> wrote in message news:299BDB0B-38C4-48C7-918F-E69A28EFE09A(a)microsoft.com... > 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.
From: Rob H on 8 Mar 2010 12:37 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 > santus(a)penn.com > > > > "Rob H" <RobH(a)discussions.microsoft.com> wrote in message > news:299BDB0B-38C4-48C7-918F-E69A28EFE09A(a)microsoft.com... > > 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. > > > . >
From: John... Visio MVP on 8 Mar 2010 13:00 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" <RobH(a)discussions.microsoft.com> wrote in message news:F23AC003-3EB6-4834-AE87-F0108C23FC83(a)microsoft.com... > 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 >> santus(a)penn.com >> >> >> >> "Rob H" <RobH(a)discussions.microsoft.com> wrote in message >> news:299BDB0B-38C4-48C7-918F-E69A28EFE09A(a)microsoft.com... >> > 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. >> >> >> . >>
From: Steve on 8 Mar 2010 13:08 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" <RobH(a)discussions.microsoft.com> wrote in message news:F23AC003-3EB6-4834-AE87-F0108C23FC83(a)microsoft.com... > 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 >> santus(a)penn.com >> >> >> >> "Rob H" <RobH(a)discussions.microsoft.com> wrote in message >> news:299BDB0B-38C4-48C7-918F-E69A28EFE09A(a)microsoft.com... >> > 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. >> >> >> . >>
|
Next
|
Last
Pages: 1 2 Prev: Superclass have only a autonumber.. is it possible? Next: Food Pantry Database |