Assign a serial number for each group

5 pts.
Tags:
Microsoft Access 2002
SQL 2000
I want to assign a serial number for the below data using the Access query or by using SQL for Access     DateItems Sold2/1/20135462/1/20138975/1/20132565/1/201310325/1/20137858/2/20136548/2/20131211   I want the output like as below: S.noDateItems Sold12/1/201354622/1/201389715/1/201325625/1/2013103235/1/201378518/2/201365428/2/20131211   Can anyone help me?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Odd, this was formatted neatly this morning.   The reason you haven't gotten a response is because what you are trying to do is rather complicated in SQL
    49,750 pointsBadges:
    report
  • philpl1jb
    Sorry, I tried to repost your request but the code window won't work.
    49,750 pointsBadges:
    report
  • TomLiotta
    Access does provide some reporting functions beyond SQL; and since this seems to be a reporting issue, it can probably be done. Look at the second and third examples in the Sequential Numbering topic of one Access Wiki on the internet. Try the techniques to see if they work for you. If you have trouble, show your code and we'll see what might fix it. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    can there be duplicates?  Here is the question as it originally appeared:
    I want to assign a serial number for the below data 
    using the Access query or by using SQL for Access     
    
    Date		Items Sold
    2/1/2013	546
    2/1/2013	897
    5/1/2013	256
    5/1/2013	1032
    5/1/2013	785
    8/2/2013	654
    8/2/2013	1211   
    
    I want the output like as below: 
    
    S.no	Date	Items Sold
    1	2/1/2013	546
    2	2/1/2013	897
    1	5/1/2013	256
    2	5/1/2013	1032
    3	5/1/2013	785
    1	8/2/2013	654
    2	8/2/2013	1211   
    
    Can anyone help me? 
    
    49,750 pointsBadges:
    report
  • philpl1jb
    Here is a rough draft -- give it a try.  It is susposed to go through the file putting the highest number from the set + 1 into the field. 
    Update myfile
    
    set [S.No] = (Select b.max([S.No]) + 1 
                from myfile b where b.[Date] = [Date])
    
    Order by [Date], [Item Sold]
    49,750 pointsBadges:
    report
  • philpl1jb
    Little tiny code window
    
    
    Update myfile
    
    set [S.No] = (Select max(b.[S.No]) + 1
    
    from myfile b where b.[Date] = [Date])
    
     
    Order by [Date], [Item Sold];
    49,750 pointsBadges:
    report
  • philpl1jb
    My logic requires a new column in the table for this number.
    49,750 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following