Brainstorming Ideas

[musicloverlch]

I might not explain this correctly, but I want to see if you have any solutions to my problem.

I would like to create a table that had the following fields

ID(autonumber)
TaskSection (string)
TaskName (String)
CompleteDate (date)

Then the table would have fields like
Task Section TaskName CompleteDate
Census Email Sent 1/1/2022
Census Census Received 1/5/2022
Census Financials Received 1/7/2022

The goal is for the user to be able to add however many tasks they want without me needing to be involved.

My problem is that I would then need to make a view where they could see all the tasks horizontally. The tasks might change so I can't put a fixed list in the crosstab query.

I really want to do this because I'm sick of having to add fields every time they want to track some new date, but I can't figure out how to overcome the crosstab problem.

Thoughts? TIA

Laura

[Ron Weiner]

I dont understand your adversion to Crosstab queries. The Sql syntax
that Access uses is pretty spiffy, and far easier to implement than the
Sql Server version. You should be able to limit the number of columns
by including the only the tasks you are interested in the Where clause.

Here is some "Air Sql" to create the output you want.

TRANSFORM Min(CompleteDate) AS MinCompleteDate
SELECT TaskName
FROM YourTable
WHERE TaskSelection in("Task1", "Task2",....)
GROUP BY TaskName
PIVOT TaskSelection;

Somewhere in front of this sql you could have a form that allows the
user to Select the Task Selection(s) they want to see, and "Bob's your
uncle".

You also will want to control the Values in Task Selection. That would
require another table TaskSelection of with 2 columns TaskSelectionId
and TaskSelection. Then TaskSelectionID becomes a Fk YourTable that you
perform the Crosstab on.

Or perhaps I have totally misunderstood your question.

Rdub


musicloverlch formulated the question :

[musicloverlch]

Thanks for your input. I don't have an aversion to crosstab queries, I just need them to be dynamic. I don't know how many tasks would be listed so it's hard to make a fixed crosstab. I tried making it dynamic, but the column headings were in a crazy order.

[Ron Weiner]

musicloverlch expressed precisely :

You can make Dynamic crosstabs in code by creating the Sql for them on
the fly. Build a form that asks your users to choose the tasks they
are interested in and the Dates to be included. Then make a Sql
statement in code and execute it.

As for the crazy order of the Column headings you can use an IN()
statement at the end of the PIVOT statement to put thinks in whatever
order makes sense for you. Something like:

...
PIVOT TaskSelection IN('ThisTask','ThatTask','AnotherTask');

Rdub