Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Assistance (XP)

    I am trying to generate a report that compares two different periods in which employees reside ie current and prior periods. It would reflect movement of employee count between different departments.

    As an example:
    Current period: John is in dept 1234, Mary is in dept 5678 and Hans is in dept 9876
    Prior period: John is in dept 1111, Mary is in dept 5678 and Hans is in dept 9876

    My first thought would be to create a new query based on the current and prior periods and then create a report based on this new query. The report should look something like this:

    Dept Current Period Prior Period
    1111
    John 0 1
    1234
    John 1 0
    5678
    Mary 1 1

    This is where I start having issues, I can't seem to get the new query quite right.

    Your assistance would be appreciated.
    John
    Attached Images Attached Images

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

    Re: Query Assistance (XP)

    Not quite sure what you're doing, but you should have TWO copies of the BUnit, Dept and Status tables in there, one set linked to Current Period and the other set linked to Prior Period. As it is now, you cannot display employees who moved from one department to another, since the DeptID's in Current Period and Prior Period are linked through the one copy of Dept, so they must be equal.

    - Delete the links from Prior Period to BUnit, Dept and Status.
    - Click the Show Table button, or select Query | Show Table...
    - Add BUnit, Dept and Status. Access will name the copies BUnit_1 etc.
    - Link Unit in Prior Period to Unit in BUnit_1, etc.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    What happens in the case where there is an employee listed in the prior period and not in the current ie a termininated employee. The query results are misleadling as one would expect to see the employee only for the prior period.

    John
    Attached Images Attached Images

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

    Re: Query Assistance (XP)

    You must return the Unit and Department from both queries to be able to see a difference. But if Andrew in your example is not listed for the current period, how come he has ended up in the query 'Current Period'? Review the design of that query.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    I'm still having issues.

    Table:Current.Period Fields

    <table border=1><td>Unit</td><td>DeptId</td><td>Id</td><td>Name</td><td>Job Descr</td><td>Status</td><td>StatusDescr</td><td>MMYY</td><td>1</td><td>1234</td><td>9999</td><td>John</td><td>aaaa</td><td>P</td><td>Leave with Pay</td><td>204</td><tr><td>2</td><td>5678</td><td>5555</td><td>Mary</td><td>bbbb</td><td>A</td><td>Active</td><td>204</td><tr><td>3</td><td>9876</td><td>8888</td><td>Hans</td><td>cccc</td><td>A</td><td>Active</td><td>204</td></table>

    Table:Prior.Period Fields
    <table border=1><td>Unit</td><td>DeptId</td><td>Id</td><td>Name</td><td>Job Descr</td><td>Status</td><td>StatusDescr</td><td>MMYY</td><td>1</td><td>1111</td><td>9999</td><td>John</td><td>aaaa</td><td>P</td><td>Leave with Pay</td><td>104</td><tr><td>2</td><td>5678</td><td>5555</td><td>Mary</td><td>bbbb</td><td>A</td><td>Active</td><td>104</td><tr><td>3</td><td>9876</td><td>8888</td><td>Hans</td><td>cccc</td><td>A</td><td>Active</td><td>104</td><tr><td>1</td><td>2223</td><td>7777</td><td>Andrew</td><td>dddd</td><td>A</td><td>Active</td><td>104</td></table>

    I'm uncertain where the error is in the query design.

    John
    Attached Images Attached Images

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

    Re: Query Assistance (XP)

    In the screenshot, it seems there is no link at all between Prior Period and Current Period, so you are not matching employees from both.

    What would you like to happen with new employees (present in Current, not in Prior) and former employees (present in Prior, not in Current)? Do you want to exclude both from the result, or include both, or include one type and exclude the other?

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    When I join the current/prior query to ID the returned records are one to one. What I should be seeing is John in the New and Old dept as well as Andrew in the prior period MMYY but not in the current period MMYY. In this case, Andrew was termininated (sorry Andrew) and was not included in the load to the current period. My intent is to trace the coming/going of employees from one dept to another period over period.

    John

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

    Re: Query Assistance (XP)

    You will have to create a union query to select all ID's present in Prior Period and in Current Period. Union queries can only be written in SQL view, not in design view. The SQL for this query is:

    SELECT Id FROM [Prior Period]
    UNION SELECT Id FROM [Current Period];

    Save this query as (say) qryIDs. Then open the query you were working on in design view.
    Remove the join (if any) between Prior Period and Current Period.
    Select Query | Show Table..., activate the Queries tab and add qryIDs. Then click Close.
    Join qryIDs to Prior Period on Id. Double click the join line and select the option to return ALL records from qryIDs, then click OK.
    Join qryIDs to Current Period on Id. Double click the join line and select the option to return ALL records from qryIDs, then click OK.
    Remove the Id field from the query grid, and add the one in qryIDs to the query grid.

    The query should display all ID's now.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    Your suggestion makes sense but I receive the following error message.

    John
    Attached Images Attached Images

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    I tried creating a new query based on your suggestion and I got it to work.

    In the great words of Lily Tomlin's telephone operator character on Rowen & Martin's Laugh-In - "Never Mind"

    Thanks for all your help.
    John

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

    Re: Query Assistance (XP)

    Double click each of the joins between Prior Period and BUnit, Dept and Status (but NOT the one to qryIDs), and select the option to return ALL records from Prior Period.
    Repeat for Current Period.

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    I need your assistance once again. While creating a report to reflect the change/movement of employees I have a challange in properly presenting what dept an employee was in and where they went to. The attachment shows John who in the prior period was in dept 1111 and the current period he is in dept 1234.

    Thanks,
    John
    Attached Images Attached Images

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

    Re: Query Assistance (XP)

    Why would you want to emulate Excel in Access? In my humble opinion, a report based on the query would present the data in a much more comprehensible way.

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Assistance (XP)

    Hans,

    The XL report as depicted in the attachment was provided to demonstrate what the final results in the access report should look like.

    John

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

    Re: Query Assistance (XP)

    That is what I guessed, but why would you want a report like that? It looks cumbersome to me, sorry.

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
  •