Awkward data (2000 SR2)

[jmacleod]

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

[HansV]

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.

[jmacleod]

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

Thanks for your time,
Jim

[jmacleod]

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

[HansV]

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?

[jmacleod]

Hi,
Here it is,
Thanks,
Jim

[HansV]

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

[jmacleod]

Thank you! That's it safely archived!

Jim