Prev: My last post in this forum
Next: Which forum or website are the regulars switching to?
From: PsyberFox on 28 May 2010 07:48
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
From: Tom van Stiphout on 28 May 2010 09:37
On Fri, 28 May 2010 04:48:01 -0700, PsyberFox
<PsyberFox(a)discussions.microsoft.com> 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
From: Keven Denen on 28 May 2010 09:41
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 28 May 2010 10:48
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 28 May 2010 15:53
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
>> .
>>


 |  Next  |  Last
Pages: 1 2
Prev: My last post in this forum
Next: Which forum or website are the regulars switching to?