Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2013
    Posts
    7

    Unanswered: Latest Record For Specific ID

    Hi!
    can anyone help me?

    i want my form to display the latest entry for the specific Employee ID.

    lets say Employee ID#001
    travelled on 10/06/2013
    travelled on 10/01/2013
    travelled on 10/10/2012

    but when i enter the specific Id number in my combo box to get the record for the specific person to be displayed in my form, it shows the first record entered for the person in my database i.e 10/10/2012 whereas i want form to display 10/06/2013 record.
    i have an autonumber as well in my form/table.

  2. #2
    Join Date
    Aug 2013
    Posts
    7

    Latest Record For Specific ID

    Hi!
    can anyone help me?

    i want my form to display the latest entry for the specific Employee ID.

    lets say Employee ID#001
    travelled on 10/06/2013
    travelled on 10/01/2013
    travelled on 10/10/2012

    but when i enter the specific Id number in my combo box to get the record for the specific person to be displayed in my form, it shows the first record entered for the person in my database i.e 10/10/2012 whereas i want form to display 10/06/2013 record.
    i have an autonumber as well in my form/table.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to refine the SQL
    what I think you want is
    Code:
    SELECT MAX([Travelled On] from mytable
    WHERE [Employee ID#]= 1
    you will need to replace the column and table names with your values
    you only need to use square brackets if there are spaces in your column or table names
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged
    please don't repost the same question. if you haven't got an answer within a reasonable time (and 21 minutes is nowhere near a reasonable time, more like 24 hours) then by all means bump the thread (usually by posting *** BUMP *** or similar.
    Also you should reconsider what you are asking to see if there is more information that you should provide.
    Also look at this post to make certain you've done as much as you can to help contributors respond
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2013
    Posts
    7

    Latest Record For Specific ID

    Sorry but not fulfilled my requirement
    i want the details to be shows in my form where i put the employee id# in my combo box located in my form

    my form
    it shows the first record of my employeed id but i want the latest record for my employee id

    thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what details?
    did that query give you the required date?
    what other columns do you expect in your combo box?
    are you trying to use the combo box to select and employee and then dispalky the most recent row for that employee in your form?

    if so
    use an unbound combo / list box*, populate it with employee ID's
    in the combo /list box on click event set the forms row source to be the same query as above, but asdd whatver other columns you want from the table

    *for this sort of application a list box may make more sense
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2013
    Posts
    7
    YOU ARE RIGHT

    THIS IS WHAT MY QUESTION IS :

    are you trying to use the combo box to select and employee and then dispalky the most recent row for that employee in your form?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so set the forms recordsource to be the SQL as identified above and add whatever other columns you need
    OR
    set the forms filter to be the employee id AND the max(date) retrieved for that employee
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2013
    Posts
    7

    Latest Record For Specific ID

    It is microsoft access
    how to do it?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I guess the easiest is going to be to apply a filter to the form

    fill the combo box and use a hidden column containing the date
    so you row source for the combo / list box will be
    Code:
    select EmpID, EmpName, format(max(TravDate), "yyyy/mm/dd") from mytable
    group by EmpID
    then in that combo / list box's on click event
    retrieve the employee id and date (columns(0) and (2) respectively)

    me.filter = "EmpID = " & mylistbox.column(0) & " AND mydatecolumn = #" & mylistbox.column(2) & "#"
    me.filteron=vbtrue

    you may also want a button to remove the filter

    me.filteron = not me.filteron
    or
    me.filteron=vbfalse
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2013
    Posts
    7
    Will check n let u know day after tomorrow
    Thanks foe ur supporr

  12. #12
    Join Date
    Aug 2013
    Posts
    7

    Talking Latest Record For Specific ID

    thanks a lot for he help

    got my problem..solved

    i got an idea from your suggestion and merely open the settings of combo box and mark desc order in empID
    now whenever i enter the specific id number then it shows me the latest record
    thanks

Posting Permissions

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