Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looping or else (Access2003)

    I hope it is doable. I need to find all the records with condition PROMO and Date of the PROMO(tion) and Salary Code.
    I have to see that prior to PROMO Date has a lower Salary Code then PROMO.

    Basically when person is promoted - they are getting higher salary code then before.
    Sounds easy. Not so much when I need to code the thing in!

    Thanks for your time! Happy Holidays.

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

    Re: Looping or else (Access2003)

    Could you post a small sample database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    Hello, Hans.

    I could but I am not sure if it is going to do any good.
    Let me try to post piece of data the way it looks. It is one table.

    DATE_____________ID________________SalaryCode_____ _____ACTIONcode
    11/11/2005 -----------12345-----------------------------010---------------------------HIRED
    11/11/2006 -----------12345-----------------------------010---------------------------PROMO
    11/11/2008 -----------12345-----------------------------020---------------------------REVIEW

    All data looks like this. I need to find the ID with PROMO. I need to 'note' the date and find next greater date - 11/11/2008 and make sure that SalaryCode is greater then before. I will have to build conversion table for Salary Codes because those do not make sense in terms of which one is greater. It is text field. But I know how to deal with it. I just don't know how to loop per ID.

    If you still need a database - I will send it - I am as a matter a fact will make one just in case while this message is sent.

    Thanks

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

    Re: Looping or else (Access2003)

    Originally you wrote "I have to see that prior to PROMO Date has a lower Salary Code then PROMO", now you write "I need to 'note' the date and find next greater date - 11/11/2008 and make sure that SalaryCode is greater then before". That is not the same. Could you try to explain clearly and without ambiguity what you want to accomplish?

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

    Re: Looping or else (Access2003)

    In the attached database, I have created four tables, of which tblActions corresponds to the example in your post. The others are lookup tables.
    The query qryTest checks whether there are records for a given ID before a promotion with a higher salary, or after a promotion with a lower salary.
    Does this give you a starting point?
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    Hans, you right, it was the second one but I thought if I have a loop - I can change it and it doesn't really matter.
    I will prove that I need to 'note' the date and find next greater date - 11/11/2008 and make sure that SalaryCode is greater then before".

    Youyr databse is great except I do not have tables to join and manipulate.
    I have only one table. Like illustrated below with data as shown.

    DATE_____________ID________________SalaryCode_____ _____ACTIONcode
    11/11/2005 -----------12345-----------------------------010---------------------------HIRED
    11/11/2006 -----------12345-----------------------------010---------------------------PROMO
    11/11/2008 -----------12345-----------------------------020---------------------------REVIEW

    So I am thinking I am getting a loop like
    i+1 loop until ACTION Code='PROMO' (note when this condition is met what date there is which is 11/11/2006 in my example)
    then
    loop to find greater date (just one - next after 11/11/2006) and see if SalaryCode is greater then the one for 'PROMO'.
    If not - return result so it can be investigated and fixed.

    So in my example there is going to be NULL result.

    However in example below result will be 11/11/2008 -----------12345-----------------------------010---------------------------REVIEW
    because after PROMO(tion) salary code had not chenged to a greater one.

    DATE_____________ID________________SalaryCode_____ _____ACTIONcode
    11/11/2005 -----------12345-----------------------------010---------------------------HIRED
    11/11/2006 -----------12345-----------------------------010---------------------------PROMO
    11/11/2008 -----------12345-----------------------------010---------------------------REVIEW


    I want to say one more time I have only one table with tons of records. I need generic way of running code - fishing out 'bad' once and send them out to a client to investigate.

    Thanks and good week!

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

    Re: Looping or else (Access2003)

    You still need to clarify - your original request was "I have to see that prior to PROMO Date has a lower Salary Code then PROMO". You're now asking something else. What EXACTLY do you want?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    Sorry for being so...difficult.
    Trying again.

    I have only 1 table. When PROMO(tion) had happened - I need to note what year and find a greater year and check salary code. It must be greater (means person got paid more after the PROMOtion) - if it is greater - the result is NULL. If it is same or less - it is a mistake.

    Example:
    DATE_____________ID________________SalaryCode_____ _____ACTIONcode
    11/11/2005 -----------12345-----------------------------010---------------------------HIRED
    11/11/2006 -----------12345-----------------------------010---------------------------PROMO
    11/11/2008 -----------12345-----------------------------020---------------------------REVIEW

    In this case mistake is in following row.
    11/11/2008 --12345-----010------------REVIEW

    After a PROMO in 2006 - salary grade should be 020 not 010.

    I am invisioning a loop.
    MyRs select ActionCode from table where ActionCode='PROMO'
    Or Do While ActionCode = "PROMO"
    then note DATE (11/11/2006) and find greater year for the same ID. Will be 11/11/2007. Check if SalaryCode greater then for the 11/11/2006.
    If greater - move to another ID. If less - write into a table or something.
    End!

    Thanks

    I hope I cleared it a little. Thanks

    mdb attached
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    Well, I have no idea why it is working but it seems does and I am confused:
    SELECT *
    FROM youTable A INNER JOIN yourTable B ON A.ID=B.ID AND A.SalaryCode=B.SalaryCode
    WHERE A.ACTIONcode='PROMO' AND B.Date>A.Date

    Same table joined to itself...I am figuring this out - to see IF it is correct after all. Thanks and IF there is another way - I am all for it.

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

    Re: Looping or else (Access2003)

    This would only return records for which the salary code has remained the same after a promotion.
    It wouldn't return records for which the salary has become lower after a promotion. If the latter isn't possible, then your query is all you need.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    You right as always. I tested it and I must ask you to help me to write that loop.
    If all possible.

    Some have more than 1 PROMO, some have 3 different Action codes in 1 year. Some have whatever else. It is very abnormal data and all I can do (if I could) is to read recordset and when PROMO found - read a Salary and Date and move to the next date.

    Thanks

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

    Re: Looping or else (Access2003)

    Does the code in the module basCode in the attached database do what you want? It writes information to the new table Problems.
    Attached Files Attached Files

  13. #13
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    Hans, you had made my day and New Year! It was something I must had done by the January. I was lost. Now I am going to study your code to make sure I had learned another great way of dealing with data. I will be modifying it for other data audits - so it will be used well.

    Thanks and Happy New Year to you and yours!

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping or else (Access2003)

    Hans,
    I've got 1137 records into the table and than message 'Invalid use of NULL' popped up and when I closed out of ot - it did not come back to the line where the error occured. Is there way to see what had happened and where? Thanks

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

    Re: Looping or else (Access2003)

    The code was written to exit if an error occurs. You could comment out the line

    On Error GoTo ErrHandler

    by inserting an apostrophe ' in front of it. If you run the code again, you'll get a Debug option when the error occurs. You can then inspect the value of fields and variables.
    Do you have records with an empty SalGrade? That would cause the error.

Page 1 of 3 123 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
  •