asked on Sequencing existing data attributes based upon unique Account Number
I query data, creating a data set having four data fields,
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
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
Microsoft AccessSQL
Last Comment
Ray Ergenbright
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.
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
@dsacker
This is an MS Access environment. I don't think the Rank and Partition features are supported.
This is an MS Access environment. I don't think the Rank and Partition features are supported.
ASKER
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
ExampleData2.xlsx
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?
ASKER
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?
Make sense or just confused the issue?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Worked perfectly. Thanks for the assistance!!
2. Does your table have an autonumber field?