Results 1 to 9 of 9

Thread: Missing Data

  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: Missing Data

    Hi All

    Can anyone help me with some code (Ive been struggling with it all day !!)

    I have this table (clusters)

    NHS_No Cluster_No startdate enddate
    1234 6 01/04/12
    5678 9 01/05/12 31/05/12
    1234 7 01/06/12 13/06/12

    If the enddate is empty I need it to be the nearest startdate of those records with a matching NHS_No.

    So in this case it would be 01/06/12

    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Couple of questions...
    • Are you looking for a view to display data, or a stored procedure to update the table?
    • How do you define "nearest"?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Hi

    Preferably a stored procedure.

    The nearest start date is the closest date following the first record start date

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Untested code...
    Code:
    UPDATE tblRecords
    SET
       EndDate = b.StartDate
    FROM
       tblRecords
    INNER JOIN
       (SELECT
          tblRecords.NHS_No
       ,  Max(tblRecords.StartDate) StartDate
       FROM
          tblRecords
       WHERE
          tblRecords.EndDate IS NOT NULL
       GROUP BY
          tblRecords.NHS_No) b
    ON
       tblRecords.NHS_No = b.NHS_No
    WHERE
       tblRecords.EndDate IS NULL
    If I'm thinking straight, the subquery should pull out the most recent start date for each NHS record that has an end date. It then applies the start date from the subquery to the end date field of the parent record.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by andygill View Post
    Hi

    Preferably a stored procedure.

    The nearest start date is the closest date following the first record start date
    Closest to what? How do you define "the first record"?

    This could be as simple as using the aggregate function Min(), with or without a group by.

    You need to provide more sample data along with desired results from said sample data.
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2012
    Posts
    5
    Quote Originally Posted by gvee View Post
    Closest to what? How do you define "the first record"?

    This could be as simple as using the aggregate function Min(), with or without a group by.

    You need to provide more sample data along with desired results from said sample data.
    Hi the "closest" is the first date after the startdate record which doesn't have an enddate where the NHS_no's match.

    In my example NHS_No 1234 doesn't have an enddate so it will be the startdate of the other record in the table (with NHS_no 1234) in this case 01/06/12

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hi the "closest" is the first date after the startdate record which doesn't have an enddate where the NHS_no's match.

    In my example NHS_No 1234 doesn't have an enddate so it will be the startdate of the other record in the table (with NHS_no 1234) in this case 01/06/12
    Which is what the statement that I provided yesterday should give you...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Sep 2012
    Posts
    5
    Quote Originally Posted by weejas View Post
    Which is what the statement that I provided yesterday should give you...
    Hi

    It didn't quite work.
    It selects the next startdate for the matching NHS_No, however it then uses that date for all matching NHS_No without and enddate.

    NHS_No Cluster_No Startdate Enddate
    1234 7 2012-06-01 2012-05-01
    2222 4 2012-04-01 2012-04-30
    2222 6 2012-05-01 2012-04-01
    2222 7 2012-05-31 2012-04-01
    1234 5 2012-04-01 2012-05-01
    1234 6 2012-05-01 2012-05-31

    So I end up with some enddates before the startdate

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm. I did say that the code was untested.

    I just tried putting it together in Access, and while I can get the SELECT version to work, the update fails. Running a slightly revised form of the query throws error 3073, "Operation must use an updateable query."

    Revised code is this:
    Code:
    UPDATE tblRecords INNER JOIN [SELECT
          tblRecords.NHS_No
       ,  Max(tblRecords.StartDate) AS StartDate
       FROM
          tblRecords
       WHERE
          tblRecords.EndDate IS NOT NULL
       GROUP BY
          tblRecords.NHS_No]. AS b ON tblRecords.NHS_No = b.NHS_No SET tblRecords.EndDate = b.StartDate
    WHERE (((tblRecords.EndDate) Is Null));
    It executes in display mode, but will not run.

    Operation must use an updatable query. (Error 3073) Microsoft Access - Stack Overflow This is a useful explanation of the problem - looks like you're out of luck with this table structure, I'm afraid.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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