Re-using queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I need help finding a solution regarding re-using queries. To produce a report for 'department 1' I have built a series of queries (26) merging tables and massaging the data, which I run in a macro. My problem is, there are 13 departments for which I need to produce similar reports. I would like to re-use my 26 query set for each department instead of building 12 more 26 query sets

Each department has its own columns of data in the table. Is there a way to 'generalize' each query may be an &1 and have the queries built depending on the input (department(1to13)). Would this need to be done in VB or C++ or SQL

Thanks in advance for help

Bill
 
Bill: MS Access is very capable of handling this, but would be most efficient done programmatically (VBA code on a form)
If that is not an option, a parameter query might get you started
SMK
 
If, as I suspect, you have fields like

DeptA_MaterialsBudget
DeptB_MaterialsBudget
....

and
DeptA_MaterialsExpense
DeptB_MaterialsExpense
....
DeptL_MaterialsExpense

Then the only way you can do this is going to be using VBA to build the query
strings. If your field structure is more like

DeptID
BudgetID
Amount

Then this becomes really simple. All you need is a parameter query and a form
to hold the various department ids.
 
Hi, I need help finding a solution regarding re-using queries. To produce a report for 'department 1' I have built a series of queries (26) merging tables and massaging the data, which I run in a macro. My problem is, there are 13 departments for which I need to produce similar reports. I would like to re-use my 26 query set for each department instead of building 12 more 26 query sets.
Click to expand...

I'd suggest that you need TWO queries, not 26 or (good grief!) 312.
Access provides a very useful feature - a "Parameter Query"; you can
use a criterion such as

[Enter department:]

rather than "Department 1" or "Department 11".
Each department has its own columns of data in the table. Is there a way to 'generalize' each query may be an &1 and have the queries built depending on the input (department(1to13)).
Click to expand...

If you have fields name Department1 and Department13... your
difficulty is coming because your table structure IS WRONG. "Fields
are expensive, records are cheap" - rather than 13 fields expressing a
one-to-many relationship embedded within a single record, you should
consider having 13 *RECORDS*, with a DepartmentNo field and a field to
link the table to your main table.

Could you perhaps post a bit more information about these 26 queries,
such as the SQL view of one or two of them? What are you "merging"?
Are you falling into the very common trap of assuming that you must
create a Table containing merged data in order to produce a report?
Because if so - you don't; you can base a Form or a Report directly on
a query.
 
Thank you for your expert suggestions. More details - this scenario deals with sales forecasting - managers input potential projects $ values into 3 different tables. Each table has common columns (project id, cust name, desc...) table B contains info for projects that are to be done in 1 office with 1 confidence level (high, medium, low) a feel they will obtain the work). Table C has records where the work will be done in 1 office but there is a mix of confidence (the project is multi phased with different confidences for each phase). Table D has records where there is 1 confidence level but the work is multi phased and more than 1 office will be doing the work. It was a simple concept that was kept simple for ease of data entry but somewhat more difficult to pull out reports. The departments are described differntly in each table (table B - 1 column for the dept and 1 column with confidences (1, 2 or 3) - (table C - 3 columns for each dept, 1 column for each confidence) - (table D - 1 column for each dept for each office (3 offices)) - My queries pull together records by dept for each confidence by doing many simple appends and calculations - 26 queries give me data for 1 dept for a report. Don't parameter queries deal with filtering records in columns? I want to pick and choose different columns depending on the department - my queries are all hard coded focussing on specific columns (department1 with associated confidence column / department1 with columns high, medium, low / department1 office1, department1 office2, department1 office3 - is there a way to re-use my query set allow a parameter to be set in each query so the queries choose the correct columns.

thanks again
Bill
 
Thank you for your expert suggestions. More details - this scenario deals with sales forecasting - managers input potential projects $ values into 3 different tables. Each table has common columns (project id, cust name, desc...) table B contains info for projects that are to be done in 1 office with 1 confidence level (high, medium, low) a feel they will obtain the work). Table C has records where the work will be done in 1 office but there is a mix of confidence (the project is multi phased with different confidences for each phase). Table D has records where there is 1 confidence level but the work is multi phased and more than 1 office will be doing the work. It was a simple concept that was kept simple for ease of data entry but somewhat more difficult to pull out reports. The departments are described differntly in each table (table B - 1 column for the dept and 1 column with confidences (1, 2 or 3) - (table C - 3 columns for each dept, 1 column for each confidence) -
(table D - 1 column for each dept for each office (3 offices)) - My queries pull together records by dept for each confidence by doing many simple appends and calculations - 26 queries give me data for 1 dept for a report. Don't parameter queries deal with filtering records in columns? I want to pick and choose different columns depending on the department - my queries are all hard coded focussing on specific columns (department1 with associated confidence column / department1 with columns high, medium, low / department1 office1, department1 office2, department1 office3 - is there a way to re-use my query set allow a parameter to be set in each query so the queries choose the correct columns.
Click to expand...

Well, as is often the case, your queries are difficult because your
data is not properly normalized! You're storing data - the confidence
level - in tablenames. If all four tables have basically the same type
of data there should be ONLY ONE table; if you have information about
departments it should be in ONLY ONE Departments table, etc. You also
have data - high, medium, low - in fieldnames; instead you should have
a Confidence column with values high, medium, low, probably in a
"many" side table.

Consider reexamining your table structure and avoiding storing data in
tablenames and fieldnames. You're going to have no end of difficulty
constructing queries in this table structure!
 
Back
Top