A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table Relationships: An Understanding is necessary...



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 12:48 PM posted to microsoft.public.access
PsyberFox
external usenet poster
 
Posts: 88
Default Table Relationships: An Understanding is necessary...

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 Fox
http://www.psyberconsulting.co.za
  #2  
Old May 28th, 2010, 02:37 PM posted to microsoft.public.access
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Table Relationships: An Understanding is necessary...

On Fri, 28 May 2010 04:48:01 -0700, PsyberFox
wrote:

Try this:
Have the wizard create a new form based solely on tblPatients. Also a
new form based solely on tblWD. Design the first form and drop the
second one on it. Use the wizard to create the relation. Voila, you
have the basis of a working master-detail form.

-Tom.
Microsoft Access MVP


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

  #3  
Old May 28th, 2010, 02:41 PM posted to microsoft.public.access
Keven Denen
external usenet poster
 
Posts: 123
Default Table Relationships: An Understanding is necessary...

On May 28, 5:48*am, PsyberFox
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
  #4  
Old May 28th, 2010, 03:48 PM posted to microsoft.public.access
PsyberFox
external usenet poster
 
Posts: 88
Default Table Relationships: An Understanding is necessary...

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
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
.

  #5  
Old May 28th, 2010, 08:53 PM posted to microsoft.public.access
Larry Linson
external usenet poster
 
Posts: 3,112
Default Table Relationships: An Understanding is necessary...

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" wrote in message
...
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
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
.



  #6  
Old May 29th, 2010, 10:47 AM posted to microsoft.public.access
PsyberFox
external usenet poster
 
Posts: 88
Default Table Relationships: An Understanding is necessary...

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" wrote in message
...
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
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
.



.

  #7  
Old May 29th, 2010, 12:03 PM posted to microsoft.public.access
PsyberFox
external usenet poster
 
Posts: 88
Default Table Relationships: An Understanding is necessary...

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" wrote in message
...
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
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
.



.

  #8  
Old May 29th, 2010, 12:18 PM posted to microsoft.public.access
PsyberFox
external usenet poster
 
Posts: 88
Default Table Relationships: An Understanding is necessary...

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" wrote in message
...
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
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
.



.

  #9  
Old May 29th, 2010, 10:52 PM posted to microsoft.public.access
Larry Linson
external usenet poster
 
Posts: 3,112
Default Table Relationships: An Understanding is necessary...

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" wrote in message
...
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" wrote in message
...
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
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
.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.