Sequentially number sub set query
I have a POS system that has a table named "History" that logs information on each sale. It is not totally normalized. It has a [Transaction] field and a [product code] field. There can be 1 to many records with the same [Transaction]. I need to create a query that will create a number for each record that starts at 1 for each unique [transaction] value and increments for each additional record with the same transaction.
Example sample data:
Transaction Product code
1 bell
1 swing
1 food
2 crickets
3 food
3 food
Desired Output:
Transaction Product code LineNum
1 bell 1
1 swing 2
1 food 3
2 crickets 1
3 food 1
3 food 2
Is there a simple query that can do this?
Example sample data:
Transaction Product code
1 bell
1 swing
1 food
2 crickets
3 food
3 food
Desired Output:
Transaction Product code LineNum
1 bell 1
1 swing 2
1 food 3
2 crickets 1
3 food 1
3 food 2
Is there a simple query that can do this?
try this query,
btw, do you have an autonumber id field?
select Transaction,[Product code], (select count(*) from History as H where H.Transaction=History.Tran
from History
Order by History.transaction
btw, do you have an autonumber id field?
select Transaction,[Product code], (select count(*) from History as H where H.Transaction=History.Tran
from History
Order by History.transaction
ASKER
1) The program I run is compiled code and i can not change it. I can only make fixes because I have back door access to the data.
2) the two records have the same linenum because they are different transactions. think of linenum as a subtable key embedded in a flat file.
3) that is essentially what i am creating
2) the two records have the same linenum because they are different transactions. think of linenum as a subtable key embedded in a flat file.
3) that is essentially what i am creating
The function RowCounter is exactly for this and very fast too.
Study the in-line comments for typical usage.
Also not that parameters are strings. No problem for your product code but the ID may need to be wrapped as shown in CStr:
Study the in-line comments for typical usage.
Also not that parameters are strings. No problem for your product code but the ID may need to be wrapped as shown in CStr:
Public Function RowCounter( _
ByVal strKey As String, _
ByVal booReset As Boolean, _
Optional ByVal strGroupKey As String) _
As Long
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
' SELECT RowCounter(CStr([ID]),False) AS RowID, *
' FROM tblSomeTable
' WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
' SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
' FROM tblSomeTable
' WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
' Call RowCounter(vbNullString, False)
' 2. Run query:
' INSERT INTO tblTemp ( RowID )
' SELECT RowCounter(CStr([ID]),False) AS RowID, *
' FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
' INSERT INTO tblTemp ( RowID )
' SELECT RowCounter(CStr([ID]),False) AS RowID, *
' FROM tblSomeTable
' WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.
Static col As New Collection
Static strGroup As String
On Error GoTo Err_RowCounter
If booReset = True Then
Set col = Nothing
ElseIf strGroup <> strGroupKey Then
Set col = Nothing
strGroup = strGroupKey
col.Add 1, strKey
Else
col.Add col.Count + 1, strKey
End If
RowCounter = col(strKey)
Exit_RowCounter:
Exit Function
Err_RowCounter:
Select Case Err
Case 457
' Key is present.
Resume Next
Case Else
' Some other error.
Resume Exit_RowCounter
End Select
End FunctionASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
capricorn1
the query is giving me the count of number records each transaction has not an ordinal number.
results are like:
Transaction Product code LineNum
1 bell 3
1 swing 3
1 food 3
2 crickets 1
3 food 2
3 food 2
the query is giving me the count of number records each transaction has not an ordinal number.
results are like:
Transaction Product code LineNum
1 bell 3
1 swing 3
1 food 3
2 crickets 1
3 food 2
3 food 2
PetGuy,
did you see my last post?
.
did you see my last post?
.
ASKER
capricorn1
History does not have an ID field
History does not have an ID field
too bad..
can you import the records to an access table, then create an autonumber field?
can you import the records to an access table, then create an autonumber field?
ASKER
The table is in access. I can add fields without breaking the original program. I will add an auto id field.
ASKER
Thanks so much for the help
Then why not normalize it...?
2. Why do these two records have the same LineNum?:
2 crickets 1
3 food 1
3. In a larger since this would be made easier to "manipulate" if you had defined primary key fields in your table...