Best Way to Design

[RFJ]

I've got a prototype database that handles pay surveys. Data is entered by
organisation and is reported by job level, quartile, average, etc.. The
statistical element is quite complex so has over a hundred queries, many of
which form unions.

I want to enhance the design of the database (mark 2) so I can select the
organisations that form the analysis (eg by company size, location, etc.).
The selection criteria will be on up to ten specific fields but the values
in the fields will change (eg I might need large organisations (by turnover)
in a certain sector - and then for another analysis, medium sized
organisations). As the analyses are client driven, I can't predict what
comes next - so will probably need an approach akin to QBF

Any ideas on how I might proceed.

TIA

ROb

 

[Jeff Boyce]

Not being there, it's a little tough to offer specific suggestions without
an idea of how you've structured your data.

 

[RFJ]

Jeff, thanks for the reply. The database is currently designed as follows :

There is a table ('participant') that contains information unique to each
participating company (eg city location, contact details).

Organisation size, turnover, sector, plan funding rates, etc. (that are
common to participants) are handled via small look-up tables linking to the
'participant' table.

'Salary_Data' is a table that contains all the pay information for each
participating company. It has a job level (linked to a look up table
specifying one of nine levels) and then manually entered data on average
salary and number of posts in the participating organisation. It is
obviously linked to 'participant'.

All the salary analysis queries select on a particular level and then
calculate one of the following - Min, Q1, Median, Average, Weighted Average,
Q3 and Max salary. Union queries then bring the values (eg Min) for each of
the nine levels together for reporting.

I essentially need a mechanism that sits between the existing tables and all
the queries so I can pass selected information through for reporting.(The
selections would always be from the participant table - but as I mentioned
in my first post, I can't predict in advance what they might be.)

HTH

Rob

 

[Jeff Boyce]

Given the wide variety of potential selection criteria, I'd probably create
a routine that runs on a button click on the form. The routine would
"dynamically" build a SQL statement that would serve as the source for your
report.

Good luck

Jeff Boyce
<Access MVP>