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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I need help with creating an Access Database
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 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 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200602/1 |
#2
|
|||
|
|||
I need help with creating an Access Database
Rita D via AccessMonster.com wrote:
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 work phone cell phone fax 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 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 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 " 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? -- Joan Wild Microsoft Access MVP |
#3
|
|||
|
|||
I need help with creating an Access Database
What about also adding fields for --
City State ZIP "Joan Wild" wrote: Rita D via AccessMonster.com wrote: 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 work phone cell phone fax 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 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 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 " 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? -- Joan Wild Microsoft Access MVP |
#4
|
|||
|
|||
I need help with creating an Access Database
Rita D wrote:
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 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 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 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200602/1 |
#5
|
|||
|
|||
I need help with creating an Access Database
Please Help
Rita D wrote: I am putting together a marketing database and I need help or some suggestions before I start creating it. [quoted text clipped - 82 lines] Rita 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200602/1 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access 2002 vs. 2003 | Neil | General Discussions | 51 | February 16th, 2006 10:18 PM |
creating email message using a field in access database as the 'to | NancyQuackenbush | General Discussion | 1 | January 17th, 2006 08:37 PM |
What is the difference between 2002 and 2003? | Red Sonya | General Discussion | 2 | March 1st, 2005 05:10 AM |
Exclusive access to the database | Steve Huff | General Discussion | 17 | December 24th, 2004 06:23 PM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |