must be a join or relationship problem?

[shawnews]

Ok...I'll first describe briefly what I've done.

Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.

Example tables:

Table Name: Names
Primary Key: NameID - autonumber

Table Name: Products
Primary Key: ProductID - autonumber

TableName: Suppliers
Primary Key: SupplierID - autonumber

The relationships are set as being one-to-one and the join type is set to
"1".

So...to be more clear..what is happening...let's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.

Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.

I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?

I appreciate any help you can provide.

Abe

[Cyberwolf]

Looks to me that you should make at least 1 field in each table
required, that way you can't leave the form without have a new record
in each table.

shawnews wrote:

Ok...I'll first describe briefly what I've done.

Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.

Example tables:

Table Name: Names
Primary Key: NameID - autonumber

Table Name: Products
Primary Key: ProductID - autonumber

TableName: Suppliers
Primary Key: SupplierID - autonumber

The relationships are set as being one-to-one and the join type is set to
"1".

So...to be more clear..what is happening...let's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.

Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.

I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?

I appreciate any help you can provide.

Abe

[Tom van Stiphout]

On 16 Nov 2006 13:00:07 -0800, "Cyberwolf" <cy**********@gmail.com>
wrote:

Wouldn't that depend on the Business Rules? Can't make a field
required if it isn't.

-Tom.

>Looks to me that you should make at least 1 field in each table
required, that way you can't leave the form without have a new record
in each table.

<clip>

[Tom van Stiphout]

On Thu, 16 Nov 2006 19:25:04 GMT, "shawnews" <sa***@kalendars.ca>
wrote:

Join problem.
When you have two tables with a relationship between them (let's call
them Parent and Child), and it is allowed for a Parent to have 0 or 1
Child records, then you'll have to use Outer Joins when creating a
query with these two tables. Create the query, right-click on the
relationship line, and choose "Select All from Parent and ..."

-Tom.

>Ok...I'll first describe briefly what I've done.

Working from a paper form with over 200 fields - broken into 10 areas, I
created a database with 10 tables. I then created a form using those 10
tables, created queries and then reports based on those queries. All works
well until.... you complete a form and DO NOT fill one or more sections (in
other words not filling in an underlying table, then everything falls apart.

Example tables:

Table Name: Names
Primary Key: NameID - autonumber

Table Name: Products
Primary Key: ProductID - autonumber

TableName: Suppliers
Primary Key: SupplierID - autonumber

The relationships are set as being one-to-one and the join type is set to
"1".

So...to be more clear..what is happening...let's say I add a new record,
fill in data for the names section and the products section, BUT NOT the
suppliers section. I look at the tables later and see that there is a new
record (with autonumber) for each the name and the product...but nothing for
the supplier. When I go to query the database I have problems because only
two of the tables are relating (via autonumber). Of course when I add new
records and again leave a section blank..it only compounds the problem.

Let's say after trying a few records I again look at the underlying table.
I see table:Names has 4 records while table: Products has two records and
say TableSuppliers has 3 records. As I said...none of the reports work..and
even when I look at my form..nothing matches up. However, if I go back to
the tables...see that the table with the most records is the products table
with 4 records, I can then go to the other tables and manually add records
until all tables have 4 records. Then the reports and forms work again.

I'm seeing this meaning that this database somehow isn't relating properly
or the join is wrong? I always get confused at this area. Is there a way
to fix this easily? Or...is there a way to force the db to automatically
add a record when a form is open, even if no data is entered?

I appreciate any help you can provide.

Abe