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 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.