how to add counter to a field based on the instance of appearance (2 Viewers)

[varadha_72]

I am a reports developer and never worked with Access, but got hands on the new project to create an access report with sql server db.

Background:
I have a table which returns 4 fields- Name, rows, exception and rundate.

Sample DB Data-
Filename RowCt ExceptionCt RunDate
A 10 0 11/11/2014 18:01:00 PM
B 20 20 11/11/2014 18:02:00 PM
B 30 10 11/11/2014 18:03:00 PM
B 30 10 11/11/2014 18:04:00 PM
C 15 0 11/11/2014 18:05:00 PM
C 15 0 11/11/2014 18:06:00 PM
D 10 7 11/11/2014 18:07:00 PM
D 5 5 11/11/2014 18:08:00 PM
E 0 0 11/11/2014 18:09:00 PM

Required Output-
Filename RowCt ExceptionCt RunDate Counter SortOrder
A 10 0 11/11/2014 18:01:00 PM 1 1
B 20 20 11/11/2014 18:02:00 PM 1 2
B 30 10 11/11/2014 18:03:00 PM 2 3
C 15 0 11/11/2014 18:05:00 PM 1 4
D 10 7 11/11/2014 18:07:00 PM 1 5
D 5 5 11/11/2014 18:08:00 PM 2 6
E 0 0 11/11/2014 18:09:00 PM 1 7

Note- in the output-
1. When the name appears twice or more in output data then compare Name, Rows, Exception ONLY in subsequent occurrence and check if they are same, if same then display only the first instance.
3. If first instance is different and 2nd and 3rd are same then display first and second rows ONLY.
4. If different then display all.
5. Show counter based on the time in runDate when there is more than one instance/record for Name (based on #1 and #2)
6. Show sortOrder based on Name (user defined sort order)

Story so far-
1. I created a linked table from SQL Server to Access.
2. Then I created a query to get sortorder, show previous day (reqmt) and time after 4pm.
3. I also tried below query, but seems to be throwing "Enter File Name" prompt.
SELECT *,
(SELECT Count(*)+1 AS Counter FROM DBO_SOURCE_RUN_DATA AS QueryA
WHERE QueryA.FileName=DBO_SOURCE_RUN_DATA.FileName
AND QueryA.RunDate<DBO_SOURCE_RUN_DATA.RunDate
AND RDBO_SOURCE_RUN_DATA.RunDate between Date()-1 and Date() AND DBO_SOURCE_RUN_DATA.result=0
ORDER BY DBO_SOURCE_RUN_DATA.filename, DBO_SOURCE_RUN_DATA.RunDate) AS Rank
FROM DBO_SOURCE_RUN_DATA;

Questions-
1. Now I need to display counter. How should I query in Access to get to show first instance as 1 and second as 2 so on…(based on #3)
2. After I get the query fixed from above, then I need to populate the data into a table. (Note sure of this, this can be linked such that a change in the query can change data here)
3. Each time the query is executed, it needs to first delete the existing data in the table and then update the table with new query output.

Please guide me here.