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

    Expression Assistance Required (97:SR2)

    I'm having difficulty with writing a query expression which will reflect the department where the EE was last year.

    As an example the main DB contains the following field/data: Note! Frank was in a different department last year and Mary is a newhire.

    <table border=1><td>Period</td><td>Name</td><td>Dept</td><td>0902</td><td>Mary</td><td>25</td><tr><td>0902</td><td>Frank</td><td>16</td><tr><td>0902</td><td>Jill</td><td>21</td><tr><td>0901</td><td>Mary</td><td> </td><tr><td>0901</td><td>Frank</td><td>12</td><tr><td>0901</td><td>Jill</td><td>21</td></table>
    I have two queries "Curr and Prior" which extract the appropriate periods. In this case "0902 and 0901" respectively. A third query has the main DB joined with the two previously mentioned queries retrieving unique records. My query field/table selections are Period/MainDB, Name/MainDB, Dept/MainDB. At this point I believe I need an expression to obtain the prior year department.

    The returned data would look something like this.

    <table border=1><td>Period</td><td>Name</td><td>DeptCur</td><td>DeptPrior</td><td>0902</td><td>Mary</td><td>25</td><tr> <td>0902</td><td>Frank</td><td>16</td><td>12</td><tr><td>0902</td><td>Jil</td><td>21</td><td>21</td></table>
    Any assistance is appreciated.

    Thanks,
    John

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Expression Assistance Required (97:SR2)

    All you need do is join the 2 queries on name and you should have all the data you need.
    If I am correct in understanding what you want, the 2 queries would be:

    Curr:
    SELECT MainDB.Period, MainDB.EName, MainDB.Dept
    FROM MainDB
    WHERE (((MainDB.Period)="0902"));

    Prior:
    SELECT MainDB.Period, MainDB.EName, MainDB.Dept
    FROM MainDB
    WHERE (((MainDB.Period)="0901"));

    The query to join these 2 would be:
    SELECT [MainDB Curr].Period, [MainDB Curr].EName AS Name, [MainDB Curr].Dept AS DeptCurr, [MainDB Prior].Dept AS DeptPrior
    FROM [MainDB Curr] INNER JOIN [MainDB Prior] ON [MainDB Curr].EName = [MainDB Prior].EName;

    HIUTP (Hope I understood the Post)
    Pat

Posting Permissions

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