• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Sequencing existing data attributes based upon unique Account Number

I query data, creating a data set having four data fields,

Account Number
Activity Code
Activity Sequence
Closed Date

Each Account Number may have multiple Activity Codes. For each Account Number and Closed Date, I want to populate the Activity Sequence field and increment that by 1 until I get to the next Account Number/Closed Date; then start with 1 and increment until the account number/closed date changes.

I know this question has likely been answered many times but when I query the archive, I could not find one that addressed my data structure specifically.

Thanks
0
ergenbgr
Asked:
ergenbgr
  • 3
  • 3
  • 2
1 Solution
 
aikimarkCommented:
1. please post a sample table in a database
2. Does your table have an autonumber field?
0
 
dsackerContract ERP Admin/ConsultantCommented:
Hi ergenbgr,

Will you be doing an UPDATE or INSERT? Or are you simply deriving the ActivitySequence for only a report or query?

Can you post a few rows that show what the data would look like?

Here is the SELECT statement (without any UPDATE or INSERT), but if you need further, it can be used in either.
SELECT  AccountNumber,
        ActivityCode,
        ClosedDate,
        RANK() OVER (PARTITION BY AccountNumber, ClosedDate ORDER BY ActivityCode) AS ActivitySequence
FROM    YourTable
ORDER BY AccountNumber, ClosedDate, ActivityCode

Open in new window

0
 
aikimarkCommented:
@dsacker

This is an MS Access environment.  I don't think the Rank and Partition features are supported.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ergenbgrAuthor Commented:
Attached is the sample data. See lines 58 and 59 for an example of one account with 2 activities. In this particular data set there is only one record that has a closed date (line number 756) but it has only one activity.
ExampleData2.xlsx
0
 
dsackerContract ERP Admin/ConsultantCommented:
What date do you default to when there isn't one? Or do you want to consider those as "still open", and group them together?
0
 
ergenbgrAuthor Commented:
They would be still open. I will take the results from this data set and match the Name/Address table on Account Number. The final report will show Name, Address and Date the Business Activity began. If it has not closed, the Activity Codes will be listed in one column, separated by a comma. If it has closed, it will show the Date Closeed for the activity.

Make sense or just confused the issue?
0
 
dsackerContract ERP Admin/ConsultantCommented:
See if this gets you closer (change the table name and fields accordingly):
SELECT t1.AccountNumber,
       t1.ActivityCode,
       t1.ClosedDate,
      (SELECT COUNT(t2.ActivityCode)
       FROM   Table1 AS t2
       WHERE  t2.AccountNumber = t1.AccountNumber
       AND    Nz(Format(t2.ClosedDate, "yyyymmdd"), "NONE") = Nz(Format(t1.ClosedDate, "yyyymmdd"), "NONE")
       AND    t2.ActivityCode < t1.ActivityCode) + 1 AS MyRank
FROM   Table1 AS t1
ORDER BY t1.AccountNumber, t1.ClosedDate, t1.ActivityCode;

Open in new window

0
 
ergenbgrAuthor Commented:
Worked perfectly. Thanks for the assistance!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now