 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / February 2007One to many relationships
I have a master database called "Master List". It contains ID, Name, Address, phone, etc. I have a detail database called "Transactions" It contains, ID, Transaction, event, date, etc. (many records for each in "Master List") I have a detail database called "Connections". It contains ID and Connection type (many records for each in "Master List") I have setup a one to many relation ship with "Master List" and "Transactions." I have setup a one to many relationship with "Master List and "Connections" I have my form working fine updating transactions and connections. My questions is how do I make this link in creating reports. I want to create a report that lists the record information from "Master list" then I want it to list all the connections for that person from "Connections" and then I want it to list all the transactions for that person from "Transactions" . _____________________________________________ HEADER Name Address City, State Zip Phone, Cell, Wk # DETAIL Connections (may have 5 connections) Transactions (may have 10 transactions) ______________________________________________________ Any help would be greatly appreciated. Sarah >I have a master database called "Master List". It contains ID, Name, > Address, phone, etc. [quoted text clipped - 5 lines] > I have a detail database called "Connections". It contains ID and > Connection type (many records for each in "Master List") I assume what you actually have are tables called these; all in one database file. > My questions is how do I make this link in creating reports. Much like you did for your form. Create a report for the Master List. Then create sub-reports for the Connections and Transactions - link Master/Child on ID.  SignatureJoan Wild Microsoft Access MVP > >I have a master database called "Master List". It contains ID, Name, > > Address, phone, etc. [quoted text clipped - 14 lines] > create sub-reports for the Connections and Transactions - link Master/Child > on ID. Joan, Thank you for your help. That 's exactly what I needed. Sarah > >I have a master database called "Master List". It contains ID, Name, > > Address, phone, etc. [quoted text clipped - 14 lines] > create sub-reports for the Connections and Transactions - link Master/Child > on ID. If your primary key in the [Master List] table is named ID and the primary key in the [Transactions] table is named ID, then you have NOT created a one to many relationship. Rather you have created a one to one relationship. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html >I have a master database called "Master List". It contains ID, Name, > Address, phone, etc. [quoted text clipped - 36 lines] > > Sarah hi there Lynn I was wonder if you can help my .When i try to set a one-too- many relationship it doesn't work ....how do i set this...i only have the one field in three tables that are the same...I have an employe master file, an employee deduction ledger file, and a employe income ledger file.... i was wonder if you can help me or if anyone can me for that matter. > hi there Lynn I was wonder if you can help my .When i try to set a > one-too- > many relationship it doesn't work ....how do i set this... Katrina, Can you tell me what you tried to do to set that relationship? > i only have the > one field in three tables that are the same...I have an employe master > file, an employee deduction ledger file, and a employe income ledger > file.... i was wonder if you can help me or if anyone can me for that > matter. Could you post the entire table structure of your tables along with a description of the business problem you are trying to solve? SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html This is unrelated to Sarah's question. How would one set up the one to many link between Master List and Transactions? If the ID in Master List is a primary key, autonumber field, and the ID in transactions is not a primary key, number field, would those two fields link. I have tried such a link but I am not able to enforce referential integrity or get cascading updates? > I have a master database called "Master List". It contains ID, Name, > Address, phone, etc. [quoted text clipped - 34 lines] > > Sarah >This is unrelated to Sarah's question. How would one set up the one to many >link between Master List and Transactions? If the ID in Master List is a >primary key, autonumber field, and the ID in transactions is not a primary >key, number field, would those two fields link. I have tried such a link but >I am not able to enforce referential integrity or get cascading updates? What have you tried? What errors have you gotten? An Autonumber primary key should link easily to a Number/Long Integer field in the transactions table. In the Relationships Window you'ld add both tables, and drag the Autonumber field to the foreign key long integer field; select the join line and check the Enforce RI checkbox. There is NO point in specifying Cascade Updates on such a link, though; you can't update an Autonumber field in any case, and there is therefore nothing to cascade. If you wish (and care to take the risk of deleting lots of data by accident) you can specify Cascade Deletes; this will delete all transactions related to a Master List item if that item should be deleted. John W. Vinson [MVP] When I click on Enforce Referential Integrity, Cascade Updates or Cascade Deletes, I get the message, Relationship must be on the same number of fields with the same data types. > >This is unrelated to Sarah's question. How would one set up the one to many > >link between Master List and Transactions? If the ID in Master List is a [quoted text clipped - 17 lines] > > John W. Vinson [MVP] >When I click on Enforce Referential Integrity, Cascade Updates or Cascade >Deletes, I get the message, Relationship must be on the same number of fields >with the same data types. What are the datatypes and sizes of the two fields you're trying to join? An Autonumber must be linked to a Number of Long Integer type; you can't join an autonumber to (say) a Float or Double. John W. Vinson [MVP] |
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.