multiple tables in one query

[Cheryl]

I have a db that contains multiple forms that the user
fills out and prints as reports to submit as
applications. I just received a new application which
contains data from the Main Table, two other existing
tables (TableA & TableB) and new data (I created TableC).

Each table has it's own unique index. The Main Table is
RecNo. and TableA has A_ID etc. Each table also includes
a RecNo field to relate it to the Main Table. Tables A,B
& C may contain duplicate RecNo's.

The new application contains the data from all three
tables in no particular order (Field 1 may come from
TableA, Field 2 will come from TableC) so I couldn't just
create three subforms.

I created a select query (Sask_Query) that contains all
the necessary fields from Tables A,B & C. I included the
RecNo. from TableC. I created my form for the main table
with the master/child link being RecNo. from the Main
Table and RecNo. from TableC. Of course, this doesn't
work. It ends up recording the correct data in the Main
Table and TableC but the data from Tables A & B disappears
from the screen. If I go in and check the tables there
are records there that contain the data but have no RecNo.

I tried adding all the RecNo.'s in to the query and form
but, then I get an error message that "RecNo. could refer
to more than one From table of my SQL statement." I
really don't know SQL very well so, I am wondering if
there is something in my query or my relationships that I
could change to make this work.

Thanks a lot for your help.

 

[Ragnar Midtskogen]

Hello Cheryl,

Usually when you want to show data from more than one table you have a key
field in the two tables that allow them to be joined in a meaningful way.
For example, a store may have a database containing customer and order data.
The customer table should have a customer id field. The orders table should
have an order id field and a customer id field.
Then you can create a query that joins the two tables on the customer id
field that will show the orders for each customer.
If you do not have any fields to join the tables on, then the only way to
show data from all tables is a union query, there the only requirement is
that the columns selected from each table is the same and that the fields
for each column has the same data type.
You should read something about database design, in particular how to
organize the data, it is not intuitive, but it is of critical importance.

Ragnar

 

[John Vinson]

I have a db that contains multiple forms that the user
fills out and prints as reports to submit as
applications. I just received a new application which
contains data from the Main Table, two other existing
tables (TableA & TableB) and new data (I created TableC).

Some concern here:

Forms are designed for *onscreen* data entry, viewing and editing.
Reports are the appropriate tool for printing. If by "form" you mean a
sheet of paper to be submitted as an application, you should use an
Access Report to produce that paper.

Each table has it's own unique index. The Main Table is
RecNo. and TableA has A_ID etc. Each table also includes
a RecNo field to relate it to the Main Table. Tables A,B
& C may contain duplicate RecNo's.

How are the tables related? If there is information in the Main Table
record with RecNo = 123, how do you determine which record or records
in TableA, TableB and TableC are associated with that record? In what
way are they associated?

I fear you've made the very common mistake of designing the Tables to
fit the desired printout, rather than vice versa! The Tables should be
set up based on the logical relationships of the data; if this is set
up correctly, it will be pretty easy to set up a Form or Report. If
they are not... you're in trouble.

The new application contains the data from all three
tables in no particular order (Field 1 may come from
TableA, Field 2 will come from TableC) so I couldn't just
create three subforms.

I created a select query (Sask_Query) that contains all
the necessary fields from Tables A,B & C. I included the
RecNo. from TableC. I created my form for the main table
with the master/child link being RecNo. from the Main
Table and RecNo. from TableC.

I think you probably want to create a Report - not a form! - based on
Sask_Query. Perhaps you could open Sask_Query in SQL view and post the
SQL text here if it's not working the way you expect.