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

 

[Jeff L]

Your relationships are off. For one thing, what is going to happen
when you have multiple products that come from the same supplier? The
way you are trying to do it, you'd have to have the supplier info in
the table for each product they supply. Not gonna happen. You need to
have a foreign key, which acts like a lookup field to another table.
The foreign key for the Products table would be SupplierID, so in your
Products table you need to create a field called SupplierID. Then when
you wish to establish a relationship between the two tables, you would
do it on the SupplierID field from both tables. Make sense?

Hope that helps!

 

[John Vinson]

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.

Basing a table design on a paper form is VERY unlikely to give a
properly normalized database. You really should take a step further
back and identify the *logical* relationships between the data.

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 a given Name can only involve one Product, and a given Product can
be sold to one and only one Name!? Surely not!

That's what I was referring to above: you're getting a non-normalized
design because you're basing it on a sheet of paper, rather than a
logical analysis of the data. Surely each Product can have multiple
SUppliers, and each Supplier can supply multiple Products - you have
*MANY TO MANY* relationships, not a one to one relationship!

Also, by setting the join type to "1" - inner join - you're causing
the exact problem you describe. An Inner Join requires that there must
be data in BOTH tables in the join before it will show you anything.
If you use an Outer Join (2 or 3), you'll be able to see records in
Names even though there is no data in Suppliers (say).

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).

You MUST NOT and CANNOT join two table Autonumber to Autonumber.

Autonumbers are decent Primary Keys but they are *NOT* suitable as a
foreign key. If you add a new record to the Suppliers table, the
autonumber assigned will be the next value available - which will
almost certainly *not* be the value needed to link to the
corresponding record in the Names table.

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?

Stop.

Step back.

NORMALIZE YOUR DATA.

*Then* start thinking about Forms and Reports.

See the resources here for some tutorials on how to do this:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]

 

[shawnews]

ok...so the foreign keys would not be "primary"? and the supplierID (as
foreign key) would not be an autonumber?

 

[John Vinson]

ok...so the foreign keys would not be "primary"? and the supplierID (as
foreign key) would not be an autonumber?

Correct, to both.


John W. Vinson[MVP]