Tables Dependent on Tables? 1

[JOEYB99]

I am using Access 2007, and have inherited a database from a co-worker that is no longer with our company. This particular database works fine, and has for quite awhile now.

I was reviewing and analyzing all the tables and decided to find out the Object Dependencies for each one.

Much to my surprise I discovered that Table A shows that the objects that depend on it are a bunch of queries, and Table B!

When I click on object dependencies for Table B I find out that the converse is true! How is that possible? Can someone please help me to trouble-shoot this and/or explain what is going on?

 

[dhookom]

Check the relationships view. It may be a primary/foreign key relationship.

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thanks for the quick response Duane but there are no table relationships! So, it is not a primary key/foreign key problem.

That was this guy's style. I don't think he ever did any table relationships for any of his databases.

Can you think of anything else it might be?

 

[dhookom]

Did you look to see if the guy used dreaded lookup fields in his table designs?

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

No sorry Duane to disappoint you.

None of the data types for the fields are Look-up. They are either text, auto-number or date.

 

[willir]

Sounds like a one-to-one relationship. Table A queries Table B and Table B queries Table A?

I prefer using the relationship design tool in Access because it enforces referential integrity. However, using a query to relate to another table would work -- Access is fairly easy to use, and this approach may seem logical to someone who is not familiar with proper desgin. This approach is more often used in the form.

Also sounds like a bad or suspect design. I do not use 1:1 relationships much except for security where one table has the "public" information and the second table has the "private" information. For example, HR public info for an "employee" table would have fields for their name, address, title, etc. The HR private info would have fields for current salary, medical note, etc.

Another place to use this would be a manager / employee info where an employee has a manager field pointing to another employee in the same table, but I digress.

If the application still works, then you may want to leave it as it is. Changing the schema when their is a lot of data can be fairly high risk and take a lot of work.

If you decide to design the DB then supplying a little more specific info would help envisiion the problem.

 

[LARiot]

No sorry Duane to disappoint you. None of the data types for the fields are Look-up. They are either text, auto-number or date.

Joey: The text field data type allows for a lookup. When in design mode of the table, go down to the Field Properties and there's a General tab and a Lookup tab. It might be in there.

-Nima
"Pluralitas non est ponenda sine necessitate", i.e., "Plurality is not to be posited without necessity" aka K.I.S.S. (Keep It Short and Simple) -- Ockham's Razor