Efficient Db Design Question

[Pam]

I am looking for ideas/suggestions on the most efficient way to set up a
database to manage scanned documents.

I've worked with Access for quite a few years now and am constantly learning
new techniques thanks to all those who dedicate their time at this site. I
already have a database in place that will allow a search on several
different fields thanks to Mr. Allen Browne. The problem I am now faced
with is when a company changes their name. What would be the best way to
add to/change the existing db or create new db so that users could search by
customer name whether it be current name or previous name? I've thought of
two separate fields - current name and previous name, but the problem is
that some have changed names many times over the past 30 years.

If anyone has any suggestions, please let me know.
Thanks in advance for your help.
Pam

 

[Roger Carlson]

Well, if each company can have one or more names, then you need a separate
table to hold those names. However, then you have the issue of deciding
which name is the name they used at the time their data was stored. You
might need to add a begin date and end date for each of the company names so
you could match that to the date of the record (assuming you stored a date).

There may be other solutions, depending on how important the previous names
are. For instance, if you ONLY wanted the previous names for look up
purposes, but you wanted all reports (even history) to show the current
name, then you wouldn't need to bother with storing the dates. Perhaps a
yes/no field for the current company name would suffice (in the CompanyName
table).

There is no single correct answer. A lot depends on your business rules.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jerry Whittle]

That would require a new table just for the company names. That table would
include a foriegn key to the main table. You probably would want a yes/no for
CurrentName so that you can find which one to use plus maybe beginning and
end date fields to know what the name was when.

In other words, the company name field would not be in the main table. Other
questions to ponder is what to do when a company splits up into multiple
companies and what to do about address changes if you need to keep the
address.

 

[Pam]

Thank you, Roger and Jerry, for the information provided. You've both given
me some ideas to think about. I believe I do need a date field just to give
a point in time when all the info was entered to system. I'm thinking maybe
a custname field in main table with previousname field in separate table.
Then on the search form, if there is some way to search by both fields
together it would give a complete list. Would a query with a one-to-many
link (with main table being one and previousname table being the many) work?

If you have any ideas to my thoughts above, I would greatly appreciate
hearing them.
Again, thanks for your time and help.
Pam

 

[Steve]

How about a TblCompany that contains all info but the company name and a
TblCompanyOrigNewName. The tables would be joined by CompanyID. All the
company names ever used would be in one table so any searches by company
name would be done through one table.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)