Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I think I need nested loop or maybe not 2

Status
Not open for further replies.

teqtaq

Technical User
Nov 18, 2007
197
US
I think I need nester loop which I had never written before. Please, help me to decide.

I have only one table. Like illustrated below with data as shown.

DATE___________ID____SalaryCode_____ACTIONcode
11/11/2005 --12345-----010------------HIRED
11/11/2005 --12345-----010------------PROMO
11/11/2005 --12345-----020------------REVIEW


So I am thinking I am getting a loop like
loop until ACTION Code='PROMO' (note when this condition is met what date there (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 changed to a greater one.

DATE___________ID____SalaryCode_____ACTIONcode
11/11/2005 --12345-----010------------HIRED
11/11/2005 --12345-----010------------PROMO
11/11/2005 --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!
 
Please explain. Your data only shows dates of
11/11/2005

11/11/2005 --12345-----010------------HIRED
11/11/2005 --12345-----010------------PROMO
11/11/2005 --12345-----010------------REVIEW

but you talk about 2006 and 2008. I do not understand.

? loop until ACTION Code='PROMO' (note when this condition is met what date there (11/11/2006 in my example)

? However in example below result will be 11/11/2008 ---12345---010--REVIEW
 
Either way, DO NOT WRITE CODE TO DO THIS. Do this using a query, but you will have to have a better example and explanation of the rule.
 
A starting point (SQL code):
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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
So sorry.
It is different years.

DATE___________ID____SalaryCode_____ACTIONcode
11/11/2005 --12345-----010------------HIRED
11/11/2006 --12345-----010------------PROMO
11/11/2007 --12345-----020------------REVIEW

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 - if it is - the result is NULL. If it is same or less - it is a mistake.

Example:
11/11/2005 --12345-----010------------HIRED
11/11/2006 --12345-----010------------PROMO
11/11/2007 --12345-----010------------REVIEW

In this case mistake is in following row.
11/11/2007 --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 PROMODATE (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.
End!

I hope I cleared it a little. Thanks
 
Again there is no reason to write code. It can be done but it will
1. Be harder to do
2. Slower
3. Less flexible
Please look at PHVs suggestion.
 
PHV, I have only one table but you suggesting 2.
Unless you meant I need to take anothe rinstance of the same table? I will ty. Thanks
 
I had tried PHVs suggestion but SQL is foltering ROMOs only
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

I need to find PROMO. Note the Date of PROMO and find greater date that is not PROMO but anything else and check the salarycode.
 
but SQL is foltering ROMOs only
???
What is YOUR SQL code ?
Mine should show the mistakes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
What is A and B tables are? I have one table only, is the B another instance of an A? Thanks
 
Yes you are joining the table to itself. You give the table an alias for each instance. A is the first instance, B is the second instance.
 
This quesry showing me all the records that are greater than one after the PROMO.
Like I said in original post:
...
loop to find greater date (just one - next after 11/11/2006) and see if SalaryCode is greater then the one for 'PROMO'.

I would need a PROMO and next ONE.
Thanks I am trying to play with given query, maybe I will also figure something out. Thanks
 
WAIT! I think it is working! I don't know why and how but I will definitely figure it out!

Thanks for the awesome lesson!
 
All right,
now I am bataling a following issie - more than one date that is greater than PRO's date like following:

DATE___________ID____SalaryCode_____ACTIONcode
11/11/2005 --12345-----010------------HIRED
11/11/2006 --12345-----010------------PROMO
11/11/2007 --12345-----010------------REVIEW
11/11/2008 --12345-----010------------REVIEW

I am sorry this data gets just dumped on me and I have about no time to get familiar with it - I am just writing audits as I go. This was just uncovered for a few records along with more than 1 PROMO which I think will be working fine except when more then 1 date after the PROMO Date.

Thanks again

P.S. I am looking for the function like Date+1 or something...am I wrong?

 
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=DateAdd('yyyy',1,A.Date)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
All right, it is not going to work and I am asking to come back to my original solution.

If I can write loop that will loop trough the recordset and when PROMO found - it will read Date and Salary Grade and will loop looking for the next date and check Salary grade to be greater. If not greater - write into the table.

Thanks

 
PHV,
I had realized my mistake.
The Date wouldn't be the Date+1 - it must be a date that next on the list. And sometimes there is a year or 5 years or 8 years gap.
So I need to read next date. Any way?
 
If I can write loop that will loop trough the recordset and when PROMO found - it will read Date and Salary Grade and will loop looking for the next date and check Salary grade to be greater. If not greater - write into the table

Yes it is very simple to write the loop. All you have to do is

1. Instantiate 2 recordset
2. read through the first one until you find Promo
3. save the ID, date, salary code, and action code
4. Move to the next record if not at the end of file
5. Compare the current record to all of your temp values
6. check to see if the current record has the same ID, and has the same salary code
7. write to your output table
8. move back one record
9. find the next Promo
10. exit if no more Promo are found.

The above seemed so trivial but we kept on suggesting suggesting a query just for fun. So here it is.

Code:
Public Sub notPromoted()
  Dim rs As DAO.Recordset
  Dim rsOutput As DAO.Recordset
  Dim tempcode As String
  Dim tempID As Integer
  Dim tempDate As Date
  Dim tempSalaryCode As String
  Set rs = CurrentDb.OpenRecordset("qrySorted", dbOpenDynaset)
  Set rsOutput = CurrentDb.OpenRecordset("tblProblems", dbOpenDynaset)
  
  rs.MoveFirst
  
  Debug.Print rs.NoMatch
  Do While Not rs.EOF
     rs.FindNext "actionCode = 'Promo'"
     If rs.NoMatch Then Exit Do
     tempID = rs.Fields("ID")
     tempcode = rs.Fields("actionCode")
     tempDate = rs.Fields("dtmDate")
     tempSalaryCode = rs.Fields("SalaryCode")
     If Not rs.EOF Then rs.MoveNext
     If tempID = rs.Fields("ID") And (tempSalaryCode = rs.Fields("SalaryCode")) Then
        rsOutput.AddNew
          rsOutput.Fields("ID") = tempID
          rsOutput.Fields("dtmDate") = tempDate
        rsOutput.Update
        rs.MovePrevious
      End If
  Loop
End Sub
 
MajP!
You had made my New Year!
Thanks so much. I will make sure I read it and understood all it does. Thanks and Happy New Year to you and yours from the bottom of my heart!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top