Table Relationships: An Understanding is necessary...
in [General]
|
From: PsyberFox on 29 May 2010 05:47 OK I have done exactly as recommended but the child records are not showing up for each parent record. I have double-checked the links between the forms. Even dragged & dropped sub-form but still nothing... W -- The Psyber Fox http://www.psyberconsulting.co.za "Larry Linson" wrote: > You've already received the good advice to create a Form for Patient, with a > Subform Control containing the Form for Wound Detail. Make the Form for > Wound Detail a continuous forms view form. > > Separately, you received the advice not to use Table View and Subdatasheets. > > I concur with both recommendations... create Forms and Subforms; don't > update Tables in Table View and don't use Subdatasheets. I don't know how > we could be more clear in what we are advising. > > -- > Larry Linson, Microsoft Office Access MVP > Co-author: "Microsoft Access Small Business Solutions", published by Wiley > Access newsgroup support is alive and well in USENET > comp.databases.ms-access > > > "PsyberFox" <PsyberFox(a)discussions.microsoft.com> wrote in message > news:980CF1E4-77B0-4B22-A85D-801D8AC5E365(a)microsoft.com... > > Hi, > > > > OK, I've now managed to do what you suggested with regards to changing the > > table's subdatasheet. But it's still not showing the records from the > > child-table in the parent table cascade... > > > > Rgds, > > W > > > > -- > > The Psyber Fox > > http://www.psyberconsulting.co.za > > > > > > "Keven Denen" wrote: > > > >> On May 28, 5:48 am, PsyberFox <Psyber...(a)discussions.microsoft.com> > >> wrote: > >> > Hi there, > >> > > >> > I thought I understood table relationships in Access (2007 btw) but > >> > clearly > >> > I'm missing something... so hope someone can assist me: > >> > > >> > I have two tables that need to be linked: tblPatients, and > >> > tblWoundDetails. > >> > tblWoundDetails can contain many records of a patient. The PatientID in > >> > tblWoundDetails is selected off a drop-down lookup linking to > >> > tblPatients.. I > >> > have set up a table relationship as well, linking these two tables on > >> > PatientID. It shows a one-to-many relationship type, and I am setting > >> > it up > >> > to say that I want all the records from tblPatients and only those > >> > records > >> > from tblWoundDetails where there are matching records. Now, what I want > >> > to > >> > achieve is when opening up tblPatients it should be able to show with a > >> > cascading + all the records from tblWoundDetails. Fristly, I am getting > >> > it > >> > the other way round, and secondly when expanding the details from > >> > tblWoundDetails it doesn't show the matching record from tblPatients. > >> > (I have > >> > captured records in both tables, so it should at least show the data). > >> > Hope > >> > all of this makes sense... > >> > > >> > Thank you kindly! > >> > W > >> > > >> > -- > >> > The Psyber Foxhttp://www.psyberconsulting.co.za > >> > >> Standard warning: You shouldn't be mucking around with subdatasheets > >> for viewing and modifying your data in your tables. You should be > >> creating forms to view and modify your data. > >> > >> That said, if you are doing this just for yourself and don't care > >> about proper design or you are doing things this way for the short > >> term and just haven't taken the time to create your forms, here's an > >> answer to your question. > >> > >> I'm not sure what you mean by getting it the other way around. As to > >> not getting the subdatasheet in tblWoundDetails, Access only creates > >> the subdatasheet by default on the 1 side of a 1 to many relationship > >> (so there should be a subdatasheet in tblPatients). You can add and > >> modify the subdatasheet settings for each table by going to the Ribbon > >> in Datasheet view. Make sure you are on the Table Tools tab, then > >> click the More button in the Records section of the ribbon. Under More > >> there should be an option for adjusting the subdatasheets. > >> > >> Keven > >> . > >> > > > . >
From: PsyberFox on 29 May 2010 07:03 I'm now confounded as to what the problem might be: I have created a simply query linking tblP with tblWD on PatientID and when viewing the query it's not even pulling throught the records from tblWD for each tblP record... Please help! W -- The Psyber Fox http://www.psyberconsulting.co.za "Larry Linson" wrote: > You've already received the good advice to create a Form for Patient, with a > Subform Control containing the Form for Wound Detail. Make the Form for > Wound Detail a continuous forms view form. > > Separately, you received the advice not to use Table View and Subdatasheets. > > I concur with both recommendations... create Forms and Subforms; don't > update Tables in Table View and don't use Subdatasheets. I don't know how > we could be more clear in what we are advising. > > -- > Larry Linson, Microsoft Office Access MVP > Co-author: "Microsoft Access Small Business Solutions", published by Wiley > Access newsgroup support is alive and well in USENET > comp.databases.ms-access > > > "PsyberFox" <PsyberFox(a)discussions.microsoft.com> wrote in message > news:980CF1E4-77B0-4B22-A85D-801D8AC5E365(a)microsoft.com... > > Hi, > > > > OK, I've now managed to do what you suggested with regards to changing the > > table's subdatasheet. But it's still not showing the records from the > > child-table in the parent table cascade... > > > > Rgds, > > W > > > > -- > > The Psyber Fox > > http://www.psyberconsulting.co.za > > > > > > "Keven Denen" wrote: > > > >> On May 28, 5:48 am, PsyberFox <Psyber...(a)discussions.microsoft.com> > >> wrote: > >> > Hi there, > >> > > >> > I thought I understood table relationships in Access (2007 btw) but > >> > clearly > >> > I'm missing something... so hope someone can assist me: > >> > > >> > I have two tables that need to be linked: tblPatients, and > >> > tblWoundDetails. > >> > tblWoundDetails can contain many records of a patient. The PatientID in > >> > tblWoundDetails is selected off a drop-down lookup linking to > >> > tblPatients.. I > >> > have set up a table relationship as well, linking these two tables on > >> > PatientID. It shows a one-to-many relationship type, and I am setting > >> > it up > >> > to say that I want all the records from tblPatients and only those > >> > records > >> > from tblWoundDetails where there are matching records. Now, what I want > >> > to > >> > achieve is when opening up tblPatients it should be able to show with a > >> > cascading + all the records from tblWoundDetails. Fristly, I am getting > >> > it > >> > the other way round, and secondly when expanding the details from > >> > tblWoundDetails it doesn't show the matching record from tblPatients. > >> > (I have > >> > captured records in both tables, so it should at least show the data). > >> > Hope > >> > all of this makes sense... > >> > > >> > Thank you kindly! > >> > W > >> > > >> > -- > >> > The Psyber Foxhttp://www.psyberconsulting.co.za > >> > >> Standard warning: You shouldn't be mucking around with subdatasheets > >> for viewing and modifying your data in your tables. You should be > >> creating forms to view and modify your data. > >> > >> That said, if you are doing this just for yourself and don't care > >> about proper design or you are doing things this way for the short > >> term and just haven't taken the time to create your forms, here's an > >> answer to your question. > >> > >> I'm not sure what you mean by getting it the other way around. As to > >> not getting the subdatasheet in tblWoundDetails, Access only creates > >> the subdatasheet by default on the 1 side of a 1 to many relationship > >> (so there should be a subdatasheet in tblPatients). You can add and > >> modify the subdatasheet settings for each table by going to the Ribbon > >> in Datasheet view. Make sure you are on the Table Tools tab, then > >> click the More button in the Records section of the ribbon. Under More > >> there should be an option for adjusting the subdatasheets. > >> > >> Keven > >> . > >> > > > . >
From: PsyberFox on 29 May 2010 07:18 EVENTUALLY SOLVED THE PROBLEM!!! Ok, just an update: it had nothing to do with creating forms / subforms, etc... it was the way the child records were getting it's information from the parent record. I have a lookup field in the child table, which looks up the PatientID in tblP. However, in tblP the PatientID is the second field. I moved this to the first field, and voila!!! W -- The Psyber Fox http://www.psyberconsulting.co.za "PsyberFox" wrote: > I'm now confounded as to what the problem might be: > I have created a simply query linking tblP with tblWD on PatientID and when > viewing the query it's not even pulling throught the records from tblWD for > each tblP record... > Please help! > W > > -- > The Psyber Fox > http://www.psyberconsulting.co.za > > > "Larry Linson" wrote: > > > You've already received the good advice to create a Form for Patient, with a > > Subform Control containing the Form for Wound Detail. Make the Form for > > Wound Detail a continuous forms view form. > > > > Separately, you received the advice not to use Table View and Subdatasheets. > > > > I concur with both recommendations... create Forms and Subforms; don't > > update Tables in Table View and don't use Subdatasheets. I don't know how > > we could be more clear in what we are advising. > > > > -- > > Larry Linson, Microsoft Office Access MVP > > Co-author: "Microsoft Access Small Business Solutions", published by Wiley > > Access newsgroup support is alive and well in USENET > > comp.databases.ms-access > > > > > > "PsyberFox" <PsyberFox(a)discussions.microsoft.com> wrote in message > > news:980CF1E4-77B0-4B22-A85D-801D8AC5E365(a)microsoft.com... > > > Hi, > > > > > > OK, I've now managed to do what you suggested with regards to changing the > > > table's subdatasheet. But it's still not showing the records from the > > > child-table in the parent table cascade... > > > > > > Rgds, > > > W > > > > > > -- > > > The Psyber Fox > > > http://www.psyberconsulting.co.za > > > > > > > > > "Keven Denen" wrote: > > > > > >> On May 28, 5:48 am, PsyberFox <Psyber...(a)discussions.microsoft.com> > > >> wrote: > > >> > Hi there, > > >> > > > >> > I thought I understood table relationships in Access (2007 btw) but > > >> > clearly > > >> > I'm missing something... so hope someone can assist me: > > >> > > > >> > I have two tables that need to be linked: tblPatients, and > > >> > tblWoundDetails. > > >> > tblWoundDetails can contain many records of a patient. The PatientID in > > >> > tblWoundDetails is selected off a drop-down lookup linking to > > >> > tblPatients.. I > > >> > have set up a table relationship as well, linking these two tables on > > >> > PatientID. It shows a one-to-many relationship type, and I am setting > > >> > it up > > >> > to say that I want all the records from tblPatients and only those > > >> > records > > >> > from tblWoundDetails where there are matching records. Now, what I want > > >> > to > > >> > achieve is when opening up tblPatients it should be able to show with a > > >> > cascading + all the records from tblWoundDetails. Fristly, I am getting > > >> > it > > >> > the other way round, and secondly when expanding the details from > > >> > tblWoundDetails it doesn't show the matching record from tblPatients. > > >> > (I have > > >> > captured records in both tables, so it should at least show the data). > > >> > Hope > > >> > all of this makes sense... > > >> > > > >> > Thank you kindly! > > >> > W > > >> > > > >> > -- > > >> > The Psyber Foxhttp://www.psyberconsulting.co.za > > >> > > >> Standard warning: You shouldn't be mucking around with subdatasheets > > >> for viewing and modifying your data in your tables. You should be > > >> creating forms to view and modify your data. > > >> > > >> That said, if you are doing this just for yourself and don't care > > >> about proper design or you are doing things this way for the short > > >> term and just haven't taken the time to create your forms, here's an > > >> answer to your question. > > >> > > >> I'm not sure what you mean by getting it the other way around. As to > > >> not getting the subdatasheet in tblWoundDetails, Access only creates > > >> the subdatasheet by default on the 1 side of a 1 to many relationship > > >> (so there should be a subdatasheet in tblPatients). You can add and > > >> modify the subdatasheet settings for each table by going to the Ribbon > > >> in Datasheet view. Make sure you are on the Table Tools tab, then > > >> click the More button in the Records section of the ribbon. Under More > > >> there should be an option for adjusting the subdatasheets. > > >> > > >> Keven > > >> . > > >> > > > > > > . > >
From: Larry Linson on 29 May 2010 17:52 Ah, you learned the HARD WAY what many have been strongly advising in the newsgroups ever since Lookup Fields became available -- don't use Lookup Fields. Use Lookup Tables, so that Access doesn't confuse you by displaying something other than what is stored in the Field, but then retrieve what IS stored in the Field when you refer to it in Queries. -- Larry Linson, Microsoft Office Access MVP Co-author: "Microsoft Access Small Business Solutions", published by Wiley Access newsgroup support is alive and well in USENET comp.databases.ms-access "PsyberFox" <PsyberFox(a)discussions.microsoft.com> wrote in message news:86763BBF-9A72-4A79-B9C0-DECB8AEF7ABC(a)microsoft.com... > EVENTUALLY SOLVED THE PROBLEM!!! > Ok, just an update: it had nothing to do with creating forms / subforms, > etc... it was the way the child records were getting it's information from > the parent record. I have a lookup field in the child table, which looks > up > the PatientID in tblP. However, in tblP the PatientID is the second field. > I > moved this to the first field, and voila!!! > W > -- > The Psyber Fox > http://www.psyberconsulting.co.za > > > "PsyberFox" wrote: > >> I'm now confounded as to what the problem might be: >> I have created a simply query linking tblP with tblWD on PatientID and >> when >> viewing the query it's not even pulling throught the records from tblWD >> for >> each tblP record... >> Please help! >> W >> >> -- >> The Psyber Fox >> http://www.psyberconsulting.co.za >> >> >> "Larry Linson" wrote: >> >> > You've already received the good advice to create a Form for Patient, >> > with a >> > Subform Control containing the Form for Wound Detail. Make the Form >> > for >> > Wound Detail a continuous forms view form. >> > >> > Separately, you received the advice not to use Table View and >> > Subdatasheets. >> > >> > I concur with both recommendations... create Forms and Subforms; don't >> > update Tables in Table View and don't use Subdatasheets. I don't know >> > how >> > we could be more clear in what we are advising. >> > >> > -- >> > Larry Linson, Microsoft Office Access MVP >> > Co-author: "Microsoft Access Small Business Solutions", published by >> > Wiley >> > Access newsgroup support is alive and well in USENET >> > comp.databases.ms-access >> > >> > >> > "PsyberFox" <PsyberFox(a)discussions.microsoft.com> wrote in message >> > news:980CF1E4-77B0-4B22-A85D-801D8AC5E365(a)microsoft.com... >> > > Hi, >> > > >> > > OK, I've now managed to do what you suggested with regards to >> > > changing the >> > > table's subdatasheet. But it's still not showing the records from the >> > > child-table in the parent table cascade... >> > > >> > > Rgds, >> > > W >> > > >> > > -- >> > > The Psyber Fox >> > > http://www.psyberconsulting.co.za >> > > >> > > >> > > "Keven Denen" wrote: >> > > >> > >> On May 28, 5:48 am, PsyberFox <Psyber...(a)discussions.microsoft.com> >> > >> wrote: >> > >> > Hi there, >> > >> > >> > >> > I thought I understood table relationships in Access (2007 btw) >> > >> > but >> > >> > clearly >> > >> > I'm missing something... so hope someone can assist me: >> > >> > >> > >> > I have two tables that need to be linked: tblPatients, and >> > >> > tblWoundDetails. >> > >> > tblWoundDetails can contain many records of a patient. The >> > >> > PatientID in >> > >> > tblWoundDetails is selected off a drop-down lookup linking to >> > >> > tblPatients.. I >> > >> > have set up a table relationship as well, linking these two tables >> > >> > on >> > >> > PatientID. It shows a one-to-many relationship type, and I am >> > >> > setting >> > >> > it up >> > >> > to say that I want all the records from tblPatients and only those >> > >> > records >> > >> > from tblWoundDetails where there are matching records. Now, what I >> > >> > want >> > >> > to >> > >> > achieve is when opening up tblPatients it should be able to show >> > >> > with a >> > >> > cascading + all the records from tblWoundDetails. Fristly, I am >> > >> > getting >> > >> > it >> > >> > the other way round, and secondly when expanding the details from >> > >> > tblWoundDetails it doesn't show the matching record from >> > >> > tblPatients. >> > >> > (I have >> > >> > captured records in both tables, so it should at least show the >> > >> > data). >> > >> > Hope >> > >> > all of this makes sense... >> > >> > >> > >> > Thank you kindly! >> > >> > W >> > >> > >> > >> > -- >> > >> > The Psyber Foxhttp://www.psyberconsulting.co.za >> > >> >> > >> Standard warning: You shouldn't be mucking around with subdatasheets >> > >> for viewing and modifying your data in your tables. You should be >> > >> creating forms to view and modify your data. >> > >> >> > >> That said, if you are doing this just for yourself and don't care >> > >> about proper design or you are doing things this way for the short >> > >> term and just haven't taken the time to create your forms, here's an >> > >> answer to your question. >> > >> >> > >> I'm not sure what you mean by getting it the other way around. As to >> > >> not getting the subdatasheet in tblWoundDetails, Access only creates >> > >> the subdatasheet by default on the 1 side of a 1 to many >> > >> relationship >> > >> (so there should be a subdatasheet in tblPatients). You can add and >> > >> modify the subdatasheet settings for each table by going to the >> > >> Ribbon >> > >> in Datasheet view. Make sure you are on the Table Tools tab, then >> > >> click the More button in the Records section of the ribbon. Under >> > >> More >> > >> there should be an option for adjusting the subdatasheets. >> > >> >> > >> Keven >> > >> . >> > >> >> > >> > >> > . >> >
First
|
Prev
|
Pages: 1 2 Prev: My last post in this forum Next: Which forum or website are the regulars switching to? |