Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare 1 Record with another by date (2003)

    I'm trying to get my head around a query, and I neither know how to construct it nor even if it's possible.

    If my DB contains several records relating to a person, is it possible to compare each individual's records against each other.

    For example

    John Smith starts working for Company X on 22-Jan-06. He then starts with a new Employer on 14 June 06. Is it possible to compare those 2 dates to see the difference between them? And if it's greater than a certain value, to count John Smith as someone who was in employment for greater than 3 months?

    I know Access can compare 2 dates, but what about when those 2 dates relate to the same person but different records? How does it compare every person against his or her many records and then just return a count of that person if the condition is true.

    Hope I've explained that properly. Does it seem feasible?

    Thanks in advance for any help!

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

    Re: Compare 1 Record with another by date (2003)

    It is possible to combine queries to compare fields in different records, but how exactly should the dates be selected?
    - Is it possible that there will be 3, 4 or even more records for John Smith, or will there only be 2 at the most?
    - Are the two dates in the same field, or in different fields?

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 1 Record with another by date (2003)

    Hi Hans
    As always, thanks for your reply.

    I'll try to provide an answer.

    CUSTOMER TABLE: Customer Name (eg, John Smith)
    JOB TABLE: STATUS (eg, 'Full-time Job')
    JOB TABLE: Date Started (eg, 12 January 2005)

    Then, there might be another Record for John Smith
    JOB TABLE: STATUS (eg, 'Part-Time Job')
    JOB TABLE: Date Started (eg, 4 March 2005)

    There could be multiple occurences in the JOB TABLE, ie, over a 2-year-period, John Smith might have had 4 Jobs (this is checked against the STATUS Field, ie, if it's a full-time or Part-Time job, then include it in the query. If the STATUS is anything else (eg, just work-experience), then do not select the record.

    Record 1: Placement Date: 12-Jan-05
    Record 2: Placement Date: 4-Mar-05 -- Record 2 Placement Date minus Record 1 Placement Date = 51 days
    Record 3: Placement Date: 21-Nov-05 -- Record 3 Placement Date minus Record 2 Placement Date = 240 days
    Record 4: Placement Date: None -- Today minus Record 3 Placement Date = 210 days


    Therefore, John Smith has had 2 successful job placements, as I need to check against 3 conditions: 3 months, 6 months, and 12 months.

    Record 1 = False
    Record 2 = False
    Record 3 = True ( >180 days)
    Record 4 = True ( >180 days)

    Therefore, in the 'Employed for 6 Months' Query, the count would increse by 2.

    Does this make sense?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compare 1 Record with another by date (2003)

    Have a look at the attached demo.

    It uses three queries.

    * first one selects just the full or part time job entries
    * second one selects from the previous one the first date for each person.
    * third query joins first two queries to the customers table and compares each date with the first date for that person.
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 1 Record with another by date (2003)

    Thanks Hans.

    But it looks like it compares each record against the very 1st-ever start date and the current job.

    What it actually needs to do is compare the 1st-ever-start date against the 2nd job.
    Then compare the 2nd start-date against the 3rd job
    Then compare the 3rd state-date against the 4th job.

    That would work great if I just needed to compare the 2nd, 3rd, and 4th Start Dates against the 1st Start Date, but each iteration needs to look at the previous one.

    I thought that the only way to do this would be to add another Field: End Date for each each Record. Then compare Start-Date against End-Date.

    Would you agree, or is there a way to do this with the existing Fields?

    Thanks again.

    Kind regards

    Brian

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compare 1 Record with another by date (2003)

    Yes you are right.

    I misunderstood your requirements. It also looks like you want to use the current date for comparing the last period?

    I think you will probably need a custom function to do this job. The thread beginning with <post#=576,097>post 576,097</post#> contains a couple of examples of functions that will find the previous record to a specified one. That might help.
    Regards
    John



  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compare 1 Record with another by date (2003)

    If you had an End Date field in each record, then it would be easy to measure how long that period of employment was. Is the end date of one period always the same as the start of the next?

    If there can be a gap between employment periods (and surely that is likely) then you need and EndDate in each record, and that should be used to measure the length.
    What happens if there is some work-experience between two period of employment.

    ie. period 1 full time
    period 2 work-experience
    period 3 part-time

    It does not make much sense to me to compare the start date of period 3 with the start date of period 1.
    But maybe I don't really understand what is going on.
    Regards
    John



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

    Re: Compare 1 Record with another by date (2003)

    Here is a modified version of John Hutchison's database. I think it will do what you want.

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 1 Record with another by date (2003)

    Thanks John. And sorry for saying thanks Hans! So used to Hans' replies!!!!!! Thanks again for your post and help.

    Hans, thanks for jumping in also. I don't see the attachment there?

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

    Re: Compare 1 Record with another by date (2003)

    Oops - sorry about that. Here is the attachment.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compare 1 Record with another by date (2003)

    I still don't think this is right. The duration is attached to the wrong job.

    Instead of looking to the previous record, you should be looking at the next record to find out how long this job lasted for. If there is no next job, then look to today's date.

    I attach a revised demo.

    but I still think there will be gaps in employment, and in this case you need an EndDate for each job.
    Edited by John I have just simplifed the attachment a bit
    Regards
    John



  12. #12
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 1 Record with another by date (2003)

    Thanks guys. Was just about to post back, because there are #Error# messages for all the times when the Previous Date is Null. I entered <> IsNull, but then the final query doesn't run when I try to set criteria for Duration, eg
    >90 days AND <180
    >180 AND <365
    >365

    The Error Message is 'Invalid Use of Null'
    I'll work with John's modified version to see how I go and will post back to let you know. [img]/forums/images/smilies/smile.gif[/img]

    As always, thanks heaps!

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

    Re: Compare 1 Record with another by date (2003)

    To do this, create a new query based on qryDuration, add all fields, and enter the criteria in the Duration column.

    Remark: you can use criteria such as

    Between 90 And 180

    in Access. This is equivalent to "greater than or equal to 90 and less than or equal to 180", i.e. the limits are included.

  14. #14
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 1 Record with another by date (2003)

    Hi Hans

    The same error message comes up regardless of whether I use the > sign or Between?

    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Compare 1 Record with another by date (2003)

    See the qryLimitDuration query in the attached database (based on John Hutchison's latest version).

Page 1 of 2 12 LastLast

Posting Permissions

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