INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tables Dependent on Tables?

Tables Dependent on Tables?

(OP)

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?

RE: Tables Dependent on Tables?

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

Duane
Hook'D on Access
MS Access MVP

RE: Tables Dependent on Tables?

(OP)

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?

RE: Tables Dependent on Tables?

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

Duane
Hook'D on Access
MS Access MVP

RE: Tables Dependent on Tables?

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

RE: Tables Dependent on Tables?

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.

RE: Tables Dependent on Tables?

Quote (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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close