how to add counter to a field based on the instance of appearance

[varadha_72]

Hi Access World,

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-

Name	Rows	Exception	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-

Name	Rows	Exception	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 2^nd and 3^rd 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.

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.

[June7]

This is ranking by group. A subquery is one way http://allenbrowne.com/ranking.html

Or DCount:

SELECT [Name], Rows, Exception, RunDate, DCount("*","Table2","[Name]='" & [Name] & "' And RunDate<#" & [RunDate] & "#")+1 AS Seq FROM tablename;

Be aware, domain aggregates used like this can perform slowly.

Name is a reserved word. Should avoid reserved words in naming convention.

Why do you need sort order calc? Just use ORDER BY in query to sort by Name or whatever fields you want.

[ssanfu]

Note: Rows & Exception are also reserved words.....

[June7]

Those two don't show up as reserved on list and creation in table did not give warning like Name does.

[ssanfu]

I used Allen Browne's list: http://www.allenbrowne.com/AppIssueBadWord.html

ROWS	JET reserved (kb248738);ODBC (kb125948);ANSI-92 Reserved (kb287417)
EXCEPTION	JET reserved (kb248738);ODBC (kb125948);ANSI-92 Reserved (kb287417)

[June7]

Okay, different lists. Table builder didn't care.

[ssanfu]

In the past I've used reserved words as field names without any complaints. Now I try and use a prefix to avoid having to go back and try to fix field names.... I'm more into "Do it right the first time" now.. (aka lazy)

[varadha_72]

Thanks for the reply. I am not sure of how to lay your query you suggested, please suggest. please find below the query I used.
Also I have renamed the field names row to row_count and exception to exception_count.
Please find below my query,

select a.file_name,a.row_count, a.exception_count, count(*) as counter
from
(SELECT file_name, row_count, exception_count
FROM
DBO_SOURCE_RUN_DATA
where
run_date between (Date()-1) and (Date())
AND result=0
group by file_name, row_count, exception_count) AS A, dbo_source_run_data srd
WHERE
A.file_name=srd.file_name
AND srd.run_date between (Date()-4) and (Date()-3)
AND srd.result=0
group by a.file_name, a.row_count, a.exception_count
ORDER BY a.file_name;

But this query is not returning what I was looking for.
For Eg- Output Seen

Name	Row_count	Exception_count	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	2	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	2	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

Expected Output- I need to get B and D as 1 and 2 respectively for each row.

[June7]

You don't need the GROUP BY query because you are not summarizing any data.

SELECT *,
(SELECT Count(*)+1 AS HowMany FROM DBO_SOURCE_RUN_DATA AS Dupe
WHERE Dupe.Name=DBO_SOURCE_RUN_DATA.Name AND Dupe.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.name, DBO_SOURCE_RUN_DATA.RunDate) AS Rank
FROM DBO_SOURCE_RUN_DATA;

As previously noted, Name is also a reserved word, as you should have seen when creating the field.

[varadha_72]

Hi,
I tried the below query you suggested in Access, but access is prompting each time for dupe.filename. I am not sure why its not picking the filename from the table and why I am being prompted for. Please advise.

SELECT *,
(SELECT Count(*)+1 AS HowMany FROM DBO_SOURCE_RUN_DATA AS Dupe
WHERE Dupe.Name=DBO_SOURCE_RUN_DATA.Name AND Dupe.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.name, DBO_SOURCE_RUN_DATA.RunDate) AS Rank
FROM DBO_SOURCE_RUN_DATA;

[June7]

Did you mean: dupe.fieldname?

Make sure field names are correctly spelled.

[varadha_72]

I tried the above query its not working. I am still getting duplicate records. Please help.

[June7]

It works for me with the sample data provided. If you want to provide db for analysis, follow instructions at bottom of my post.