Dynamic Query to show/not show field

[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

 

[Tom Ellison]

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]

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?

 

[Tom Ellison]

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