Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sequentially number sub set query

Posted on 2013-02-05
11
Medium Priority
?
459 Views
Last Modified: 2013-02-05
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?
0
Comment
Question by:PetGuy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38855769
1. <It is not totally normalized. >
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...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38855836
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.Transaction) as LineNum
from History
Order by History.transaction
0
 

Author Comment

by:PetGuy
ID: 38855852
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
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38855858
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:

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 Function

Open in new window

/gustav
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38855867
btw, do you have an autonumber id field?

select Transaction,[Product code], (select count(*) from History as H where H.Transaction=History.Transaction and H.id>=History.ID) as LineNum
from History
Order by History.transaction
0
 

Author Comment

by:PetGuy
ID: 38855911
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38855930
PetGuy,

did you see my last post?





.
0
 

Author Comment

by:PetGuy
ID: 38856030
capricorn1

History does not have an ID field
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38856043
too bad..

can you import the records to an access table, then create an autonumber field?
0
 

Author Comment

by:PetGuy
ID: 38856116
The table is in access. I can add fields without breaking the original program. I will add an auto id field.
0
 

Author Closing Comment

by:PetGuy
ID: 38856414
Thanks so much for the help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question