Can two tables be used to run one field in a form

[Michelle C]

I am working on an existing database (5yrs of data) that drives work records
for my team eg who submitted a change to us, what the change was etc etc.
We use a form that is linked to a number of tables in order to record
pertinate details, one of these dedicated tables recording details about the
submitters of the work.

Given this is a database and no records should be delete, but the early data
in this submitter table is becoming dated and making it difficult to use the
spreadsheets generated from queries using that particular table, because
many of the initial submitters are no longer around (ie need to be manually
deleted from spreadsheets created from the queries using the table).

In this regard I would like to start a fresh table, however we still need to
be able to view the old data in the form records for historical and
investigative purposes (ie need to be able to search on the field or
submitter names old and new to find out what changes came in and why).

This is probably a silly question, but is there a way to retain the old
table linked to the form field and have it still display at the same time as
being able to use a new fresh table linked to the same field for more current
records, or am I limited to having to add an additional field on the form to
the new table? .... any suggestions for the best solution/alternatives?

 

[Wayne-I-M]

Hi Mchelle

It may not be the best idea to add another table. You can very simply add
some form of date criteria to you application / form / query / etc.

So this way you wouldn't to create a new table and you could use record
created after a certain date as default but still have the option to look at
older records.

The simplest solutions are normally the best - so don't add another table if
you need really need to

 

[Arvin Meyer MVP]

It is possible to build a second table and change the form's recordsource in
code. That's not a good idea. First because data will invariably be entered
in the wrong table, or viewed from the wrong table.

Simply adding a Yes/No (Boolean) field to mark (using an update query, if
possible) the rows you want, with the new, or edited rows being defaulted or
changed to whichever the non-historical choice you make, will solve all your
problems and maintain good database design. You could add a datefield to
accomplish a similar result.