New To Access

[Guest]

I am trying to create an Access Database from scratch and have never used
Access before. I am using Access 2003.
Could someone please help me in very simple terms to explain to me how the
best way is to view what I want to see please.

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)
I have Property Table, which shows the Property Address they are buying (ie
Peter, Raymond & Will are buying 60 Brighton Road)
I have Mortgage Details Table, which shows the Mortgage Lender and relevant
details (ie Peter, Raymond & Will are buying 60 Brighton Road with The
Halifax for £100,000)
I have Tracking Details Table, which shows all status's along the way (ie
Peter, Raymond & Will are buying 60 Brighton Road with The Halifax for
£100,000 and is it Offered, Completed, Fees Paid etc)

The hard bit for me to understand is the Relationships and HOW i make them.
Althought Peter, Raymond & Will are buying 6 Brighton Road, they may also be
buying another property aswell, and Peter & Will maybe buying a different
property together. I believe I need a Many To Many Relationship but do not
know how to do it.
I also want to produce one Form (if possible) with Tabs in it to show all
the Tables as Form View not as Subdatasheets.

Is this Possible??
Please can someone help me as I have used Access before and it is brilliant
but I just cannot get my head around how to create in it.
Many thanx for help in advance

 

[Ed Warren]

First problem out of the box is :

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)

You need a table that has one Id for each contact: I will call it Contacts:

ContactID (Primary key), ContactFirstName, ContactLastName, (other stuff
about that individual contact)

ContactID Contact
1 Peter
2 Raymond
3 Will


You need a table that has one Id for each Property I will call it
Properties:

PropertyID, PropertyDscription, PropertyStreet, PropertyCity, Property Stat,
PropertyZip, (other stuff about that individual property)

PropertyId PropertyDescription
1 60 Brighton Road
2 Old Yellow Farm House
3 White O'Morn

Contracts: An offer on One and only one property
ContractID (PK), Property(ID), AmountOffered, DateOffered, DateOfferExpires,
(other stuff about this specific contract)

ContractID PropertyID
1 1
2 1
3 2
4 2
5 3


Contract_Contacts (stuff about the people involved in making the contract)
(Many CONTACTS are related to Many CONTRACTS) M:M RELATIONSHIP

ContractID(FK) --> Contracts
ContactID(FK) -->Contacts
ContractID together with ContactID (Primary Key) Each Contact and be
related to Each Contract only once.

ContractID ContactID
1 1 (Peter)
1 2 (Raymond)
1 3 (Will)
5 1 (Peter)
5 3 (Will)

Peter, Raymond and Will are making an offer on 60 Brighton Road
Peter, and Will are making an offer on White O'Morn

I would 'expect' that the Mortage would relate M:1 to Contracts (offer),
(many mortage offers for each contract)

Finally Relationships:

Contacts 1 -->[contactID]--> M Contract_Contacts M -->[contractid]--> 1
Contracts M -->[propertyid]-->1 Property.
Contracts 1 -->[ContractID] -->M Mortages


Ed Warren

 

[Guest]

WOW!
Ed you are a star!
I will take probably all weekend to digest what you have said BUT I stress I
very much appreciate the expansive reply.
I understand the abbreviation PK = Primary but I presume KF - Foreign Key,
but I don't know hot to create that, could you enlighten me?

Once again thanx for the rapid and expansive reply

First problem out of the box is :

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)

You need a table that has one Id for each contact: I will call it Contacts:

ContactID (Primary key), ContactFirstName, ContactLastName, (other stuff
about that individual contact)

ContactID Contact
1 Peter
2 Raymond
3 Will


You need a table that has one Id for each Property I will call it
Properties:

PropertyID, PropertyDscription, PropertyStreet, PropertyCity, Property Stat,
PropertyZip, (other stuff about that individual property)

PropertyId PropertyDescription
1 60 Brighton Road
2 Old Yellow Farm House
3 White O'Morn

Contracts: An offer on One and only one property
ContractID (PK), Property(ID), AmountOffered, DateOffered, DateOfferExpires,
(other stuff about this specific contract)

ContractID PropertyID
1 1
2 1
3 2
4 2
5 3


Contract_Contacts (stuff about the people involved in making the contract)
(Many CONTACTS are related to Many CONTRACTS) M:M RELATIONSHIP

ContractID(FK) --> Contracts
ContactID(FK) -->Contacts
ContractID together with ContactID (Primary Key) Each Contact and be
related to Each Contract only once.

ContractID ContactID
1 1 (Peter)
1 2 (Raymond)
1 3 (Will)
5 1 (Peter)
5 3 (Will)

Peter, Raymond and Will are making an offer on 60 Brighton Road
Peter, and Will are making an offer on White O'Morn

I would 'expect' that the Mortage would relate M:1 to Contracts (offer),
(many mortage offers for each contract)

Finally Relationships:

Contacts 1 -->[contactID]--> M Contract_Contacts M -->[contractid]--> 1
Contracts M -->[propertyid]-->1 Property.
Contracts 1 -->[ContractID] -->M Mortages


Ed Warren

I am trying to create an Access Database from scratch and have never used
Access before. I am using Access 2003.
Could someone please help me in very simple terms to explain to me how the
best way is to view what I want to see please.

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)
I have Property Table, which shows the Property Address they are buying
(ie
Peter, Raymond & Will are buying 60 Brighton Road)
I have Mortgage Details Table, which shows the Mortgage Lender and
relevant
details (ie Peter, Raymond & Will are buying 60 Brighton Road with The
Halifax for £100,000)
I have Tracking Details Table, which shows all status's along the way (ie
Peter, Raymond & Will are buying 60 Brighton Road with The Halifax for
£100,000 and is it Offered, Completed, Fees Paid etc)

The hard bit for me to understand is the Relationships and HOW i make
them.
Althought Peter, Raymond & Will are buying 6 Brighton Road, they may also
be
buying another property aswell, and Peter & Will maybe buying a different
property together. I believe I need a Many To Many Relationship but do not
know how to do it.
I also want to produce one Form (if possible) with Tabs in it to show all
the Tables as Form View not as Subdatasheets.

Is this Possible??
Please can someone help me as I have used Access before and it is
brilliant
but I just cannot get my head around how to create in it.
Many thanx for help in advance

 

[Ed Warren]

Sorry about the I really meant fk (foreign key)

WOW!
Ed you are a star!
I will take probably all weekend to digest what you have said BUT I stress
I
very much appreciate the expansive reply.
I understand the abbreviation PK = Primary but I presume KF - Foreign Key,
but I don't know hot to create that, could you enlighten me?

Once again thanx for the rapid and expansive reply

First problem out of the box is :

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)

You need a table that has one Id for each contact: I will call it
Contacts:

ContactID (Primary key), ContactFirstName, ContactLastName, (other stuff
about that individual contact)

ContactID Contact
1 Peter
2 Raymond
3 Will


You need a table that has one Id for each Property I will call it
Properties:

PropertyID, PropertyDscription, PropertyStreet, PropertyCity, Property
Stat,
PropertyZip, (other stuff about that individual property)

PropertyId PropertyDescription
1 60 Brighton Road
2 Old Yellow Farm House
3 White O'Morn

Contracts: An offer on One and only one property
ContractID (PK), Property(ID), AmountOffered, DateOffered,
DateOfferExpires,
(other stuff about this specific contract)

ContractID PropertyID
1 1
2 1
3 2
4 2
5 3


Contract_Contacts (stuff about the people involved in making the
contract)
(Many CONTACTS are related to Many CONTRACTS) M:M RELATIONSHIP

ContractID(FK) --> Contracts
ContactID(FK) -->Contacts
ContractID together with ContactID (Primary Key) Each Contact and be
related to Each Contract only once.

ContractID ContactID
1 1 (Peter)
1 2 (Raymond)
1 3 (Will)
5 1 (Peter)
5 3 (Will)

Peter, Raymond and Will are making an offer on 60 Brighton Road
Peter, and Will are making an offer on White O'Morn

I would 'expect' that the Mortage would relate M:1 to Contracts (offer),
(many mortage offers for each contract)

Finally Relationships:

Contacts 1 -->[contactID]--> M Contract_Contacts M -->[contractid]--> 1
Contracts M -->[propertyid]-->1 Property.
Contracts 1 -->[ContractID] -->M Mortages


Ed Warren

I am trying to create an Access Database from scratch and have never
used
Access before. I am using Access 2003.
Could someone please help me in very simple terms to explain to me how
the
best way is to view what I want to see please.

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)
I have Property Table, which shows the Property Address they are
buying
(ie
Peter, Raymond & Will are buying 60 Brighton Road)
I have Mortgage Details Table, which shows the Mortgage Lender and
relevant
details (ie Peter, Raymond & Will are buying 60 Brighton Road with The
Halifax for £100,000)
I have Tracking Details Table, which shows all status's along the way
(ie
Peter, Raymond & Will are buying 60 Brighton Road with The Halifax for
£100,000 and is it Offered, Completed, Fees Paid etc)

The hard bit for me to understand is the Relationships and HOW i make
them.
Althought Peter, Raymond & Will are buying 6 Brighton Road, they may
also
be
buying another property aswell, and Peter & Will maybe buying a
different
property together. I believe I need a Many To Many Relationship but do
not
know how to do it.
I also want to produce one Form (if possible) with Tabs in it to show
all
the Tables as Form View not as Subdatasheets.

Is this Possible??
Please can someone help me as I have used Access before and it is
brilliant
but I just cannot get my head around how to create in it.
Many thanx for help in advance

 

[Guest]

How Do I creat a Foreign Key??
And I dont understand the relationships you have keyed Ed, sorry told you I
was new, could you help please

Sorry about the I really meant fk (foreign key)

WOW!
Ed you are a star!
I will take probably all weekend to digest what you have said BUT I stress
I
very much appreciate the expansive reply.
I understand the abbreviation PK = Primary but I presume KF - Foreign Key,
but I don't know hot to create that, could you enlighten me?

Once again thanx for the rapid and expansive reply

First problem out of the box is :

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)

You need a table that has one Id for each contact: I will call it
Contacts:

ContactID (Primary key), ContactFirstName, ContactLastName, (other stuff
about that individual contact)

ContactID Contact
1 Peter
2 Raymond
3 Will


You need a table that has one Id for each Property I will call it
Properties:

PropertyID, PropertyDscription, PropertyStreet, PropertyCity, Property
Stat,
PropertyZip, (other stuff about that individual property)

PropertyId PropertyDescription
1 60 Brighton Road
2 Old Yellow Farm House
3 White O'Morn

Contracts: An offer on One and only one property
ContractID (PK), Property(ID), AmountOffered, DateOffered,
DateOfferExpires,
(other stuff about this specific contract)

ContractID PropertyID
1 1
2 1
3 2
4 2
5 3


Contract_Contacts (stuff about the people involved in making the
contract)
(Many CONTACTS are related to Many CONTRACTS) M:M RELATIONSHIP

ContractID(FK) --> Contracts
ContactID(FK) -->Contacts
ContractID together with ContactID (Primary Key) Each Contact and be
related to Each Contract only once.

ContractID ContactID
1 1 (Peter)
1 2 (Raymond)
1 3 (Will)
5 1 (Peter)
5 3 (Will)

Peter, Raymond and Will are making an offer on 60 Brighton Road
Peter, and Will are making an offer on White O'Morn

I would 'expect' that the Mortage would relate M:1 to Contracts (offer),
(many mortage offers for each contract)

Finally Relationships:

Contacts 1 -->[contactID]--> M Contract_Contacts M -->[contractid]--> 1
Contracts M -->[propertyid]-->1 Property.
Contracts 1 -->[ContractID] -->M Mortages


Ed Warren


I am trying to create an Access Database from scratch and have never
used
Access before. I am using Access 2003.
Could someone please help me in very simple terms to explain to me how
the
best way is to view what I want to see please.

I have Contacts Table, which can have up to 3 contacts on each Id (ie,
Peter, Raymond & Will)
I have Property Table, which shows the Property Address they are
buying
(ie
Peter, Raymond & Will are buying 60 Brighton Road)
I have Mortgage Details Table, which shows the Mortgage Lender and
relevant
details (ie Peter, Raymond & Will are buying 60 Brighton Road with The
Halifax for £100,000)
I have Tracking Details Table, which shows all status's along the way
(ie
Peter, Raymond & Will are buying 60 Brighton Road with The Halifax for
£100,000 and is it Offered, Completed, Fees Paid etc)

The hard bit for me to understand is the Relationships and HOW i make
them.
Althought Peter, Raymond & Will are buying 6 Brighton Road, they may
also
be
buying another property aswell, and Peter & Will maybe buying a
different
property together. I believe I need a Many To Many Relationship but do
not
know how to do it.
I also want to produce one Form (if possible) with Tabs in it to show
all
the Tables as Form View not as Subdatasheets.

Is this Possible??
Please can someone help me as I have used Access before and it is
brilliant
but I just cannot get my head around how to create in it.
Many thanx for help in advance

 

[Guest]

Okay, I am now lost!!!
I do not understand how to produce a Foreign Key.
I also seem to have lost my way, I thought you were saying to create 4
Tables, Contacts, Property, Contracts & Contract_Contacts But then I see
Mortgage???
I have created four tables, Contact Details, Property Details, Mortgage
Details & Mortgage Details _Contacts.
I have been unable to follow your relationship reasoning and am unable to
comprehend how they will join in one Form and how to create Tabs to do this.
Please bear with me and help further as I need to try and get this done this
weekend.
Many thanx

 

[Ed Warren]

Step by Step.

1. Build the CONTACTS table
2. Build the PROPERTY table
3. Build the CONTRACTS table
4. Build the CONTRACTS_CONTACTS TABLE.

right click on the database window
open relationships

add the PROPERTY table
add the CONTRACTS table

click on the PropertyID in the Property Table
hold down the mousebutton and drag over to the Contracts Table
release the button

now you have a line connecting the two tables.

in the boxes below check the option "Enforce Referential Integrity"
Note the relationship type: One-to-Many
save and you have created your relationship.
the PropertyID in the Contract table is now a "Foreign Key"

Regards Mortage table, see the Orginial post -- this is a table you
mentioned, you should be able to figure out how to do that on your own after
you work through the example I provided.


Ed Warren.

How Do I creat a Foreign Key??
And I dont understand the relationships you have keyed Ed, sorry told you
I
was new, could you help please

Sorry about the I really meant fk (foreign key)

WOW!
Ed you are a star!
I will take probably all weekend to digest what you have said BUT I
stress
I
very much appreciate the expansive reply.
I understand the abbreviation PK = Primary but I presume KF - Foreign
Key,
but I don't know hot to create that, could you enlighten me?

Once again thanx for the rapid and expansive reply

:

First problem out of the box is :

I have Contacts Table, which can have up to 3 contacts on each Id
(ie,
Peter, Raymond & Will)

You need a table that has one Id for each contact: I will call it
Contacts:

ContactID (Primary key), ContactFirstName, ContactLastName, (other
stuff
about that individual contact)

ContactID Contact
1 Peter
2 Raymond
3 Will


You need a table that has one Id for each Property I will call it
Properties:

PropertyID, PropertyDscription, PropertyStreet, PropertyCity, Property
Stat,
PropertyZip, (other stuff about that individual property)

PropertyId PropertyDescription
1 60 Brighton Road
2 Old Yellow Farm House
3 White O'Morn

Contracts: An offer on One and only one property
ContractID (PK), Property(ID), AmountOffered, DateOffered,
DateOfferExpires,
(other stuff about this specific contract)

ContractID PropertyID
1 1
2 1
3 2
4 2
5 3


Contract_Contacts (stuff about the people involved in making the
contract)
(Many CONTACTS are related to Many CONTRACTS) M:M RELATIONSHIP

ContractID(FK) --> Contracts
ContactID(FK) -->Contacts
ContractID together with ContactID (Primary Key) Each Contact and be
related to Each Contract only once.

ContractID ContactID
1 1 (Peter)
1 2 (Raymond)
1 3 (Will)
5 1 (Peter)
5 3 (Will)

Peter, Raymond and Will are making an offer on 60 Brighton Road
Peter, and Will are making an offer on White O'Morn

I would 'expect' that the Mortage would relate M:1 to Contracts
(offer),
(many mortage offers for each contract)

Finally Relationships:

Contacts 1 -->[contactID]--> M Contract_Contacts M -->[contractid]-->
1
Contracts M -->[propertyid]-->1 Property.
Contracts 1 -->[ContractID] -->M Mortages


Ed Warren


I am trying to create an Access Database from scratch and have never
used
Access before. I am using Access 2003.
Could someone please help me in very simple terms to explain to me
how
the
best way is to view what I want to see please.

I have Contacts Table, which can have up to 3 contacts on each Id
(ie,
Peter, Raymond & Will)
I have Property Table, which shows the Property Address they are
buying
(ie
Peter, Raymond & Will are buying 60 Brighton Road)
I have Mortgage Details Table, which shows the Mortgage Lender and
relevant
details (ie Peter, Raymond & Will are buying 60 Brighton Road with
The
Halifax for £100,000)
I have Tracking Details Table, which shows all status's along the
way
(ie
Peter, Raymond & Will are buying 60 Brighton Road with The Halifax
for
£100,000 and is it Offered, Completed, Fees Paid etc)

The hard bit for me to understand is the Relationships and HOW i
make
them.
Althought Peter, Raymond & Will are buying 6 Brighton Road, they may
also
be
buying another property aswell, and Peter & Will maybe buying a
different
property together. I believe I need a Many To Many Relationship but
do
not
know how to do it.
I also want to produce one Form (if possible) with Tabs in it to
show
all
the Tables as Form View not as Subdatasheets.

Is this Possible??
Please can someone help me as I have used Access before and it is
brilliant
but I just cannot get my head around how to create in it.
Many thanx for help in advance

 

[Jeff Boyce]

Perhaps it is a matter of definition...

A primary key is a unique identifier for a row in a table.

A foreign key is a field in another table. It (the foreign key) is used to
"point back" to the row in the first table to which this row in the second
table "belongs".

In other terms, a "parent" table has an ID to identify each parent. That ID
is a Primary Key in that table. A "child" table has an ID to identify each
child. That ID is a Primary Key in that table. And the child table also
has a field that holds that child's ParentID (this is the foreign key).

Has this only confused the issue?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

That has helped expalin it but I am still very much confused grrrr

 

[Van T. Dinh]

See the following Microsoft Knowledge Base article:

http://support.microsoft.com/?id=283698

and more references (including the above) in:

http://support.microsoft.com/?id=289533