Difficult Row Number Needed 1

[Fattire]

I need a function or something that can give me a bucket that increments by 1 at each change in data that is not unique (but it is sorted correctly)... See below, I need to produce the GROUP_ID at every change in invoice date then line_number (notice invoice date is not unique, niether is line number, its just simply sorted correctly...) :


INVOICE_DATE | LINE_NUMBER | AMOUNT | GROUP_ID
----------------------------------------------
30-APR-2010 | 1 | 25 | 1
30-APR-2010 | 2 | 15 | 1
31-MAR-2010 | 1 | 18 | 2
31-MAR-2010 | 2 | 4 | 2
31-MAR-2010 | 3 | 60 | 2
30-APR-2010 | 1 | 9 | 3
30-APR-2010 | 2 | 15 | 3

Any help would be appreciated.

 

[Emtucifor]

There is no such thing as data in a particular order without an order by clause.

It is absolutely not safe to rely on the order that stuff comes out of a table, even in MS Access.

The best you can do at this point is write a function that steps through the rows of the recordset and does the calculation for Group ID.

Going forward, put a Date_Entered column or something in the table so that you can sort and group correctly.

 

[MajP]

You could possibly write a procedure that writes the solution to the table, but I disagree that you could write a function that you could use in a query. And I definitely doubt without a primary key and some sort field that you could do this in SQL. A function is iterative and you would have to pass in the invoice date and line number. The problem is that the function would not know when to exit because it would not know the proper occurence of that date and linenumber. If you had any unique key/s you could do this using a function, but not without one.

The following assumes that there is no unique key, but you have some means of sorting the data. As stated you can not rely on the ordered entered as the order it will come out.

Public Sub writeGroup()
  Dim rs As DAO.Recordset
  Dim tempDate As Date
  Dim tempLineNumber As Integer
  Dim groupID As Integer
  Set rs = CurrentDb.OpenRecordset("qrySorted", dbOpenDynaset)
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    tempDate = rs!invoice_date
    tempLineNumber = rs!lineNumber
    groupID = 1
  End If
  Do While Not rs.EOF
    Debug.Print tempDate & " rsdate " & rs!invoice_date
    Debug.Print tempLineNumber & " rsnumber " & rs!lineNumber
    If tempDate <> rs!invoice_date Or (tempLineNumber > rs!lineNumber And tempDate = rs!invoice_date) Then
      groupID = groupID + 1
      tempDate = rs!invoice_date
      tempLineNumber = rs!lineNumber
    End If
    rs.Edit
      rs!groupID = groupID
    rs.Update
    rs.MoveNext
  Loop
End Sub

 

[Fattire]

Well done MajP... I still don't understand how it works yet - its coming slowly, but after I put in an autonumber pk it worked perfectly.

Private Sub CMD_GROUP_Click()

  Dim rs As DAO.Recordset
  Dim con1 As DAO.Database
  Dim tempDate As Date
  Dim tempLineNumber As Integer
  Dim groupID As Double
  
  Set con1 = CurrentDb()
  Set rs = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)
  
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    tempDate = rs!INVOICE_DATE
    tempLineNumber = rs!LINE_NUMBER
    groupID = 1
  End If
  
  Do While Not rs.EOF
    'Debug.Print tempDate & " rsdate " & rs!INVOICE_DATE
    'Debug.Print tempLineNumber & " rsnumber " & rs!LINE_NUMBER
    If tempDate <> rs!INVOICE_DATE Or (tempLineNumber >= rs!LINE_NUMBER And tempDate = rs!INVOICE_DATE) Then
      groupID = groupID + 1
      tempDate = rs!INVOICE_DATE
      tempLineNumber = rs!LINE_NUMBER
    End If
    rs.Edit
      rs!GROUP_ID = groupID
    rs.Update
    rs.MoveNext
  Loop
  
  rs.MoveFirst
  
  Do While Not rs.EOF
    INS = "INSERT INTO [TBL_GROUPED] values('" & rs!PK & "','" & rs!INVOICE_DATE & "','" & rs!LINE_NUMBER & "','" & rs!LINE_AMOUNT & "','" & rs!GROUP_ID & "')"
    con1.Execute INS
    rs.MoveNext
  Loop

End Sub

 

[PHV]

What is the SQL code of Query1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fattire]

SELECT GL_DATA.PK, GL_DATA.[Invoice Date] AS INVOICE_DATE, GL_DATA.[Line Number] AS LINE_NUMBER, GL_DATA.GROUP_ID, GL_DATA.[Line Amount] AS LINE_AMOUNT
FROM GL_DATA
ORDER BY GL_DATA.PK;

 

[MajP]

If you have an autonumber field, you could do the whole solution in SQL. It would take me a while to figure it out, but someone like PHV can knock it out quickly.

 

[MajP]

OK, here is my SQL solution but I had to do in in parts.

qryRank to get a rank order for the records

SELECT 
 A.Invoice_Date, 
 A.lineNumber, 
 A.PK, 
 (Select count(PK) from table1 where A.PK > table1.pk) AS Rank
FROM 
 Table1 AS A;

Now use this to check the previous record for either a change in the date or possibly the date staying the same but the line number not increasing. This returns just the records where a group change occurs.
qryGroupChange

SELECT 
 A.Invoice_Date, 
 A.lineNumber, 
 A.PK, A.Rank, 
 B.Rank
FROM 
 qryRank AS A, 
 qryRank AS B
WHERE (((A.Invoice_Date)<>[B].[invoice_Date]) AND ((A.Rank)=[B].[Rank]+1)) OR (((A.Invoice_Date)=[B].[invoice_Date]) AND ((A.lineNumber)<[B].[lineNumber]) AND ((A.Rank)=[B].[Rank]+1));
[code]

now use the above to count the changes
[code]
SELECT 
 Table1.PK, 
 Table1.Invoice_Date, 
 Table1.lineNumber, 
 (Select count(qryGroupChange.PK)+1 from qryGroupChange where table1.PK > ([qryGroupChange].PK )) AS [Group]
FROM 
 Table1
ORDER BY Table1.PK;

It ain't pretty but seems to work.

 

[PHV]

Whatb about this ?

SELECT A.PK, A.[Invoice Date], A.[Line Number], A.[Line Amount]
, (SELECT Count(*) FROM (SELECT PK FROM GL_DATA WHERE [Line Number]=1) R WHERE R.PK <= A.PK) AS GROUP_ID
FROM GL_DATA A
ORDER BY A.PK;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886