Fixing Existing Indexes

When Do I Need to Fix Indexes

When converting existing data into the Enterprise DataLog, each sale must have a unique index number. If you are already assigning unique values in the sale index field, then nothing needs to be done to your databases. They can be converted as they are.

If you have sale indexes that are blank or duplicated across your sales, you must fix them before loading sales into the Enterprise DataLog. If you have a single master database with blank sale indexes, you may be okay. In this case, the Database ID will be converted into the Sale Index field. If you have multiple databases more work is needed to fix the indexes.

The easiest way to fix the sale index field is to use Microsoft Access and the 2005 Database before beginning the conversion. The following steps will show you how to update the indexes using Access.

What Values Should I Use for a Sale Index

The value of the sale index is specific to your setup and needs. Sale indexes are the numbers that will be used as permanent tracking numbers attached to your sales. This may be used to track paper files or other records related to the sale. An important item to consider is what values your users may already be using to track items related to the sale. The Enterprise DataLog does not use the Database ID to identify sales. This is because sales may be loaded from multiple 2005 databases. The existing Database ID is not unique across all of your databases.

Many times it is desirable to have a sale index that has some type of prefix that represents the year of the sale and possibly the county where the sale took place. Then after that number, a unique number can be assigned. This method works well for users that keep paper binders of sales by year and organized by county. It also helps keep the unique number low so pre-numbered tabs or stickers can be used in sale binders or on maps.

The steps below will also use the existing Database ID as part of the new sale index. This can be helpful because you will not lose any information that was tied to the old Database ID number.

Using Microsoft Access To Fix Sale Indexes

This must be done before converting the UAAR 2005 database.

In order to use Microsoft Access to fix sales indexes, you must have a full version of Access installed.

To open the files, find your sales database and double click on it. This will open the database file in Access. Do not open the database from within the UAAR 2005 program. Depending on the version of Access you have installed you may see a "Security Warning" asking you if you want to "open this file or cancel the operation". Choose "Open" to open the file.

Once Access is open, click on the Queries button on the left side of the screen.

Next double click on the "Create query in Design View".

The "Show Table" screen will appear listing tables in this Database. Click on the "Close" button to close the screen.

Next click on the menu item "View -> SQL View". The screen will change to a text box. Copy and paste the following text into that box. The exact query that you need to use may vary depending on your needs. Please contact AgWare if you have any questions.

Warning: The query below will update any existing index numbers. If you are not sure this is what you want to do, please contact AgWare and make sure you have a backup copy of your database before continuing.

UPDATE AGMain SET AGMain.PVS_INX_NO = 
	Format(Cur_Sale_Date, "YYMM") 
	+ "MM1." 
	+ Format(DB_Rec_NO, "0000");

The result should look like this screenshot.

Next choose the menu item "Query -> Run". You will be prompted with the message "You are about to update XX row(s)". Click Yes to continue and update your database.

You can now close Microsoft Access. You will be prompted to save the query. You can choose "No", the query does not need to be saved. Now open the database in the UAAR 2005 program as normal. Verify that your sale indexes are what you expected.

Once your index numbers are fixed, you can convert the database into the 2006 UAAR DataLog and use the Batch Post tool to upload the sales into the Enterprise DataLog.

Converting Multiple Databases

When you are converting multiple database, you need to run this procedure for each database you are converting.

Before running the query, you should adjust the highlighted text below. This text should be unique to each database to avoid duplicate index numbers. Our recommendation is to use the initials of the appraiser who owned the database. If an appraiser has multiple databases you can include an index number (1, 2, 3, ...) after the initials.

UPDATE AGMain SET AGMain.PVS_INX_NO = 
	Format(Cur_Sale_Date, "YYMM") 
	+ "MM1." 
	+ Format(DB_Rec_NO, "0000");