Hello,
Awhile ago I inherited a handful of VB6 programs that produce financial reports.
These Access databases have a form with some controls like date and check boxes for the reports to generate.
I had added some new accounts in the past and I did part of the work SAS a few years ago...
I have this basic data step that takes my table and creates a new Member ID column and populates the field with a 1 for the first member and adds a +1 to the others if its not the first member in the group by. My data set has many accounts and members so the auto populate feature in Access with auto populate a 1,2,3,4 for 150 rows but I need it to stop and start assigning a 1,2,3,4 for each account or CLIENT_GRP_NAME.
I spent some time trying to build an Expression in Access that would do the same but it was more of a Challenge than converting my data step into Access as I thought.
Thanks in advance!
Steve
CT
SAS FIRST DATA STEP WORKS:
data CW_FINISHED;
SET CW_FINISHED;
'MEMBER ID'N +1;
BY CLIENT_GRP_NAME;
IF FIRST.CLIENT_GRP_NAME THEN 'MEMBER ID'N = 1;
RUN,
NEED AN ACCESS SQL LIKE:
GROUP BY
,
,
,
,
,
HAVING
([CW_FINISHED])=IIF((FIRST[CW_FINISHED]![MEMBER ID], [CW_FINISHED]![MEMBER ID] = 1, [CW_FINISHED]![MEMBER ID] = +1]))