Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    10

    Question Unanswered: Newbie needs more help with an experssion

    With some great help from Teddy and M Owen the other day I was able to formulate a query to return the name value of an employee that was in a position number today and not worry about someone that was in there last month, thus negating any duplications of the position number itself.

    So I created a report for my boss and we realized that now our reports are not showing the seasonal employees starting next month (because they are not in the positon today) and I also foresee the same problem happening when our summer students return to school in a couple of weeks. So my SQL looks like this:

    SELECT [Position Table].[Cost Centre], [Position Table].[Depart Name], [Position Table].[Depart #], [Position Table].[PUN #], [Position Table].[Position Category], [Employee Table].[Staff Category ], [Employee Table].Name, [Position Table].[Position #], [Position Table].[Position Title], [Position Table].Class, [Position Table].Notes, [Base Salary Query].[Forecasted Salary], [Position Table].[Position Count]
    FROM [Base Salary Query] INNER JOIN ([Employee Table] INNER JOIN [Position Table] ON [Employee Table].[Position #] = [Position Table].[Position #]) ON ([Base Salary Query].Name = [Employee Table].Name) AND ([Base Salary Query].[Position #] = [Position Table].[Position #])
    WHERE (((Date()) Between [Start Date] And [End Date]))
    GROUP BY [Position Table].[Cost Centre], [Position Table].[Depart Name], [Position Table].[Depart #], [Position Table].[PUN #], [Position Table].[Position Category], [Employee Table].[Staff Category ], [Employee Table].Name, [Position Table].[Position #], [Position Table].[Position Title], [Position Table].Class, [Position Table].Notes, [Base Salary Query].[Forecasted Salary], [Position Table].[Position Count], [Position Table].[Position Status]
    HAVING ((([Position Table].[Position Status])<>"LTIP"));

    The field I think I need to mess around with is [Position Table].[Position Category] which will return values of Classified, Unclassified, Seasonal or Student. Now the Classified and Unclassified values are the ones I want to stay affected by the current querie, however I need a solution to bring in the Seasonal and Student categories unaffected. Is this possible and what is the best way to resolve this?

    Thanks in advance my gurus' of database expertise!

    David

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Can you provide one sample set of records and the result you're trying to achieve?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2006
    Posts
    10
    Thanks for the response Teddy,

    There lies a bit of a problem...I work for the government and I am not allowed to release any information that is considered confidential, which this whole database is. I can tell you that without the where clause I return 523 results, which is sounds right for the information given. With the Where clause in I return 393 results, which also sounds correct as of today.
    There are 43 missing seasonal employees who do not start until the fall-winter and we require them to be returned by this query. Now to try and make up a record sampe would Somewhat look like this:

    Position Category Name Position # Start Date End Date
    Classified Zanzoul,Ghassan 147981 01-Apr-06 11-Aug-06
    Classified Zhu,Shuqiang 147981 12-Aug-06 31-Mar-07
    Seasonal Bedard,Patrick 87973 01-Nov-06 31-Mar-07

    Now the resust would not show The first record, because she finished Aug 11th, it would show Shuiang instead (the whole where clause was installed because I wanted to only show the person in the position and not to return two results for one position number. However, in doing I cut out people like the third record in which Patrick will not start until Nov. 1st, but we still need to show his position in the query. So ther result would look like this:

    Position Category Name Position # Start Date End Date
    Classified Zhu,Shuqiang 147981 08-Aug-06 31-Mar-07
    Seasonal Bedard,Patrick 87973 01-Nov-06 31-Mar-07

    Thanks for your help Teddy, I'm sorry I can't explain more technically what I want it to do.

  4. #4
    Join Date
    Aug 2006
    Posts
    1

    This may help

    I think I may understand what you are trying to do. So without going into awhole lot of jibberish this is what I think:

    Recommended solution: Instead of basing your query on date() between x and y, try basing it instead on date() lessthan or equal to enddate.

    Rationale: If the start/end dates of your seasonal employees are always projected into your database then it stands to follow that their end dates would always be greater than today's date. For that matter, if you based your entire query on date()<=enddate, you also would not have to worry about more than one name coming up for the same position. To put it another way, only the people who are in active positions today have enddates that are greater than today's date.

    Hope this Helps

  5. #5
    Join Date
    Aug 2006
    Posts
    10

    Thank you for the response GMP

    I understand you logic and agree with it, and that would fix half of my problem concerning the winter seasonals starting in Oct, Nov and Jan....however that would also filter out the summer seasonals ending in Aug, Sept and Oct. But I see and agree with your approach.

    It is almost as if I want to create some sort of conditional filter where it would say:

    "if the position category is classified or unclassified, then take the name that is in the position # today. If the position category is seasonal or student (which there should never be two people in the same position #) than list all names and position #'s regarless of what the dates are."

    I've tried messing around with the Iif function and the Where clause in sql.

    Thanks again for your feedback.

Posting Permissions

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