Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Awkward data (2000 SR2)

    Hi, I have a simple query I can't get my head around.

    I have a snapshot of our workforce from abut 6 months ago, and one from now. I need to know the changes that have taken place in that six months.

    Unfortunately the post->person is not a one-to-one relationship. We have a few hundred people in one post number etc. I thought about adding post number to person number to get a unique identifier but I can't get that to produce the output I'd like.

    I basically need to know 1) Where a postholder has changed 2) where a post os no longer used 3) where a new post has been created

    I attach an example of the output I'd like.

    Thanks in anticipation,

    Jim MacLeod
    Shetland Isles

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

    Re: Awkward data (2000 SR2)

    See the attached version. I created 6 queries in total:

    qryMatch returns the records matching on Post_Number, Post_Title and Person_Number.
    qryNonMatchNew and qryNonMatchOld return the records from New and Old that do not match on those three fields.
    qryChangeNew and qryChangeOld return the changes to New and from Old.
    qryChange is a union query that combines the previous two. This is the one you requested.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Awkward data (2000 SR2)

    Perfect, I'm not sure I could have ever worked that one out!

    Thanks for your time,
    Jim

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Awkward data (2000 SR2)

    Hi Hans,

    I've changed PCs and lost the example DB you did for me back in '06 <post:=570,244>post 570,244</post:> .

    I can't get the download to work, is there an archive? or can you re-post.

    Thanks
    Jim

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

    Re: Awkward data (2000 SR2)

    The attachments in this thread were lost when our server crashed last September. Unfortunately, I don't have a copy of the sample database. Could you re-post the database from the first post in this thread?

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Awkward data (2000 SR2)

    Hi,
    Here it is,
    Thanks,
    Jim
    Attached Files Attached Files

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

    Re: Awkward data (2000 SR2)

    And here is the database with the 6 queries. qryChange is the one producing the result you want.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Awkward data (2000 SR2)

    Thank you! That's it safely archived!

    Jim

Posting Permissions

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