1. Forum
  2. USENET
  3. comp.databases.ms-sqlserv

  • Brainstorming Ideas

    From musicloverlch@lhowey@gmail.com to comp.databases.ms-access on Tue May 3 11:08:06 2022
    From Newsgroup: comp.databases.ms-sqlserv

    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


    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ron Weiner@rw@domain.com to comp.databases.ms-access on Tue May 3 17:45:24 2022
    From Newsgroup: comp.databases.ms-sqlserv

    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 :
    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
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From musicloverlch@lhowey@gmail.com to comp.databases.ms-access on Wed May 4 18:36:00 2022
    From Newsgroup: comp.databases.ms-sqlserv

    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.
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ron Weiner@rw@domain.com to comp.databases.ms-access on Wed May 4 21:57:50 2022
    From Newsgroup: comp.databases.ms-sqlserv

    musicloverlch expressed precisely :
    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.

    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
    --- Synchronet 3.21d-Linux NewsLink 1.2