Dynamic Query to show/not show field

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I need some help w/ the desing of a query.

Let's say, the data source table contains the following columns:
- DIV
- BRANCH
- TASK1
- TASK2
- TASK3
- TASK4

Based on the data in the table, a SELECT query shows the following results:

DIV BRANCH TASK1 TASK2 TASK3 TASK4
Div1 BranchA X X
Div1 BranchB X X
Div2 BranchC X
Div2 BranchD X
Div2 BranchE X


Based on the results, no division/branch does do "Task3". Therefore, I
don't even want to show the field "Task3" in my query.

However, if another branch is added who performs Task3, I then certainly
want to display that field in the query.

I also need to know how to dynamically link such query to a report without
"allocating space" in the report. If Task3 doesn't exists, I don't want to
show the header for that field (nor any empty records below that field).

Any suggestions how to do that?


Thanks,
Tom
 
There is something happening in your design of the table that makes
this, and potentially many other tasks later on, very difficult to do.

There are a set of "rules" (probably better described as very
important guidelines) for database design. One of them is to avoid
redundant columns, such as your TASK1, TASK2, TASK3, TASK4 columns.

Rather, a "Normalized" design (one that follows the rules) would have
these columns:

DIV
BRANCH
TaskNumber

Your data would then look like this:

Div1 BranchA 1
Div1 BranchA 3
Div1 BranchB 1
Div1 BranchB 2
Div2 BranchC 1
Div2 BranchD 1
Div2 BranchE 1

If your data were built like this, you could then use a crosstab query
to create just what you describe. The column(s) for each task would
appear and disappear according to usage.

In the report, you will have to accomodate a maximum number of columns
and make those that are unused disappear using code.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom:

The field names (Task1, Task2, Task3) were only sample values. As far as I know, the table design is properly developed.

So, maybe I should try the crosstab query approach... I heard about this but I really have no clue how this works.

Would you mind providing me some addtional pointers on crosstab queries?
 
Dear Tom:

There is a wizard to create them. I suggest you look at the online
help and try the wizard. You may need some more specific assistance
at that point, but there may be a bit for you to learn there first.

It does seem to me you are speaking of repetitive columns if they
function at all like you suggested. Without normalization, the
cross-tab isn't going to do the job for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top