Count last 32 entries help please.

[BigMac4]

Hi everyone,
Got what might be a simple problem, here is what i have. A table with dates in column 1 and grade in column 2 what i need to do is count how many "pass" grades are in column 2 within the last 32 entries (not dates) in the date column i have got the same date multiple times so i cannot work on the last 32 dates it must be on entries.




Hope that makes sense

Cheers

Mac

[Rod]

Not sure it can be done via SQL. Anyone else?

How's your VBA coding? I can imagine a fairly simple routine based on the absolute position of a record in a record set.

[BigMac4]

I was hoping for a nice simple query but i can do some VBA if thats the way forward.

Mac

[Rod]

OK, something like this should do you.

Public Function CountLast32() As Long
    Dim rst As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset("myQuery")
    rst.MoveLast
    If rst.AbsolutePosition >= 32 Then
        rst.Move -31
        Do Until rst.EOF
            If myCondition Then CountLast32 = CountLast32 + 1
            rst.MoveNext
        Loop
    End If
    Set rst = Nothing
    
End Function

Substitute your own names and code for the blue highlights.

[BigMac4]

I will give it a try. Thanks

Mac

[orange]

What field or combination of fields makes a record in your table unique? In other words, what is the primary key of this table?
How do you identify the last record in the table?

[BigMac4]

I have an Autonumber field as my primary key, it is not shown to the users but it will always be my unique record.

[orange]

Research Select TOP

I'd try

SELECT COUNT(*) FROM
(SELECT TOP 32 FROM YourTableName)
Where Grade = "PASS"

[Rod]

Hi Orange,

Something like that would work for BigMac if he could turn his result set upside down - that is reverse the sort order and guarantee that the records are in fact exactly in reverse sequence. Your inner query would be replaced by this new query. The outer query would have to group on 'Pass' or 'Fail' and count within those groupings.

Never occurred to me to reverse sort. Doh!

Maybe grouping is not necessary if the grades are numeric rather than two-state.

[orange]

Why Fail?

what i need to do is count how many "pass" grades are in column 2 within the last 32 entries

I agree that it should be DESCENDING.

Sample of same/similar concept:
I have an Employee Table (see attached) and have tested this sql

SELECT Count(*) AS Expr1, LNAME
FROM (select top 4 * from employee  ORDER BY EMPID DESC)
WHERE (((Left([lname],1))="J")) 
GROUP BY LName;

[Rod]

Why Fail?

Agreed, not necessary, BigMac only wants "pass" grades that I assumed at first were simple two-state, Pass/Fail values.

[BigMac4]

The database is alot more complicated that i make it sound i just needed an easy way to explain what i was after, now i have a code to start from i can work it into what i need. I may upload an example in the next few days if i cant ge it to work properly and see if you guys can rejig it abit.

Thanks for your help

Mac

[BigMac4]

It works out just how i needed it to, thanks alot.

Mac

[BigMac4]

Seen as that was very easy for you to work out i would like to expand on this a little and make the next problem a bit harder. What i need to do now is what can only assume will be an IF function in an expression builder. As you can see from my picture i have pass fail and OCL what i need in each entry under OCL is - If within the last 10 entries i have 3 fails return an "A" and if i have 4 fails return a "B" and so and so forth, again once i have the basic formula working i will expand on it a bit. By the way this is all very easy in Excel but in Access it is a whole different kettle of fish.

Lets see how good you guys are.

Thanks

Mac

P.s lots more interesting problems to follow at some point.

[Rod]

Lets see how good you guys are.

Hm! I've stumbled at the first hurdle; I don't fully understand your requirement. Using your example, are you expecting a value for OCL on every line or just the line for ID=4? Actually there appears to be no logical sequence to the lines iin your example.