how to display data from Table 1 ("one") in Form for Table 2 ("man

[CAPcoordinator]

Hello all,

I have a DB in which I'm trying to get a Form for Table 2 to display data
from a field (column) in Table 1. The relationship is as in the subject line
above- one to many, table 1 to table 2. I want to display information related
to individual, one-time-only records in table one, that are listed multiple
times in table 2 (hence the 1-many relationship). Does anybody know how to
accomplish this? thank you in advance
-Chris

 

[Graham Mandeno]

Hi Chris

Assuming you already have a one-to-many relationship set up between your two
tables, you should base your form not on Table2, but on a query of Table2
*joined to* Table1. Then, add the required field from Table1 to your query,
along with all the required fields from Table2.

For example, say Table1 is "Customers" and Table2 is "Orders". Your Orders
table contains a field "ordCustID" that relates to the primary key "custID"
in the Customers table. The Customers table also has a field named
"custName" which you want to display on your orders form.

The SQL for your query would look something like this:

SELECT Orders.*, Customers.custName FROM Orders INNER JOIN Customers ON
Orders.ordCust=Customers.custID;

The other way to do it, particularly if you want to select or change the
customer on your orders form, is to bind ordCust to a combo box whose
RowSource is a query based on the Customers table:

SELECT custID, custName FROM Customers ORDER BY custName;

and with the following additional properties for your combo box:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the left-most column - the custID value -
and displays the name)

 

[CAPcoordinator]

Hi Steve,

I tried that and no data was visible in my subform; wondering if there is a
specific aspect to the table relationship I may be failing to address? I've
used subforms in the past and don't understand why it is not working
currently... thanks
-c

 

[CAPcoordinator]

Hi Graham, thanks- very helpful so far. Follow-up question: once I create a
new form out of a query joining two tables, will any data entered into that
form be stored in the respective tables from which the form fields were
originally queried? (i.e. I have created a form that stores inputted data
into two different tables?)

thanks- c

Hi Chris

Assuming you already have a one-to-many relationship set up between your two
tables, you should base your form not on Table2, but on a query of Table2
*joined to* Table1. Then, add the required field from Table1 to your query,
along with all the required fields from Table2.

For example, say Table1 is "Customers" and Table2 is "Orders". Your Orders
table contains a field "ordCustID" that relates to the primary key "custID"
in the Customers table. The Customers table also has a field named
"custName" which you want to display on your orders form.

The SQL for your query would look something like this:

SELECT Orders.*, Customers.custName FROM Orders INNER JOIN Customers ON
Orders.ordCust=Customers.custID;

The other way to do it, particularly if you want to select or change the
customer on your orders form, is to bind ordCust to a combo box whose
RowSource is a query based on the Customers table:

SELECT custID, custName FROM Customers ORDER BY custName;

and with the following additional properties for your combo box:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the left-most column - the custID value -
and displays the name)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello all,

I have a DB in which I'm trying to get a Form for Table 2 to display data
from a field (column) in Table 1. The relationship is as in the subject
line
above- one to many, table 1 to table 2. I want to display information
related
to individual, one-time-only records in table one, that are listed
multiple
times in table 2 (hence the 1-many relationship). Does anybody know how to
accomplish this? thank you in advance
-Chris

 

[Graham Mandeno]

Hi Chris

New records will be added only to the many-side table.

Changed records will alter the data in the table containing the field that
was changed. So, if you changed the custName field from "ABC Inc." to "XYZ
Corp." then that would change the name in the customer record, which is
probably not what is desired. To assign the order to a different customer,
you would need to change the foreign key field (ordCustID in my example) and
the easiest way to do this is with a combo box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham, thanks- very helpful so far. Follow-up question: once I create
a
new form out of a query joining two tables, will any data entered into
that
form be stored in the respective tables from which the form fields were
originally queried? (i.e. I have created a form that stores inputted data
into two different tables?)

thanks- c

Hi Chris

Assuming you already have a one-to-many relationship set up between your
two
tables, you should base your form not on Table2, but on a query of Table2
*joined to* Table1. Then, add the required field from Table1 to your
query,
along with all the required fields from Table2.

For example, say Table1 is "Customers" and Table2 is "Orders". Your
Orders
table contains a field "ordCustID" that relates to the primary key
"custID"
in the Customers table. The Customers table also has a field named
"custName" which you want to display on your orders form.

The SQL for your query would look something like this:

SELECT Orders.*, Customers.custName FROM Orders INNER JOIN Customers ON
Orders.ordCust=Customers.custID;

The other way to do it, particularly if you want to select or change the
customer on your orders form, is to bind ordCust to a combo box whose
RowSource is a query based on the Customers table:

SELECT custID, custName FROM Customers ORDER BY custName;

and with the following additional properties for your combo box:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0 (this hides the left-most column - the custID value -
and displays the name)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


message

Hello all,

I have a DB in which I'm trying to get a Form for Table 2 to display
data
from a field (column) in Table 1. The relationship is as in the subject
line
above- one to many, table 1 to table 2. I want to display information
related
to individual, one-time-only records in table one, that are listed
multiple
times in table 2 (hence the 1-many relationship). Does anybody know how
to
accomplish this? thank you in advance
-Chris