I have 3 tables: "Beads" containing a consistent 30 records,
"Samples"(Field:Sample Date) and "Results" (Field:Result). Beads has a
one to many relationship to Results, as does Samples. So Results is
effectively a linked table from the many-to-many relationship between
Samples and Beads.

In any case, I need a data entry form based on the sample with a
datasheet subform for Beads and Results. But I would like the Bead/
Result datasheet to show all 30 records with a blank spot to fill in
the results, rather than picking each of the 30 beads from a combobox
and entering the result. Is this even possible?

Thanks in a advance

Dave

On Sat, 26 Nov 2011 09:45:33 -0800 (PST), Dave <(E-Mail Removed)> wrote:

>I have 3 tables: "Beads" containing a consistent 30 records,
>"Samples"(Field:Sample Date) and "Results" (Field:Result). Beads has a
>one to many relationship to Results, as does Samples. So Results is
>effectively a linked table from the many-to-many relationship between
>Samples and Beads.
>
>In any case, I need a data entry form based on the sample with a
>datasheet subform for Beads and Results. But I would like the Bead/
>Result datasheet to show all 30 records with a blank spot to fill in
>the results, rather than picking each of the 30 beads from a combobox
>and entering the result. Is this even possible?
>
>Thanks in a advance
>
>Dave

Try creating a query joining Beads to Results, but use a Left Outer Join:
select the join line in the query grid and choose the option "Show all rows in
Beads and matching rows in Result". This will show all 30 rows, whether or not
there is a matching result, and it will be updateable.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Nov 26, 6:23*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 26 Nov 2011 09:45:33 -0800 (PST), Dave <Dave.Bea...@lhsc.on.ca> wrote:
> >I have 3 tables: "Beads" containing a consistent 30 records,
> >"Samples"(Field:Sample Date) and "Results" (Field:Result). Beads has a
> >one to many relationship to Results, as does Samples. So Results is
> >effectively a linked table from the many-to-many relationship between
> >Samples and Beads.
>
> >In any case, I need a data entry form based on the sample with a
> >datasheet subform for Beads and Results. But I would like the Bead/
> >Result datasheet to show all 30 records with a blank spot to fill in
> >the results, rather than picking each of the 30 beads from a combobox
> >and entering the result. Is this even possible?
>
> >Thanks in a advance
>
> >Dave
>
> Try creating a query joining Beads to Results, but use a Left Outer Join:
> select the join line in the query grid and choose the option "Show all rows in
> Beads and matching rows in Result". This will show all 30 rows, whether or not
> there is a matching result, and it will be updateable.
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com- Hide quoted text -
>
> - Show quoted text -

Thanks John,

I get the query, and it works. How then do I build the form though? If
I have the Sample on the main form and the Beads/Results as subform
the beads don't show up. Propably because of the master/child linkage.

Dave

On Sat, 26 Nov 2011 15:46:38 -0800 (PST), Dave <(E-Mail Removed)> wrote:

>Thanks John,
>
>I get the query, and it works. How then do I build the form though? If
>I have the Sample on the main form and the Beads/Results as subform
>the beads don't show up. Propably because of the master/child linkage.
>
>Dave

Well? What IS the linkage? What are the Recordsources of the main and subform?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Nov 26, 7:52*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 26 Nov 2011 15:46:38 -0800 (PST), Dave <Dave.Bea...@lhsc.on.ca> wrote:
> >Thanks John,
>
> >I get the query, and it works. How then do I build the form though? If
> >I have the Sample on the main form and the Beads/Results as subform
> >the beads don't show up. Propably because of the master/child linkage.
>
> >Dave
>
> Well? What IS the linkage? What are the Recordsources of the main and subform?
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com

Let me try to clarify things here.

I'll have 3 tables

tblBeads with PK and Beads field which includes 20 bead entries

tblSample with PK and Sample Date field

TblBeadSample as a join for the tblBeads and tblSample many-to-many
relationship with Beads FK, Sample FK and Result

The form, I imagine in my head, has a text box to input a new Sample
Date and a subform that will populate with all 30 beads awaiting the
"Result" for each bead. Like:

Bead Result
Bead 1 _______
Bead 2 _______
Bead 3 _______
....
Bead20 _______

Each sample will always have the 20 beads.

Thanks again,

Dave

On Sat, 26 Nov 2011 17:32:50 -0800 (PST), Dave <(E-Mail Removed)> wrote:


>tblBeads with PK and Beads field which includes 20 bead entries
>
>tblSample with PK and Sample Date field
>
>TblBeadSample as a join for the tblBeads and tblSample many-to-many
>relationship with Beads FK, Sample FK and Result
>
>The form, I imagine in my head, has a text box to input a new Sample
>Date and a subform that will populate with all 30 beads awaiting the
>"Result" for each bead. Like:
>
>Bead Result
>Bead 1 _______
>Bead 2 _______
>Bead 3 _______
>...
>Bead20 _______
>
>Each sample will always have the 20 beads.
>
>Thanks again,
>
>Dave

Don't use an autonumber for the Bead ID if the numbers are meaningful, you
can't edit or control its value. If the Beads field is a unique, stable,
numeric ID, just use it directly as the PK.

You could have a Form based on Samples, with a Subform based on a query

SELECT tblBeads.Beads, tblBeadSample.Beads, tblBeadSample.SampleID,
tblBeadSample.Result
FROM tblBeads LEFT JOIN tblBeadSample
ON tblBeads.Beads = tblBeadSample.Beads;

As soon as you "dirty" the subform record by adding a value in Result it will
automatically fill in the bead number.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

You can display tblBeads.Beads and tblResult.Result

On Nov 26, 8:58*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 26 Nov 2011 17:32:50 -0800 (PST), Dave <Dave.Bea...@lhsc.on.ca> wrote:
> >tblBeads with PK and Beads field which includes 20 bead entries
>
> >tblSample with PK and Sample Date field
>
> >TblBeadSample as a join for the tblBeads and tblSample many-to-many
> >relationship with Beads FK, Sample FK and Result
>
> >The form, I imagine in my head, has a text box to input a new Sample
> >Date and a subform that will populate with all 30 beads awaiting the
> >"Result" for each bead. Like:
>
> >Bead * * * * * * * * * *Result
> >Bead 1 * * * * * * * * _______
> >Bead 2 * * * * * * * * _______
> >Bead 3 * * * * * * * * _______
> >...
> >Bead20 * * * * * * * *_______
>
> >Each sample will always have the 20 beads.
>
> >Thanks again,
>
> >Dave
>
> Don't use an autonumber for the Bead ID if the numbers are meaningful, you
> can't edit or control its value. If the Beads field is a unique, stable,
> numeric ID, just use it directly as the PK.
>
> You could have a Form based on Samples, with a Subform based on a query
>
> SELECT tblBeads.Beads, tblBeadSample.Beads, tblBeadSample.SampleID,
> tblBeadSample.Result
> FROM tblBeads LEFT JOIN tblBeadSample
> ON tblBeads.Beads = tblBeadSample.Beads;
>
> As soon as you "dirty" the subform record by adding a value in Result it will
> automatically fill in the bead number.
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com
>
> You can display tblBeads.Beads and tblResult.Result- Hide quoted text -
>
> - Show quoted text -

Except that all 20 beads need to be displayed on the subform to match
up the result with the bead.

Thanks John for taking the time to help.

Dave

On Sat, 26 Nov 2011 18:45:40 -0800 (PST), Dave <(E-Mail Removed)> wrote:

>Except that all 20 beads need to be displayed on the subform to match
>up the result with the bead.

That's exactly what the query I posted should do. Doesn't it?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Nov 26, 10:28*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 26 Nov 2011 18:45:40 -0800 (PST), Dave <Dave.Bea...@lhsc.on.ca> wrote:
> >Except that all 20 beads need to be displayed on the subform to match
> >up the result with the bead.
>
> That's exactly what the query I posted should do. Doesn't it?
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com

No, it doesn't. Unless I'm doing something wrong, which wouldn't
surprise me.
The subform has only one blank row. When I go into the subform it will
automatically populate the sample ID but no beads are displayed.

Dave

On Sat, 26 Nov 2011 19:57:26 -0800 (PST), Dave <(E-Mail Removed)> wrote:

>On Nov 26, 10:28*pm, John W. Vinson
><jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>> On Sat, 26 Nov 2011 18:45:40 -0800 (PST), Dave <Dave.Bea...@lhsc.on.ca> wrote:
>> >Except that all 20 beads need to be displayed on the subform to match
>> >up the result with the bead.
>>
>> That's exactly what the query I posted should do. Doesn't it?
>> --
>>
>> * * * * * * *John W. Vinson [MVP]
>> *Microsoft's replacements for these newsgroups:
>> *http://social.msdn.microsoft.com/For...-US/accessdev/
>> *http://social.answers.microsoft.com/.../en-US/addbuz/
>> *and see alsohttp://www.utteraccess.com
>
>No, it doesn't. Unless I'm doing something wrong, which wouldn't
>surprise me.
>The subform has only one blank row. When I go into the subform it will
>automatically populate the sample ID but no beads are displayed.
>
>Dave

Hrm. I'll try some experiments tomorrow. I would have expected it to work...

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com