Our main order records are held in the Inquiries table and Ref is the primary
key.
We issue questionnaires to customers and log these on a form based on a
Questionnaires table.
However the Questionnaires table does not contain all the Refs
automatically, so when we try to enter data on the questionnaire form we get
errors.
At present I have a no. 2 type join between Ref in Inquires and Ref in
Questionnaires, which I thought would ensure that ALL records from Inquiries
would be found, but it seems that doesn't work.
How can I ensure that for every Ref in the Inquiries table, a Ref will
automatically exist in the Questionnaires table?
Many thanks
CW

I can see no reason why a row needs to pre-exist in the Questionnaires table.
The Questionnaires table presumably includes a Ref column as a foreign key
referencing the primary key of Inquiries (if the relationship is one-to-one,
i.e. there is only one Questionnaire row for each row in Inquiries, then its
both a primary key and a foreign key of Questionnaires of course). To
complete a questionnaire in relation to a particular ref, therefore, you
simply need to insert a new row into Questionnaires via a form bound to that
table, with a control bound to the ref Column. This control would usually
be a combo box in which you would select a ref from a list, or possibly
select a value from another uniquely valued column in Enquiries if ref is an
arbitrary value whose actual value is irrelevant to the user.

For the first scenario the RowSource of the combo box would be:

SELECT Ref FROM Inquiries ORDER BY Ref;

For the second scenario you'd need to set the combo box up differently, to
hide the bound ref column and show whatever column is meaningful to the user,
as follows:

ControlSource: Ref

RowSource: SELECT Ref, TheColumnYouWantToSee FROM Inquiries ORDER BY
TheColumnYouWantToSee;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

An alternative method of data entry would be to have a form based on
Inquiries and within it a subform based on Questionnaires, the parent form
and subform being linked on the Ref columns by virtue of these being the
LinkMasterFields and LinkChildFields properties of the subform control. That
way the correct Ref value would automatically be entered into a new row in
Questionnaires when its inserted via the subform. To avoid undue clutter in
the form one approach would be to have the Inquiries data on one page of a
tab control and the Questionnaires subform on another. The user can then tab
between them. Controls bound to the principal columns from inquiries,
including Ref, would normally be placed in the main parent form above the tab
control so that these are visible whatever page of the tab control is
selected; the user can then see what Inquiry record the form is at when they
have selected the Questionnaires tab.

Where a query which LEFT OUTER JOINs Inquiries to Questionnaires (which is
what the 2nd join type option is) would come into play would be if you wanted
to return rows from Inquiries along with data from Questionnaires where
matches exist, but including the Inquiries data even where there is no match
in Questionnaires, in which case the columns from Questionnaires would be
Null for the unmatched rows. This is more likely to be as the RecordSource
for a report rather than a form for data entry.

One thing to be aware of with this type of query is that you can only
restrict it on columns on the left side of the join, i.e. you can impose
criteria on columns from Inquiries, but not on columns from Questionnaires.
To do the latter you'd have to adopt a different approach, using a subquery,
but that's not something we need to go into in at present.

Ken Sheridan
Stafford, England

"CW" wrote:

> Our main order records are held in the Inquiries table and Ref is the primary
> key.
> We issue questionnaires to customers and log these on a form based on a
> Questionnaires table.
> However the Questionnaires table does not contain all the Refs
> automatically, so when we try to enter data on the questionnaire form we get
> errors.
> At present I have a no. 2 type join between Ref in Inquires and Ref in
> Questionnaires, which I thought would ensure that ALL records from Inquiries
> would be found, but it seems that doesn't work.
> How can I ensure that for every Ref in the Inquiries table, a Ref will
> automatically exist in the Questionnaires table?
> Many thanks
> CW

Ken -
Sorry not to have acknowledged until now - been off all week. Many thanks
for your very detailed and clear response, much appreciated
CW

"Ken Sheridan" wrote:

> I can see no reason why a row needs to pre-exist in the Questionnaires table.
> The Questionnaires table presumably includes a Ref column as a foreign key
> referencing the primary key of Inquiries (if the relationship is one-to-one,
> i.e. there is only one Questionnaire row for each row in Inquiries, then its
> both a primary key and a foreign key of Questionnaires of course). To
> complete a questionnaire in relation to a particular ref, therefore, you
> simply need to insert a new row into Questionnaires via a form bound to that
> table, with a control bound to the ref Column. This control would usually
> be a combo box in which you would select a ref from a list, or possibly
> select a value from another uniquely valued column in Enquiries if ref is an
> arbitrary value whose actual value is irrelevant to the user.
>
> For the first scenario the RowSource of the combo box would be:
>
> SELECT Ref FROM Inquiries ORDER BY Ref;
>
> For the second scenario you'd need to set the combo box up differently, to
> hide the bound ref column and show whatever column is meaningful to the user,
> as follows:
>
> ControlSource: Ref
>
> RowSource: SELECT Ref, TheColumnYouWantToSee FROM Inquiries ORDER BY
> TheColumnYouWantToSee;
>
> BoundColum: 1
> ColumnCount: 2
> ColumnWidths: 0cm;8cm
>
> If your units of measurement are imperial rather than metric Access will
> automatically convert the last one. The important thing is that the first
> dimension is zero to hide the first column and that the second is at least as
> wide as the combo box.
>
> An alternative method of data entry would be to have a form based on
> Inquiries and within it a subform based on Questionnaires, the parent form
> and subform being linked on the Ref columns by virtue of these being the
> LinkMasterFields and LinkChildFields properties of the subform control. That
> way the correct Ref value would automatically be entered into a new row in
> Questionnaires when its inserted via the subform. To avoid undue clutter in
> the form one approach would be to have the Inquiries data on one page of a
> tab control and the Questionnaires subform on another. The user can then tab
> between them. Controls bound to the principal columns from inquiries,
> including Ref, would normally be placed in the main parent form above the tab
> control so that these are visible whatever page of the tab control is
> selected; the user can then see what Inquiry record the form is at when they
> have selected the Questionnaires tab.
>
> Where a query which LEFT OUTER JOINs Inquiries to Questionnaires (which is
> what the 2nd join type option is) would come into play would be if you wanted
> to return rows from Inquiries along with data from Questionnaires where
> matches exist, but including the Inquiries data even where there is no match
> in Questionnaires, in which case the columns from Questionnaires would be
> Null for the unmatched rows. This is more likely to be as the RecordSource
> for a report rather than a form for data entry.
>
> One thing to be aware of with this type of query is that you can only
> restrict it on columns on the left side of the join, i.e. you can impose
> criteria on columns from Inquiries, but not on columns from Questionnaires.
> To do the latter you'd have to adopt a different approach, using a subquery,
> but that's not something we need to go into in at present.
>
> Ken Sheridan
> Stafford, England
>
> "CW" wrote:
>
> > Our main order records are held in the Inquiries table and Ref is the primary
> > key.
> > We issue questionnaires to customers and log these on a form based on a
> > Questionnaires table.
> > However the Questionnaires table does not contain all the Refs
> > automatically, so when we try to enter data on the questionnaire form we get
> > errors.
> > At present I have a no. 2 type join between Ref in Inquires and Ref in
> > Questionnaires, which I thought would ensure that ALL records from Inquiries
> > would be found, but it seems that doesn't work.
> > How can I ensure that for every Ref in the Inquiries table, a Ref will
> > automatically exist in the Questionnaires table?
> > Many thanks
> > CW
>