Simplest way to locate & remove unused values in a look-up table

  • Thread starter Thread starter Mikey C
  • Start date Start date
M

Mikey C

Hey folks!

I have amended the design of my database which has led to a whole load
of redundant values in one of my tables. Could anybody tell me the
simplest way to locate and delete only the values which are not being
used?

The table containing the redundant data is used to provide values for a
look-up field. I would like to shorten the list by removing these
obsolete values without risking damage to my other records.

Any ideas??
 
Use the Find Unmatched Query Wizard to create a query that returns a list of
those entries in the lookup table that aren't used in the other table.
 
Where do I find the unmatched query wizard (if it's installed)? I think
I may have to get the IT dept to install it for me because the help
section says that it is an optional installation.

Thanks Douglas :-)
 
Mikey C said:
Hey folks!

I have amended the design of my database which has led to a whole load
of redundant values in one of my tables. Could anybody tell me the
simplest way to locate and delete only the values which are not being
used?

The table containing the redundant data is used to provide values for a
look-up field. I would like to shorten the list by removing these
obsolete values without risking damage to my other records.

Any ideas??
Click to expand...

If you enforced referential integrity, then you can actually run a delete
query on that table, and any values that are not used will be deleted....

For a considerable amount of time, for many of my pick lists, or combo drop
down lists, I not always enforced RI. The reason is that if you delete main
records in the main tables, that pick list is not modified. Further, if you
add new values to the pick list, then again, the main tables are not
modified. (eg: no cascade deletes or updates need ever happen).

It is a good idea to conceptually distinguish between tables used for
pick-list, and that of actually enforced relations (eg: you delete a
customer, then that means the customer invoices need be deleted. Further, if
you add a invoice, you MUST have a customer exist before you do this).

However, with a pick list..you often add many new records to it, and they
are NOT yet in use by some table.

So, in the above cases, drawing a join line in the relationship windows ONLY
served to document the database, but nothing really happened when you added,
or delete records in either table. However, the EXCEPTION IS YOUR example,
and if you enforce RI, then when you try to delete a record in the pick list
table, you will NOT be allowed to do this if there is any record in the main
table that uses the value from the pick list.

So, to reduce that risk, simply enforce RI for those pick list tables. Once
you done this..you can simply try to delete all records, and ones not in use
will be the only recodes that you can delete. Of course, while you do
enforce RI, you can't use cascade deletes for these tables.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form question: Simplest way to bring in a query result that is notthe record source into a form? 3
simplest way...? 13
Looking up a looked up look up value! 1
Look up last value in field 5
Remove a Look-up 2
If an option is not available in a look-up table, I want to enter 6
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Making a query that creates a value base on a table value 8

Back
Top