Create a query in code or not

[rdemyan via AccessMonster.com]

My application has a table that contains information on buildings. The
building data comes from another database and is imported from a spreadsheet
into my application.

Originally, I thought that when a building data update is available, I would
simply delete the existing data and import the new data into the table.
However, now it seems that there may be a requirement to keep the historical
data.

Of course there is mountains of code now that goes against the building data
table. The table currently is not setup to maintain historical data.

I'm considering the following as a simple fix:

1) Add a data/time field (ImportDate) to the table and the table primary key
which will track the date the building data was imported into my application.

2) Rather than go through and change the code, instead create a query or run
a query everytime the front end opens. The query will simply select all
records from the building data table where ImportDate is the most recent or I
guess I would use MAX.

3) Rename the current building table and name the query with the name that
the building table currently has. That way all code will go against the query
and not the table.

Here's my problem. I've never created a query in code. I'm not even sure if I
have to. I think I can create a query in the dbWindow that just looks for the
maximum Import Date and then gets all records with that date. This would be,
then, the most current data for all buildings. If so, would I need to run the
query explicitly in code or will Access automatically do this every time the
app opens (I think Access will automatically handle this).

Before I spend a lot of time I would like some domments, suggestions, etc.

Thank you.

--
Message posted via http://www.accessmonster.com

[Rick Wannall]

You're pretty close. Renaming the table and providing a query with the same
name as the old name of the table is a good start.

Purely for performance, you're going to want to do something about that
most-recent-data selection process. Looking for the Max() of your import
date, and then filtering based on that every time you run that query will
take a toll on your performance. In concept, you're correct, but in
practice you can do something once, after you import the data, and improve
your performance.

There are several ways to approach this. Here are a couple:

Add a yes/no field to your data table. Set its default to Yes. Before the
import, run an update query like this:

Update BuildingData Set MostRecent=false where MostRecent=true;

This, of course, means that you're storing information that you could
calculate. In general, this is a no-no. However, it is common to
denormalize for the sake of performance. In this approach, your new query
would simply select records like this:

Select bd.* From BuildingData As bd Where bd.MostRecent=true;

Another approach: Create a "system parameter" table if you don't have one.
Several fields: ParameterKey, ParameterValue_String, ParameterValue_Date,
ParameterValue_Long, etc. as needed. You really only need the date one.
Anyway, after your import you would update like this:

Update ParamTable as pt
Set ParameterValue_Date = (Select Max(ImportDate) From BuildingData;)
Where ParameterValue = 'MostRecentBDImportDate'

That puts your most recent import data on one row in your parameter table.
Then your new query (replacing the table) would select like this:

Select bd.* From BuildingDate as bd Inner Join ParamTable as pt on
bd.ImportDate = pt.ParameterValue_Date;

Hope one of these helps or suggests a solution for you.

Be sure to index ParameterKey as primary key, of course , technically you
might get some benefit from indexing each of the parameter value columns
individually. However, on a table as small as the parameter table I have my
doubts about actual gains being realized from those indexes.