MS Access Forms problem

[DataB]

Hi everyone!

I have a forms problem.

Bakground:
I have created a number of tables. Of these, I have a main parent
table (Personal Details) and a number of other child tables (Tax file
No., VISA card no.), etc......

All tables have a primary key of "Name", all child tables forming
foreign key references with the parent table 'Personal Details'. I
have established referential integrity will all relationships. All
relationships are 1-to-1. To solve my problem, I have tried making the
relationships 1-to-many (ie., the 2nd option in the "Link Type" option
of the Relationships window), but it has not worked.

Here's my problem:
I have created a single form with all of these tables' columns in the
form, however, for all of the "Name" fields of all the tables, the
parent table filed is the only one on the form. When the form opens,
it searches the tables for all 1-to-1 relationships (ie. sort of like
an Inner Join of all the tables).

However, if you add, via the form, data for a new record, and for
example, you only populate fileds to fill the parent table "Personal
Details", when the form is closed and re-openned, the new record
information is not returned. It is captured in the parent table, but
as the data for the child tables has not been provided, it doesn't get
pulled on the Inner Join.

Is there a way, such that, I can pull back results in this form of a
LEFT OUTER JOIN nature? - ie., I will pull all rows in the 'Personal
Details' table, regardless of whether a particular row has matching
rows in the child tables........

Any help is VERY much appreciated! This one is giving me
hebe-jebees...

Cheers,
A

[Jeff Smith]

First of all you need to have a major look at your table structure. 'Name'
is a reserved word in Access and may cause problems later on so it shouldn't
be used as the name of a field, especially a primary key. Using somebody's
name as a primary key is also a no no, what if you get 2 John Doe's or 2
Mary May's. Are you entering both the first and last names in this field or
just the first name.

A primary key is used to make each record unique, that means no duplicates,
so a number is the easiest option to use as a primary key but not always the
best option. The user doesn't need to see the primary key.

For a 1-to-1 relationship the main table needs to have a unique identifier
(primary key) and each linked table have their primary keys with the same
format: (tblMain: PersonID (PK), FName, LName) (tblChild1: PersonID (PK),
Field1, Field2) (tblChild2: PersonID (PK), Field1, Field2) and each PersonID
linked.

For a 1-to-M relationship the main table should have a primary key and each
child table has its own primary key and a separate foreign key usually with
the same name as the primary key of the main table. The main table's primary
key is then linked to the foreign key of the child tables: (tblMain:
PersonID (PK), FName, LName) (tblChild1: ChildID (PK), PersonID (FK),
Field1, Field2).

An Autonumber is the easiest to use for a numeric type primary key but if
you want to use a number other than an Autonumber you'll have to write your
own function to insert the next available number into your record.

With your problem.
How have you created the recordsource for the form? You need to select the
main table and all the child tables and use the join type of "Use all
records from your main table and only those records from your child
tables....". This equates to a LEFT JOIN.

Jeff

"DataB" <ab******@hotma il.com> wrote in message
news:6c******** *************** ***@posting.goo gle.com...

Hi everyone!

I have a forms problem.

Bakground:
I have created a number of tables. Of these, I have a main parent
table (Personal Details) and a number of other child tables (Tax file
No., VISA card no.), etc......

All tables have a primary key of "Name", all child tables forming
foreign key references with the parent table 'Personal Details'. I
have established referential integrity will all relationships. All
relationships are 1-to-1. To solve my problem, I have tried making the
relationships 1-to-many (ie., the 2nd option in the "Link Type" option
of the Relationships window), but it has not worked.

Here's my problem:
I have created a single form with all of these tables' columns in the
form, however, for all of the "Name" fields of all the tables, the
parent table filed is the only one on the form. When the form opens,
it searches the tables for all 1-to-1 relationships (ie. sort of like
an Inner Join of all the tables).

However, if you add, via the form, data for a new record, and for
example, you only populate fileds to fill the parent table "Personal
Details", when the form is closed and re-openned, the new record
information is not returned. It is captured in the parent table, but
as the data for the child tables has not been provided, it doesn't get
pulled on the Inner Join.

Is there a way, such that, I can pull back results in this form of a
LEFT OUTER JOIN nature? - ie., I will pull all rows in the 'Personal
Details' table, regardless of whether a particular row has matching
rows in the child tables........

Any help is VERY much appreciated! This one is giving me
hebe-jebees...

Cheers,
A

[DataB]

Thank you so much Jeff.......

RECORDSOURCE!!! !!! ARRGHHHH! I couldn't find the properties sheet for
the form itself.... it was sending me bonkers........ ..... replacing
the INNER joins with LEFT OUTER joins worked well.........

With respect to the table design......... .. I know, I didn't design
it. One of the first things you learn in Database Systems 1 at Uni is
that a numeric is the best choice for a primary key.....I am just
doing some work on this pig of a thing......... took me a while to
establish normalisation, thus, I am not going to try and convince my
client to change their db structure as well as that would require way
too much impact analysis on downstream systems!

Thanks a lot Jeff,

A