Access Relationships, etc

laura

I am new to Access, trying to figure it out on my own.

I am trying to create a form to input the same information in 3
different tables. The 3 tables each have a different number as the
primary key, although they each do contain the other 2 numbers in
their tables.

I have been unsuccessful and am wondering if perhaps my relationships
are inaccurate.

Do I need a many to many relationship or a one to one or do I need to
do something else?

Also, I tried to create a form from a select query to use for the 3
table input but the form is blank in the form view and empty in the
datasheet view (new records can not be inserted). Clicking on
the ...build doesn't seem to have any effect.

Aug 17 '07 #1

Rich P

For your situation I will guess that the problem is probably in the
Query Joins. Try creating a query with only one table and add that to
your form. If the form opens correctly, then try joining a table to the
query and see if the form still works. If not, then you need to modify
your join.

Access is a Desktop / File based Relational Database system (RDBMS for
workstation computers). Access can perform several of the operations
that a server based RDBMS (like Oracle or MS Sql Server) can perform
with the main limitation that Access can only hold 1 gig of useable data
(2gigs if you count unicode - but that extra gig is not useable data) --
where a server based RDBMS can hold at least 1 terabyte of data. So
Access is basically a mini RDBMS.

So -- relationships in Access function the same as they do in a server
based RDBMS -- which is to control referential Integrity -- controlling
the integrity of data references between tables. Relationships serve to
reduce duplicate data entry or incorrect data entry as follows:

You have a master table that contains client information --ClientID,
Name, address, phone, ... Then you have a detail table. The
relationship would be master:one detail:many. You relate the tables by
ClientID. The restriction is that you cannot enter data into the detail
data unless the new record you are entering contains a valid clientID --
that would be a ClientID which is first entered into/contained by the
Master table.

Then, say you also have a Products table which lists 10 different
products. You relate the Products table to the Detail table as
products:one Detail:many.

You can only enter a product name in the Detail table if that Product
Name is contained in the Products table.

Relationships are not part of a query join. Query Joins deal with data
that is already contained in your tables. Relationships server to
restrict what data can be entered or removed from the tables.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Aug 17 '07 #2