Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005

    Unanswered: Parameter queries and tables that keep history


    In my database, I have quite some mapping tables. For each of them, I would like to be able to use the mapping of an earlier date, even though the mapping table has meanwhile changed. Therefore, I intend to add two fields (ToDate and FromDate) to the tables. These two fields then define for which date-range the mapping defined by a record is valid. If a mapping is changed, the ToDate of the old record is set to the current date and a new record (with the new mapping) is added with FromDate = current date.

    I then intend to use a saved query (q) with a parameter [date] in order to select the mappings of a specific date (i.e. ToDate > [date] > FromDate => SQL statement of q: "... WHERE ToDate > [date] AND FromDate < [date]"). I would now like to use this query in other queries (such as joins). E.g.:

    "SELECT t.*, q.f1, q.f2 FROM t INNER JOIN q ON t.f1 = q.f1"

    Is there a way to control and pass this date parameter from VBA (and use it in other queries)? I don't want to just set it to a value in a form since

    1) there might be multiple forms that affect the parameter
    2) the parameters might be read from a config file
    3) the parameter might be affected by data in other tables

    Is there maybe a better way at all to implement such mapping tables that keep track of their history?

    Thanks for any comments and ideas,


    P.S.: I'm using MS Access 2000

  2. #2
    Join Date
    Aug 2002
    Northampton, England
    Sounds like you want an audit trail. This site may prove helpful.

    Scroll down until you get to Audit Trail

  3. #3
    Join Date
    Feb 2005
    Thanks for your reply! The example you point to shows how to log changes on a table. I don't simply want to log the changes, but I want to store the table in a way, that I easily can query the state as of a certain date. The main problem I have is actually, how to pass this date to such a query. As mentioned before, I thought about adding two fields, "FromDate" and "ToDate" to the table. I decided to add two fields instead of only one ("ChangeDate"), because it seems to me that it becomes easier to query the table, and because with a "ChangeDate" only, it is more difficult to represent a deleted record.

    Obviously, I want to use this table as of a certain date in my further queries (which are often joins, as pointed out in the previous message). In order to do so, I intend to use a saved query (lets call it dateQuery), which shows the table as of a certain date. To do so, I wanted to use a parameter query, with a parameter "date". However, so far I didn't really manage to create new (saved) queries, which reference to dateQuery and which can be used without having the user to manually enter the date-parameter, or without having this date-parameter in a form.

    I meanwhile thought about the following options:

    1) Use a table tblDates with fields TableName and Date which stores the date for which the table TableName should be shown, and write a query dateQuery, that checks for this date in tblDates in a subquery in the where clause:

    SELECT * FROM myTable WHERE FromDate <= (SELECT Date FROM tblDates WHERE TableName = "myTable") AND ToDate > (SELECT Date FROM tblDate WHERE TableName = "myTable")

    I have some performance concerns with this approach, since this query is rather complex (numbers as I expect them to be realistic: we have to join about 20000 records with this table, about 20 times and there exist about 20 or 30 entries in tblDates). Is there a reason for performance concerns? Obviously it has a big impact on how optimized access performs this sub-query...

    2) Instead of the table tblDates, store a form with a text field for each table, in which the date is entered (i.e. the form replaces the tblDates above). Then, write a parameter query with a parameter that points to this text field.

    Advantage: no subquery, might perform better...
    Problem: I don't like the idea of maintainig a form to store such kind of data...

    3) Whenever the date has to be changed, rewrite the entire sql statement of dateQuery (using VBA)

    I don't like the idea of rewriting the whole query, if just one parameter changes. I guess that's unreasonable overhead, even though the dates probably don't change very often...

    4) Same as 3), but create a table with this entries

    If we have to rewrite the whole query, we can directely write a new table. Joins with this table are likely to perform better than with a query.

    Any comments are highly appreciated.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts