Newbie question

[dave greene]

Hi, I want to create a form in Access 2002 that pulls
selected data from two existing tables and creates a new
table. The user needs to be able to either select a entry
from a table or create a new record to use for the two
existing tables.

I have tried using a couple of combo boxes on a single
form to get the data together but am having trouble
because the tables are not linked by any relationships. I
have looked into subforms a little bit but am having
trouble with getting them to work properly.

Are there any good examples out there or can anyone point
me in the right direction?

Even more simply described, my goal is to design a form
that will allow user to select a single record from (or
create a new record in) two existing tables

Selected table record Column A, B, D +
Selected table record Column A, D, C +
New info +

= New table record to be printed on a pre-printed form.

Many thanks,

David

 

[John Vinson]

Hi, I want to create a form in Access 2002 that pulls
selected data from two existing tables and creates a new
table. The user needs to be able to either select a entry
from a table or create a new record to use for the two
existing tables.

Ummm...

WHY?

Storing data redundantly is neither necessary nor a good idea.

I have tried using a couple of combo boxes on a single
form to get the data together but am having trouble
because the tables are not linked by any relationships. I
have looked into subforms a little bit but am having
trouble with getting them to work properly.

Is there any LOGICAL relationship between the data in your two tables?
What do the tables represent in the real world?

 

[dave]

The first table is a group of construction projects, the
second is a set of members, and the resulting table
matches the two together to fill in a preprinted form
called a notice to owner. The construction projects are
pre-loaded and the members table is pretty static.

Is there an easy way to do this?

David

 

[John Vinson]

The first table is a group of construction projects, the
second is a set of members, and the resulting table
matches the two together to fill in a preprinted form
called a notice to owner. The construction projects are
pre-loaded and the members table is pretty static.

Is there an easy way to do this?

Yes. You have a Many (projects) to Many (members) relationship. The
standard relational database design for such a relationship involves
*a third table* - a "resolver" table, or more properly a table which
has as its defining Entity the relationship between a project and a
person. This table would have fields for the ProjectID (link to the
Project) and for the MemberID (link to the Members table). You'ld add
one record to this table for each member.

The handy way to enter data into such a table is to use a Form based
on the Project table, with a Subform based on the resolver table,
using the ProjectID as the master/child link field. Your Report would
be based on a query joining all three tables (among other methods).