Preselection of source records

[RFJ]

I've just developed a database that collects and analyses salaries from
different organisations (about 30 companies - c15 entries per company - 3000
posts).

The data is kept in 2 main tables - orgs (which has company details - eg
sector, turnover, number of staff) and info (which has average salaries paid
by each company for each survey job level). There are /lots/ of queries to
calculate medians, quartiles, weighted averages, etc..

I've now been asked to add a facility where survey information can be
filtered by organisation size and/or organisation turnover. (Pity that was
not specified at the start as this is the problem!)

The information to do this selection /is/ in the tables and I know how to
set up the query to identify whatever records are required - so no problems
there. The problem is that all my existing queries point to the source
tables (Orgs and Info) and if I put in a pre-selection query I guess I then
have to adjust every other query (nearly 100 ) to point to the new
query - not the existing tables.

So two questions for SKS's out of there :
1. Is there a quick and dirty method to achieve the outcome (eg setting up a
query to delete the unwanted records in the two tables). If so, how do I
delete in two tables (I know how to do one.)
2. For the longer term, is there a way I can add a preselection query
without then having to go through every other query and report to change
source queries and tables.

 

[Rebecca Riordan]

I think your best bet is going to be to set up a pair of tables and add the
appropriate records to them at run-time (empty and delete each time).
There's no way to pass the name of the record source (the FROM clause) to a
query. By using duplicate tables at runtime, you can pass the selection
criteria at runtime (APPEND...WHERE), and you have considerable flexibility
if they add additional criteria later on in the project. (If it happens
once, it can happen again, right?)

You'll still need to re-direct all your existing queries, which is a pain,
but at least you know you'll only have to do it once. If you really do have
100's of them, you might consider writing a little program to manipulate the
query text rather than doing it manually. You'll need string manipulation,
which is not the sort of thing I can do off the top of my head, but
basically you'll search for the FROM (InStr) and replace is with a new one.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress