I need help with creating an Access Database

  • Thread starter Thread starter Rita D via AccessMonster.com
  • Start date Start date
R

Rita D via AccessMonster.com

I am putting together a marketing database and I need help or some
suggestions before I start creating it.
I need to capture contacts with the following information:

First Name
Last Name
Title
Business Name
Street address
P.O. Box address
e-mail
work phone
cell phone
fax

The contact files also need to sort by classifications such as:
(One contact file may have one or many classifications)

press releases
newsletter
general mailings
holiday cards




Contacts need to have a field that shows:

last updated: date contact information was verified

I also need to be able to highlight what organizations they are in, whether
they are part of:
(Also, a contact file may be,ong to one or more organizations)

Public Employee
Private Employee
International Road Federation leadership
National Council for Public Private Partnerships


This is what I'm thinking about doing;

Create one table called contact and add the following;
User ID Primary Key Autonumber
First Name
Last Name
Title
Business Name
Street address
P.O. Box address
e-mail
work phone
cell phone
fax

Then create another table called

Classification and add the following

ID Primary key auto number
User ID Foreign Key (link it to contact as a one to many)
press releases
newsletter
general mailings
holiday cards

Then add another table called
Organization and add the following;

ID Primary key auto number
User ID Foreign Key (link it to contact as a one to many)
Public Employee
Private Employee
International Road Federation leadership
National Council for Public Private Partnerships

I don't what to do about this, "Contacts need to have a field that shows:

last updated: date contact information was verified "

Then I can perhaps create a form and have a list not a drop down combo box
where I can have the user select the organizations and classification from
the list.


Am I on the right track?
I sure appreciate any help.

Thanks,

Rita
 
Rita said:
This is what I'm thinking about doing;

Create one table called contact and add the following;
User ID Primary Key Autonumber
First Name
Last Name
Title
Business Name
Street address
P.O. Box address
e-mail
work phone
cell phone
fax
Click to expand...

Consider removing the spaces in the field names; I'd also not use a '.' in a
field name, so instead of P.O. Box address use MailingAddress.
You might also consider that a person could have many phone numbers, so
these could be put in a separate table

tblPhoneNumbers
UserID
PhoneType (work, home, cell, fax)
PhoneNumber
Then create another table called

Classification and add the following

ID Primary key auto number
User ID Foreign Key (link it to contact as a one to many)
press releases
newsletter
general mailings
holiday cards
Click to expand...

No. Those last four items are data, not fields. Instead create a table to
hold the Classifications (it'll just have a few entries)
ClassificationID
Classification

Then a linking table between Contacts and Classifications (one contact can
have many classifications, and one classification belongs to many contacts).
That's a many to many relationship requiring this 'linking table'
UserID
ClassificationID
You'd create a relationship from Contacts.UserID (1) to UserID in this table
(many)
and also create a relationship from Classifications.ClassificationID (1) to
ClassificationID in this table (many).
Then add another table called
Organization and add the following;

ID Primary key auto number
User ID Foreign Key (link it to contact as a one to many)
Public Employee
Private Employee
International Road Federation leadership
National Council for Public Private Partnerships
Click to expand...

Repeat for this, what I said above about Classifications.
I don't what to do about this, "Contacts need to have a field that
shows:

last updated: date contact information was verified "
Click to expand...

You can include a date/time field in the Contacts table, however you need to
determine what you really want. 'Last updated' could mean the last time the
record was changed, however does that really mean it was verified?
 
Rita said:
I am putting together a marketing database and I need help or some
suggestions before I start creating it.
I need to capture contacts with the following information:

First Name
Last Name
Title
Business Name
Street address
P.O. Box address
e-mail
work phone
cell phone
fax

The contact files also need to sort by classifications such as:
(One contact file may have one or many classifications)

press releases
newsletter
general mailings
holiday cards

Contacts need to have a field that shows:

last updated: date contact information was verified

I also need to be able to highlight what organizations they are in, whether
they are part of:
(Also, a contact file may be,ong to one or more organizations)

Public Employee
Private Employee
International Road Federation leadership
National Council for Public Private Partnerships

This is what I'm thinking about doing;

Create one table called contact and add the following;
User ID Primary Key Autonumber
First Name
Last Name
Title
Business Name
Street address
P.O. Box address
e-mail
work phone
cell phone
fax

Then create another table called

Classification and add the following

ID Primary key auto number
User ID Foreign Key (link it to contact as a one to many)
press releases
newsletter
general mailings
holiday cards

Then add another table called
Organization and add the following;

ID Primary key auto number
User ID Foreign Key (link it to contact as a one to many)
Public Employee
Private Employee
International Road Federation leadership
National Council for Public Private Partnerships

I don't what to do about this, "Contacts need to have a field that shows:

last updated: date contact information was verified "

Then I can perhaps create a form and have a list not a drop down combo box
where I can have the user select the organizations and classification from
the list.

Am I on the right track?
I sure appreciate any help.

Thanks,

Rita
Click to expand...


Thanks for your replies. This is what did but I'm still having problems when
entering the data. Are they correctly linked?

I created the tblContact
ContactId PK AutoNumber
First Name text
Last Name text
Title text
Business Name text
Street address text
P.O. Box address text
e-mail text

Then I created tblPhoneNumber

NumberId PK AutoNumber
ContactId Foreign Key to tblContact (One to Many from tblContact to
tblPhoneNumber) number
PhoneType (Combo Box) " Work, Home Phone, Cell Phone, Fax" text
PhoneNumber text


Then I created tblOrganizationLink (Note, I did not assigned Primary Keys on
here. Is that ok?)

ContactId ( One to Many from tblContact to tblOrganizationLink) Number
OrganizationId Number ( One to Many from tblOrganization to
tblOrganizationLink)

Then I created tblOrganization

OrganizationId PK AutoNumber ( One to Many from tblOrganization to
tblOrganizationLink)
Organization text Combo Box

Then I created tblClassificationLink (Note, I did not assigned Primary Keys
on here. Is that ok?)

ContactId number ( One to Many from tblContact to
tblClassificationLink) Number

ClassificationId number ( One to Many from tblClassification to
tblClassificationLink)

Then I created tblClassification

ClassificationId PK Autonumber ( One to Many from tblClassification to
tblClassificationLink)

Classification text Combo Box


I created a quick query and when I run it, it gives me duplicates. I've
entered a small sample of data. Three Names, so the query gives me six names.
Is the assigned data type wrong?

Please I'd appreciate any help/suggestions since I'm new to Access.

Thanks,

Rita
 
Please Help

Rita said:
I am putting together a marketing database and I need help or some
suggestions before I start creating it.
Click to expand...
[quoted text clipped - 82 lines]
Rita
Click to expand...

Thanks for your replies. This is what did but I'm still having problems when
entering the data. Are they correctly linked?

I created the tblContact
ContactId PK AutoNumber
First Name text
Last Name text
Title text
Business Name text
Street address text
P.O. Box address text
e-mail text

Then I created tblPhoneNumber

NumberId PK AutoNumber
ContactId Foreign Key to tblContact (One to Many from tblContact to
tblPhoneNumber) number
PhoneType (Combo Box) " Work, Home Phone, Cell Phone, Fax" text
PhoneNumber text

Then I created tblOrganizationLink (Note, I did not assigned Primary Keys on
here. Is that ok?)

ContactId ( One to Many from tblContact to tblOrganizationLink) Number
OrganizationId Number ( One to Many from tblOrganization to
tblOrganizationLink)

Then I created tblOrganization

OrganizationId PK AutoNumber ( One to Many from tblOrganization to
tblOrganizationLink)
Organization text Combo Box

Then I created tblClassificationLink (Note, I did not assigned Primary Keys
on here. Is that ok?)

ContactId number ( One to Many from tblContact to
tblClassificationLink) Number

ClassificationId number ( One to Many from tblClassification to
tblClassificationLink)

Then I created tblClassification

ClassificationId PK Autonumber ( One to Many from tblClassification to
tblClassificationLink)

Classification text Combo Box

I created a quick query and when I run it, it gives me duplicates. I've
entered a small sample of data. Three Names, so the query gives me six names.
Is the assigned data type wrong?

Please I'd appreciate any help/suggestions since I'm new to Access.

Thanks,

Rita
Click to expand...
 
Back
Top