Using Excel as an RDMS

[scott6840]

I have reviewed the tutorial regarding Excel RDBMS usage that is on the tushar-mehta.com web site and can follow the logic fairly well. While I am not a novice with regards to a RDBMS, I am very much a novice when it comes to Excel.

I follow the tutorial with regards to the MS Query routines with respect to generating reports and/or queries from worksheets that have been linked but cannot quite follow how you set up an Excel workbook that maintains the referential integrity of the RDBMS when you are actually entering the data.

Please bear with me as this may seem a bit macabre but it is the real world problem I'm facing: We are trying to convert the paper records of a small not-for-profit cemetery to an Excel spreadsheet(s) prior to moving all the data to a commercial software system. The cemetery has been in existence since 1952 and, while the records are fairly good, I'm sure there are mistakes.

Please bear with me while I try to elaborate on this: The cemetery divided into numerous lots and each lot is divided into anywhere from one to six individual burial plots. Purchasers are given a single certificate (deed) that covers however many burial plots they have requested. A certificate may span more than one lot.

The deed is the controlling document and each deed has a "one to many" relationship to the lots and each lot has a "one to many" relationship to the burial plots it encompasses.

Obviously, it would be a major problem if the same burial plot were conveyed to more than one person. As this may have already occurred (It can be decades between the purchase and the actual use of the plots), it is absolutely vital that referential integrity be maintained during the building of the table(s).

After all that, the question is: Can Excel be used to accomplish the above, and, if so, how would I set up the tables so that any attempt to assign a plot more than once would be flagged?

Thanks for bearing with me. I appreciate your interest.

 

[RonB1111]

Here are some thoughts for you to consider:

1. Why use an intemediary step - why not load the data directly into the comercial software package which probably has built in referential integrity?

2. If multiple related tables are required, Microsoft Access or similar database systems might be a better solution than Excel.

3. For Excel, you could set up a single worksheet(table) where Col A lists every lot (multiple rows for each lot) and Col B lists every plot for the lot in Col A. For example:
Lot --- Plot
East1 - 1
East1 - 2
East1 - 3
East2 - 1
East2 - 2
etc
Then you format the cells in col A & B as "locked" and turn on worksheet "Protection" so no changes could occur in Col A& B while you're entering data in Col C thru ?. This way if you come accross a deed with a lot/plot that has already been filled in, you know either there's been a typo or the plot's been sold twice.

4. If you have to create multiple tables within Excel, make sure you create appropriate links. IE: The plot table would have a column for Lot# which is entered in the same format between the Lot Table and Plot table (eg: don't enter for lot "East1" in 1 table and "East-1" in the other table. The order of the columns is important for use of certain formula's such as =vlookup() which you may be using. Here's a link explaining vlookup:
http://www.contextures.com/xlfunctions02.html
Also you'll want to understand the use of autofilters which you'll find useful. Link is:
http://www.contextures.com/xlautofilter01.html
You may also find other excel techniques useful such as Pivot Tables and MSQuery. It would be best to understand these available tools before designing your Excel file.