Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Previous Entry..DLookup?

    Hey guys I have a table called tblproductivy. The data comes from our AS400 system and Im using an unmatched append query to append new records to a local table i created. The basic structure of my local table (tblproducitivy) is as follows:
    TaskingGroup
    User
    Date
    StartTime
    EndTime
    (there are other fields but these are the ones i need to concentrate on)

    I need to add a field called Gap Time in a query that is the time between the users tasks. I can't use a dlookup up looking at the previous record i need it to verify that its looking at the same user, the same data then give me the endtime of the previous known entry.
    Last edited by Syrch; 08-26-14 at 09:27.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Instead of make table, use append query to add to a table that has extra fields you need.

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by ranman256 View Post
    Instead of make table, use append query to add to a table that has extra fields you need.
    Thank you, I do that with an unmatched append query. Sorry I didn't specify.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why can't you...
    I can't use a dlookup up looking at the previous record i need it to verify that its looking at the same user, the same data then give me the endtime of the previous known entry.
    .
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    why can't you...
    .
    Maybe i typed this wrong. I read the easy way to do this is to use an autonumber field as a key and just look at the previous entry. The problem i'll have with that is the previous entry might be a different user. See attachment
    Attached Thumbnails Attached Thumbnails Untitled.png  
    Last edited by Syrch; 08-26-14 at 09:48.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how have you defined your dlookup?

    you can place multiple criteria on a dlookup's "where clause"

    however you may be better off with a subselect. eg:-
    Code:
    SELECT T1.TaskingGroup,  T1.User, T1.StartTime, T1.EndTime,
    (select top 1 T2.endtime from mytable as t2
    where t1.user = t2.user and t1.eventdate >= t2.eventdate and t1.starttime>= t2.endtime) as LastCompletion
    from mytable as T1
    mind you you will definitely be better off NOT using the reserved word 'date' for a column name.

    it would also make your job easier to store the date AND the time in the same column
    Code:
    SELECT T1.TaskingGroup,  T1.User, T1.EventStart, T1.EventEnd, 
    (select top 1 T2.endtime from mytable as t2
    where t1.user = t2.user and t1.eventstart >= t2.eventend) as LastCompletion
    from mytable as T1
    this assuem that you get rid of eventdate, starttime and endtime columns and replace with eventstart and eventend
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    how have you defined your dlookup?

    you can place multiple criteria on a dlookup's "where clause"

    however you may be better off with a subselect. eg:-
    Code:
    SELECT T1.TaskingGroup,  T1.User, T1.StartTime, T1.EndTime,
    (select top 1 T2.endtime from mytable as t2
    where t1.user = t2.user and t1.eventdate >= t2.eventdate and t1.starttime>= t2.endtime) as LastCompletion
    from mytable as T1
    mind you you will definitely be better off NOT using the reserved word 'date' for a column name.

    it would also make your job easier to store the date AND the time in the same column
    Code:
    SELECT T1.TaskingGroup,  T1.User, T1.EventStart, T1.EventEnd, 
    (select top 1 T2.endtime from mytable as t2
    where t1.user = t2.user and t1.eventstart >= t2.eventend) as LastCompletion
    from mytable as T1
    this assuem that you get rid of eventdate, starttime and endtime columns and replace with eventstart and eventend
    Not sure a subselect would work. There will be multiple "top" records as the user can have 100's or records in the day.

    The data comes right for our as400 and i already have to convert the date and type into date and type fields.

    Im still new at dlookups honestly and can't seem to figure out how to put one together that would achieve the results i need.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    [QUOTE=Syrch;6623928]Not sure a subselect would work.[/code]
    why, have you tried those queries?
    ..here's a hint hint try 'em before you discount 'em

    Code:
    SELECT T1.TaskingGroup, T1.User, T1.EventStart, T1.EventEnd,  (select top 1 T2.EventEnd from mytable as t2
    where t1.user = t2.user and t1.eventstart >= t2.eventend) AS LastCompletion
    FROM mytable AS T1;
    returns:-
    Code:
    TaskingGroup	User	EventStart	EventEnd	LastCompletion
    31O	MA9	25/08/2014 09:06:44	25/08/2014 09:18:44	
    81E	DS9	25/08/2014 09:14:23	25/08/2014 09:36:21	
    61O	TJ5	25/08/2014 09:20:00	25/08/2014 09:43:01	
    31O	MA9	25/08/2014 09:21:40	25/08/2014 09:32:58	25/08/2014 09:18:44
    3SP	TEJ	25/08/2014 09:34:09	25/08/2014 09:39:01	
    33E	DLM	25/08/2014 09:37:08	25/08/2014 09:43:21	
    3SP	TEJ	25/08/2014 09:39:17	25/08/2014 09:41:53	25/08/2014 09:39:01
    using:-
    Code:
    SELECT T1.TaskingGroup, T1.User, t1.EventDate, T1.StartTime, T1.EndTime,  (select top 1 T2.EndTime from mytable as t2
    where t1.user = t2.user and t1.StartTime >= t2.EndTime) AS LastCompletion
    FROM mytable AS T1
    will return:-
    Code:
    TaskingGroup	User	EventDate	StartTime	EndTime	LastCompletion
    31O	MA9	25/08/2014	09:06:44	09:18:44	
    81E	DS9	25/08/2014	09:14:23	09:36:21	
    61O	TJ5	25/08/2014	09:20:00	09:43:01	
    31O	MA9	25/08/2014	09:21:40	09:32:58	09:18:44
    3SP	TEJ	25/08/2014	09:34:09	09:39:01	
    33E	DLM	25/08/2014	09:37:08	09:43:21	
    3SP	TEJ	25/08/2014	09:39:17	09:41:53	09:39:01
    Quote Originally Posted by Syrch View Post
    There will be multiple "top" records as the user can have 100's or records in the day.
    so?
    you have specifically requested that the finish time of the previous operation by the same user is brought forward. so thaose queries should do that, irrespective of how many rows there are for a specific user, the sub query should always return the previous row for that user (where the user id is the same AND the new start time is greater than the previous finish time. however Im not entirely certain that the sub query will return the rows you wanted as your test data is very very limited, and it had to be retyped by me to do some basic checks
    Quote Originally Posted by Syrch View Post
    The data comes right for our as400 and i already have to convert the date and type into date and type fields.
    so convert it at source. your current design is flawed and may cause issues over time. Id be very, very surprised if the data is coming from the AS400 as separate date and time components. doing it the way you are at present may cause issues over time.. it makes using the inbuilt date and time functions trickier IF they SPAN midnight

    Unless you have a specific design requirement for date and time components to be stored separately (and I doubt you have) then store the date and time together. you can always extract the date and or the time from a datetime value.

    Quote Originally Posted by Syrch View Post
    Im still new at dlookups honestly and can't seem to figure out how to put one together that would achieve the results i need.
    so what have you tried, what are you struggling with, what isn't working
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2012
    Posts
    126
    thank you for the help Healdem, as always i appreciate it.

    I used the subselect and the results are in the attached.

    I have also including in a seperate tab a full days worth of data.

    As far as the dates go this is what i have to use to convert it in the 400
    EventDate: DateSerial(Left([qryTask]![DateCreate],4),Mid([qryTask]![DateCreate],5,2),Right([qryTask]![DateCreate],2))
    since it comes across as in a single field as a text field 20140826

    Time is also a text field: StartTime: CDate(Format([WMLAKBASD_PLPULL00]![PLASTM],"00\:00\:00"))
    and comes across 0 for midnight and 1 for 12:00:01AM
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So as your data comes across from the AS400 fix it..... do not store separate dates and times... its dodgy at best

    looking at your data I spotted there was a problem. the query should read:-
    Code:
    SELECT T1.TaskingGroup, T1.User, T1.EventStart, T1.EventEnd,
    (select max(T2.EventEnd) from mytable as t2
    where t1.user = t2.user and t1.EventStart >= t2.EventEnd
    ) AS LastCompletion
    FROM mytable AS T1
    order by  t1.user,t1.EventEnd;
    the query is simpler when you store datetime values in a SINGLE datetime column, rather than two separate datetime columns. your current approach in my books makes the job harder than it need be

    incidentally you will note that there is what looks to be a data error with 33E | AR2 where you have duplicate rows and overlapping event end and start times.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2012
    Posts
    126
    healdem I love working with you. This is working as I needed. Thank you!


    On the data that im pulling from our 400 how would i take the two fields and make them one. Since im using dateserial to format it as a date how would i concatenate that.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just format the the values as a valid date time
    its probably
    cdate(Left([qryTask]![DateCreate],4), & "/" & Mid([qryTask]![DateCreate],5,2) & "/" & Right([qryTask]![DateCreate],2) & " " & Format([WMLAKBASD_PLPULL00]![PLASTM],"00\:00\:00"))
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    just format the the values as a valid date time
    its probably
    cdate(Left([qryTask]![DateCreate],4), & "/" & Mid([qryTask]![DateCreate],5,2) & "/" & Right([qryTask]![DateCreate],2) & " " & Format([WMLAKBASD_PLPULL00]![PLASTM],"00\:00\:00"))

    Thank you healdem. I will move forward with this concept. However in order to make it work I had to create the formated date column and the formatted time column then concatenate them in another column. It works though, thank you

    Its almost friday so have a beer on me


Posting Permissions

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