Count number of record to first occurence of a value

[zx3]

Hi all,

Here's a problem i m working on,

emp	date	hour	value
B1	1feb	16	D
A1	11feb	8	E
A1	6feb	3	A
A1	7feb	10	B
A1	6feb	4	B
A1	8feb	23	C

I need a query to count the number of records from the table top (latest date and hour after sorting) to get to value "A". The result should be 5 after sorting the date time by descending order as below, please help.

emp	date	hour	value
A1	11feb	8	E
A1	8feb	23	C
A1	7feb	10	B
A1	6feb	4	B
A1	6feb	3	A
B1	1feb	16	D

[June7]

That date field is not showing as a date/time value. It looks like text and will not sort chronologically.

[zx3]

Sorry about the confusion, it should be in date format like 1-Feb-2014, but to make a miniature of my real table for posting purpose, i did simply the date and the time only show the hours in 24 hours format.

[June7]

Don't really understand the requirements. You want to count how many "A1" records there are? Why A1?

[zx3]

I actually want to get the position of "A" in "value" column of the sorted table, the 2nd table show the sorted version of the 1st, the date time is sorted in descending order and is grouped by emp, and the emp column is the employee name.

On the 2nd table, the date time is latest on the top and earliest on the bottom, i need to know how many lines/records i have to go back from the latest date time to get to the line/record that has an "A" in the "value" column. So for emp A1, i know by counting from the top the 5th line will have an "A", for emp B1, the result will be nil(or whatever show no matches) cause there is no line/record matches the above criteria, and i want a query to do the job for each emp, please help.

[June7]

So if there were a record: B1 7feb 9, what would be desired result - 2?

Perhaps if you explained why you need this, could better advise.

[trevor40]

You may need to use vba and loop through a recordset looking for the result
Air code untested !!
eg very basic sample, you will need to fix it up but gives the general idea

Set rs = db.OpenRecordset("SELECT * FROM your_table ORDER BY Value_field")
rs.movefirst
Do While Not rs.value_filed not me.text1 ' text1 holds the value you are looking for
count_lines=count_lines+1
rs.MoveNext
Loop
me.tex1 = count_lines ' when it equals it put that value into text1
rs.Close

[zx3]

So if there were a record: B1 7feb 9, what would be desired result - 2?

Perhaps if you explained why you need this, could better advise.

if there is an "A" on that line in the "value" column you suggest, the expecting result of the query should be

emp	RecCount
A1	5
B1	1

June7, thanks for the patience and interest.

[June7]

All that's doing is counting records by group. That is an aggregate (Totals) GROUP BY query. Access Help has info on using the query builder to build aggregate query.

SELECT EmpID, Count([Date]) AS CountOfEmp FROM tablename GROUP BY EmpID;


Or build a report and use its Grouping & Sorting features with aggregate calcs in group footer.

[zx3]

June7, How about the value "A", Or if the sorted table changed to

emp	date	hour	value
A1	11feb	8	E
A1	8feb	23	C
A1	7feb	10	B
A1	6feb	4	B
A1	6feb	3	A
B1	7feb	9	A
B1	1feb	16	B

and the criteria change to 1st B value from the top for each emp, the result will be as following

emp	RecCount
A1	3
B1	2

please help.

[June7]

See trevor40 suggestion in post 7.

[zx3]

If there is no easy way to do it using sql queries then i will try vba like trevor40 suggested, thanks everyone for their contribution.