Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Unanswered: SQL query from a table with multiple items

    hi there,

    I have a simple table with projects, some dates and a column "status" showing if the project has finished or not (yes/no type). The projects are identified with nummbers (Project-Nr). in the table it ist possible that one and the same project number has not been finished and is marked with no, but at a later time it has finished and is marked with yes in the column status.

    for the report I need to extract projects that are finished and the ones that are not. but I cannot simply choose the ones that are marked with yes or no, because some projects with the same number are simply both status yes and no.

    do you have any ideas how to solve this?

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "I need to extract projects that are finished and the ones that are not. but I cannot simply choose the ones that are marked with yes or no, because some projects with the same number are simply both status yes and no."

    At a guess you want to SELECT the Project-Nr and then a field to evaluate whether or not that project has been finised based on status within N rows of the table but you only want to return 1 row per project in the query and whether or not it is finished? (If all of the N rows have satisfying crieteria = finished else = unfinished?)

    Is this correct? If not, please clarify.

    Also please post the details of the table - fieldnames and datatypes which you need to evaluate.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Oct 2009
    Posts
    8
    the parts of the table :
    Project-Nr Status
    400 No
    401 No
    402 Yes
    403 No
    403 No
    403 Yes
    ....
    Date-at a certain time a project was not finished at a later time point it is finished, that's why I have duplicate entries in Project-Nr

    Project-Nr ist numerical, Status is Yes/No (True/False)

    I need to extract Projects for the report, the user can choose if he wants to see the finished or unfinished projects. The problem is for example with the project number 403, because it was first set to status NOT FINISHED and later the same number is finished, otherwise I could simply just check the status column and extract the finished and the unfinished ones. I cannot do that, because then project number 403 would appear as unfinished and it is finished in the end.

    I hope I could explain what I mean

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    So amend the query to select only the greatest date record for each project number.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Oct 2009
    Posts
    8
    I'm not sure I understood what you mean, also I have one more question
    should I put the function MAX directly after SELECT or in the WHERE condition


    SELECT Project-Nr, max (Datum), Status FROM Projects WHERE Status=True

    oder

    SELECT Project-Nr, Datum, Status FROM Projects WHERE max (Datum) AND Status=True

    should I group the projects, what would be the SQL query?
    thanks

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    SELECT [Project-Nr], Max([Datum]), [Status]
    FROM Projects
    GROUP BY [Project-Nr], [Status]
    You can't use [Status] in the WHERE clause according to the point that you made in your original post. The SQL above should give you one entry per project number, showing the date that it was created and the status at that time.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Oct 2009
    Posts
    8
    I have to use status in where condition

    I need the data for a report. in a form a user selects if he wants to see the list of finished or unfinished projects. the problem was at first (I didn't think of the max function for date) that because of the possibly duplicate entries of the same project some would appear in both categories (finished/unfinished)

    I need to filter them according to status, maybe I just didn't explain clearly what I need

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Select top date per project number
    Code:
    SELECT [Project-Nr]
         , Max(Datum) As Datum
    FROM   Projects
    GROUP
        BY [Project-Nr]
    Now you can save this query and then create another query based on this to get the applicable status field, or just do all the work in one go:
    Code:
    SELECT a.[Project-Nr]
         , a.Datum
         , a.Status
    FROM   Projects As a
     INNER
      JOIN (
            SELECT [Project-Nr]
                 , Max(Datum) As Datum
            FROM   Projects
            GROUP
                BY [Project-Nr]
           ) As top_project
        ON top_project.[Project-Nr] = a.[Project-Nr]
       AND top_project.Datum = a.Datum
    HTH
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2009
    Posts
    8
    I have another question..
    I wrote the code for extracting the data, but I have problems how to implement it for my report

    this ist my code:

    stSQL = "SELECT [Pr-Nr], Status, Max (Termin) FROM Termine GROUP BY [Pr-Nr], Status"
    rsPSB.CursorLocation = adUseClient
    rsPSB.Open stSQL, conCurrent, adOpenStatic, adLockOptimistic, adCmdText

    and later I want to open a report with this data and several conditions

    DoCmd.OpenReport "internes Audit", acViewPreview, "rsPSB", strConditionDatumAb & " And " & strConditionDatumBis & " And " & strConditionStatus

    how can I do this, where should I define the source of my recors (rsPSB)?
    or if I use an ACCESS query, where should I put the select clause?

  10. #10
    Join Date
    Oct 2009
    Posts
    8
    I almost have a solution, but the syntax ist not quite right. I think I cannot use the Max(Termin) function for this sort of filter, but another idea is to sort the dates abscending and then to take the top 1, but I get an error message. what should the filter be like?

    strConditionLatestDate= "Termine.Termin ORDER BY Termin DESC LIMIT 1"

    DoCmd.OpenReport "internes Audit", acViewPreview, "query", strConditionDatumAb & " And " & strConditionStatus & " And " & " And " & strConditionLatestDate


Posting Permissions

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