 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / April 2006New To Access
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 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. [quoted text clipped - 29 lines] > but I just cannot get my head around how to create in it. > Many thanx for help in advance 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 : > [quoted text clipped - 95 lines] > > but I just cannot get my head around how to create in it. > > Many thanx for help in advance Sorry about the I really meant fk (foreign key) > WOW! > Ed you are a star! [quoted text clipped - 117 lines] >> > but I just cannot get my head around how to create in it. >> > Many thanx for help in advance 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! [quoted text clipped - 118 lines] > >> > but I just cannot get my head around how to create in it. > >> > Many thanx for help in advance 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 [quoted text clipped - 137 lines] >> >> > but I just cannot get my head around how to create in it. >> >> > Many thanx for help in advance 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 > I am trying to create an Access Database from scratch and have never used > Access before. I am using Access 2003. [quoted text clipped - 24 lines] > but I just cannot get my head around how to create in it. > Many thanx for help in advance 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 > Okay, I am now lost!!! > I do not understand how to produce a Foreign Key. [quoted text clipped - 46 lines] >> but I just cannot get my head around how to create in it. >> Many thanx for help in advance That has helped expalin it but I am still very much confused grrrr > Perhaps it is a matter of definition... > [quoted text clipped - 66 lines] > >> but I just cannot get my head around how to create in it. > >> Many thanx for help in advance 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  SignatureHTH Van T. Dinh MVP (Access) > That has helped expalin it but I am still very much confused grrrr > [quoted text clipped - 80 lines] >> >> but I just cannot get my head around how to create in it. >> >> Many thanx for help in advance |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.