Access to Multiple Tables from one Form

C

C Brandt

Hi Guys:

A real live beginner here, as will be obvious from my question.
Traditionally I have used Excel to keep track of my data, but decided to
give Access a try.

Question: I have created a form, using wizard that accesses data from two
different tables. I now need to add a "Text Box" that will display
information from a third table. How do I get the form to understand the
location of the data? When I try using the Expression Builder and select the
folder of tables then the table itself with the appropriate column, I get a
#Name? ERROR in Form View.

Is there a beginner document on line that could help with this low level
type of question? I went out and purchased Microsoft Access Step-by-Step,
but could not locate the desired information.

Regrads,

Craig
 
R

Rick B

Are these tables one-to-one relationships? Please explain how the tables
are related, what data is in each one, etc.
 
C

C Brandt

Rick:

Not quite 1:1.
Three Tables: ACCOUNT, CLIENT and STRATEGY
ACCOUNT table has multile columns. The Primary Key is a column labeled
SNAME, for Short Name. There is also a column labeled CLIENT.
STRATEGY has a column labeled SNAME and is the Primary Key for this table.
ACCOUNT and STRATEGY is a 1:1 relationship with SNAME linking the
records in the two tables.
The CLIENT table, also multiple columns, with the Primary Key being a
CLIENT.
Strategy is linked to Account via the CLIENT column. There will
be multiple ACCOUNT records pointing to a single CLIENT record.

Prior to setting up the form, I went to the relationship tool and linked the
CLIENT Column in the ACCOUNT Table to the CLIENT column in the CLIENT Table
and the SNAME column in the ACCOUNT table to the SNAME in the STRATEGY
table.

When I first created the form, I pulled data from the ACCOUNT and STRATEGY
tables. To better define the client, I wanted to add more information
(Client Name(CLIENT))to the form from the CLIENT table.
I would have thought the process was to open an "unbound" test box in the
form and in properties, set the Control Source to CLIENT(Client Name). I
clicked on control source, then clicked on the button that brought up
Expression Builder. I clicked on the Table Folder, then the CLIENT folder.
This gave me a choice of the columns in the CLIENT table, one of which was
the CLIENT Column. I double clicked on the client column and it built the
expression : =[CLIENT]![CLIENT] which I fully expected was the CLIENT column
in the CLIENT Table.
On my first go around I was getting a #NAME? error and discovered that I had
miss-spelled my test data. I corrected that and now I get a #ERROR in the
text box.

I am using Access 2000 in Windows 2000.

Thanks for any help.

Craig
 
Top