A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relational Database Layout



 
 
Thread Tools Display Modes
  #1  
Old March 7th, 2010, 06:20 PM posted to microsoft.public.access.tablesdbdesign
Rob H
external usenet poster
 
Posts: 35
Default 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.
  #2  
Old March 7th, 2010, 07:01 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Relational Database Layout

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.



  #3  
Old March 8th, 2010, 06:37 PM posted to microsoft.public.access.tablesdbdesign
Rob H
external usenet poster
 
Posts: 35
Default 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  
Old March 8th, 2010, 07:00 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default 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  
Old March 8th, 2010, 07:08 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old March 8th, 2010, 07:35 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default 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  
Old March 8th, 2010, 08:26 PM posted to microsoft.public.access.tablesdbdesign
Rob H
external usenet poster
 
Posts: 35
Default 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  
Old March 9th, 2010, 06:04 PM posted to microsoft.public.access.tablesdbdesign
accessnewbie79915
external usenet poster
 
Posts: 4
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.