Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Total query (2000)

    Hi,

    I have 2 tables that are structured as follows:

    tbl_JobHistory (Table #1)
    -------------------------------------
    JobID
    LRN
    VName
    etc.

    tbl_JobHistory_Status (Table #2)
    ---------------------------------
    JobID
    Recordnumber
    Status
    StatusDate

    One JobID can contain multiple Recordnumbers.
    I want to make a query which tells me what the last status and statusdate of a specific VName is.

    Have been working with Grouped by and max of jobid but that did not give a solution.
    The only thing i get are duplicates records with different date.

    Any help is appreciated.
    TIA
    Joop

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total query (2000)

    1. Create a query based on tbl_JobHistory_Status.
    Add only JobID and StatusDate to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Leave the Total option for JobID as it is (Group By) and set it for StatusDate to Max.
    Save this query as (for example) qryMaxDate.
    This query returns the most recent date for each JobID.

    2. Create a new query based on tbl_JobHistory, tbl_JobHistory_Status and qryMaxDate.
    Join the two tables on JobID and join tbl_JobHistory_Status to qryMaxDate on JobID and on the date field (so there are two lines joining them)
    Add VName, Status and StatusDate to the query grid.

    If this query still returns multiple records per VName, we'll have to change this one and add create yet another query. Post back if you need that.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Total query (2000)

    Thanks for the quick reply Hans,

    Am still getting multiple records per Vname.
    Some additional info:
    This database has multiple records per Vname, but what i'm looking for is the last status for each vname in the database including the statusdate.
    Every JobID has a vname but can have more than one recordnumber. A JobID can be valid for 13 months after which the database
    automatically changes the status; adds a recordnumber but does not change the JobID.

    Regards,
    Joop

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total query (2000)

    Try this instead:

    1. Create a query based on tbl_JobHistory and tbl_JobHistory_Status.
    Join them on JobID
    Add only VName and StatusDate to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Leave the Total option for VName as it is (Group By) and set it for StatusDate to Max.
    Save this query as (for example) qryMaxDate.
    This query returns the most recent date for each VName.

    2. Create a new query based on tbl_JobHistory_Status and qryMaxDate.
    Join them on the date field
    Add VName, Status and StatusDate to the query grid.

    I hope this one does what you want.

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Total query (2000)

    Thanks <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>,

    The answer is no <img src=/S/sad.gif border=0 alt=sad width=15 height=15>, your first reply gave better results.
    However you gave me an idea. I'll try this later and postback with a zipped version of the database if the query still shows the "old" vname records.

    Thanks again
    Joop

Posting Permissions

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