Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Getting a field from a "previous" record in a subquery

    I have an Access 2010 database with a Jobs table that includes these fields.
    - OrderNo
    - JobType (1 = Task1, 2 = Task2, 3 = Task3, etc.)
    - JobStatus (1 = Started, 2 = Completed)
    - JobOperator

    I have a reference query named "AllJobs" that lists all jobs for current orders, sorted by OrderNo then JobType.

    Example

    OrderNo, JobType, Job Status, JobOperator
    1001, 1, 2, Tom
    1001, 2, 2, Dick
    1001, 3, 1, Harry
    1002, 1, 2, Joe
    1002, 3, 1, Fred

    NB: Not all orders have all three job types; e.g., order 1002 has only job types 1 and 3.

    I have created another query named "StartedJobs" that lists all jobs where JobStatus = 1 (Started), and I need to include a calculated field that gets JobOperator from the *previous* job for the same order; i.e., where JobStatus = 2 (Completed).

    For example, I need the "StartedJobs" query to produce this:

    OrderNo, JobType, Job Status, JobOperator, PreviousJobOperator
    1001, 3, 1, Harry, Dick
    1002, 3, 1, Fred, Joe

    Because all orders do not necessarily have all three job types, I cannot get PreviousJobOperator simply by using "Select [JobOperator] FROM [AllJobs] WHERE [AllJobs.OrderNo] = [StartedJobs.OrderNo] AND [AllJobs.JobType] = [StartedJobs.JobType]-1" to find the record whose JobType value is one less.

    (This would work for OrderNo 1001 but not for OrderNo 1002, because there is no record in AllJobs WHERE OrderNo = 1002 AND JobType is (3 - 1) = 2.)

    Is there another way to get the value I need by finding a matching record in the AllJobs query and then selecting the *previous record*?
    Last edited by Murgatroyd; 2016-07-05 at 00:14.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I would create a query "CompletedJobs" of just completed jobs.
    In your "StartedJobs" query I would introduce a LEFT JOIN for this new query to get the PreviousJobOperator.

    Query StartedJobs: SELECT OrderNo, JobType, JobStatus, JobOperator FROM JobsTable WHERE JobStatus = 1;

    Query CompletedJobs: SELECT OrderNo, JobType, JobStatus, JobOperator as PreviousJobOperator FROM JobsTable WHERE JobStatus = 2;

    New Query to get the final result:
    SELECT StartedJobs.OrderNo, StartedJobs.JobType, StartedJobs.JobStatus, StartedJobs.JobOperator, PreviousJobOperator
    FROM StartedJobs LEFT JOIN CompletedJobs ON StartedJobs.OrderNo = CompletedJobs.OrderNo AND StartedJobs.JobType = CompletedJobs.JobType
    WHERE CompletedJobs.OrderNo is not Null;

    This new query is more easily defined using the query Design Mode.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. However, I'm not sure whether I'm following correctly.

    "CompletedJobs" will produce:
    OrderNo, JobType, Job Status, PreviousJobOperator
    1001, 1, 2, Tom
    1001, 2, 2, Dick
    1002, 1, 2, Joe

    "StartedJobs" will produce:
    OrderNo, JobType, Job Status, JobOperator
    1001, 3, 1, Harry
    1002, 3, 1, Fred

    But it looks like "ResultQuery" will list nothing, because a join on the above queries will produce matches on "StartedJobs.OrderNo = CompletedJobs.OrderNo" but no matches on "AND StartedJobs.JobType = CompletedJobs.JobType" (all the started jobs have JobType = 3, whereas all the completed jobs have JobType 1 or 2)?

    It looks like somehow I need to list all the started jobs and, for each one, get the operator (i.e., PreviousJobOperator) from the *highest* completed job with the same order no?
    Last edited by Murgatroyd; 2016-07-03 at 23:27.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    True what you say.
    You say:
    It looks like somehow I need to list all the started jobs and, for each one, get the operator (i.e., PreviousJobOperator) from the *highest* completed job with the same order no?
    What determines the *highest* completed job?

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    For each order, jobs are completed sequentially by JobType; i.e., regardless of whether an order has JobTypes 1, 2, and 3, or only 1 and 3, or only 2 and 3, the jobs are completed in numerical sequence, so for each order, the highest completed order is the one with the highest JobType where JobStatus = 2 (Completed).

    "AllJobs" (sorted by OrderNo then JobType then JobStatus) will produce:
    OrderNo, JobType, Job Status, JobOperator
    1001, 1, 2, Tom
    1001, 2, 2, Dick
    1001, 3, 1, Harry
    1002, 1, 2, Joe
    1002, 3, 1, Fred

    "StartedJobs" (where JobStatus = 1) will produce:
    OrderNo, JobType, Job Status, JobOperator
    1001, 3, 1, Harry
    1002, 3, 1, Fred

    So to get PreviousJobOperator for each record in StartedJobs, is there a way to get JobOperator from the record in AllJobs with the *highest* JobType where JobStatus = 2 and OrderNo = the same?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have included a database rather than try to explain it all
    Attached Files Attached Files

  7. The Following User Says Thank You to patt For This Useful Post:

    Murgatroyd (2016-07-04)

  8. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Yes, I see how that works. Thanks for your help.

  9. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    1. Normally each job must be completed before the next can be started; however, sometimes an order can have two started jobs at the same time, but the result query should only list the *lowest* started job for each order.

    Example

    OrderNo, JobType, JobStatus, JobOperator
    1003, 1, 2, Bill
    1003, 2, 2, Dave
    1003, 3, 1, Nick
    1003, 4, 1, Steve

    The result query should produce this.
    OrderNo, JobType, Job Status, JobOperator, PreviousJobOperator
    1003, 3, 1, Nick, Dave

    ----------
    2. When the lowest started job for an order is the *first* job for that order (i.e., there are no completed jobs for that order yet), the result query needs to include that job also.

    Example

    OrderNo, JobType, JobStatus, JobOperator
    1004, 1, 1, Jack
    1004, 2, 1, Pete

    The result query should produce this.
    OrderNo, JobType, Job Status, JobOperator, PreviousJobOperator
    1004, 1, 1, Jack, [blank]

    ----------
    I think I managed to achieve these two requirements by extending the technique you demonstrated, but have I got it right, or is there a better alternative?

    Completed Jobs 2.zip
    Last edited by Murgatroyd; 2016-07-05 at 00:38.

  10. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    From what I can see you have pretty well nailed it.

    As long as you are happy with it.

    Maybe others have a more elegant solution.

Posting Permissions

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