record based table selection...

[jcrawford08]

Hello All,

I have a database I'm working with that records processes launched for clients; at the close of a process, all of the records regarding that process are moved to an archived table within our database.

Unfortunately, that makes them mutually exclusive - to get performance data (processing times of files etc.), I have been writing one report for Active Processes and one report for Inactive Processes, each pulling from the respective tables.

The field names do not change, only the table they are housed in; is there any way to have a formula that selects the table the fields reside in based on the records existence within a given table...

i.e. if Client1 name is in Table.Active then pull data from Table.Active else pull from Table.Inactive....

Any ideas?


Thanks,

 

[briangriffin]

I would write a command object to return the recordset. There are many ways to do it, but since you only want inactive records if that customer doesn’t exist in the active table it would look something like this:

Select ‘Active’,clientcode, otherfield
From table.active

Union all

Select ‘Inactive’,clientcode, otherfield
From table.inactive
Where clientcode not in (select clientcode from table.active)


I only include the ‘Active’ and ‘Inactive’ values in case you need to indicate in the report which records come from which table. You could also compile it as a stored procedure and base the report on that.