Relational Database Layout

[Rob H]

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.

 

[Steve]

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
(e-mail address removed)

 

[Rob H]

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

 

[John... Visio MVP]

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

 

[Steve]

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

 

[John... Visio MVP]

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

 

[Rob H]

Thanks for the heads up John

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




.

 

[accessnewbie79915]

Hello, I may be brand new to access myself but the information you posted
about foreign keys and their properties is just plain wrong.