Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: How do you pull same data from multiple rows/columns in a query?

    Hello,

    I need to know how to query on the word “Completed” which could be recorded in 10 different columns for multiple projects.

    So for example…
    Column A = Project Name
    Column B = Task A
    Column C = Task B
    Column D = Task C

    In each column, the word “complete” can be in various columns. So, how can I pull a query that captures all of the columns that has the word “Complete” in it?

    I have been assigned a task to manage a report with all "completed" tasks and I don't want to pick and choose manually.

    Please help! Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    select
      my,
      column,
      list
    from mytable
    where
      columna = 'Completed'
      or columna = 'Completed'
      or columnb = 'Completed'
      or columnc = 'Completed'
      or columnd = 'Completed'
    how you express that int he query designer I don't know, its years since I last used it.

    however having a table where columnB = taskA, columnC = taskb and and so on suggests your table design is not normalised, and this I'd suggest is part of the reason why you are struggling
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    incidentally where are you setting completed
    I would argue that it should come from a status table and use a code in your data so there is no risk of problems from spelling mistakes, typos or miscapitalisation
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2008
    Posts
    150
    Thank you healdem for your response. Unfortunately, I am using someone's tables. I have to pull from 10 tables with approximately 80 columns. This is way over my head ugh!

    Again, I thank you for your generous suggestions.

  5. #5
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    It almost sounds like you are trying unpivot the table in order to have a list of completed tasks per project, correct? Unfortunately, there is not an unpivot method within Access, but you might be able to brute force it through a series of UNION ALL statements.

    Code:
    SELECT COLUMNA AS [ProjectName], "Task1" AS [Task]
    FROM Table3
    WHERE (((COLUMNB)="Complete"))
    
    UNION ALL SELECT COLUMNA AS [ProjectName], "Task2" AS [Task]
    FROM Table3
    WHERE (((COLUMNC)="Complete"))
    
    UNION ALL SELECT COLUMNA AS [ProjectName], "Task3" AS [Task]
    FROM Table3
    WHERE (((COLUMND)="Complete"));
    It is unefficient in that you will have do this for each task you are trying to capture. If the defined tasks are standard (the field names for Columns B - n...) then you should not run into too much difficulty. However, if new tasks are common (say someone decided to add another column to their table) or change what a certain task was used for (Column B was "template completed" and now "client approved") then you will have a lot of trouble keeping your query up to date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •