Count last 32 entries help please.

[BigMac4]

I need a value for every line in OCL based on the last 32 results of pass (just ignore fail and date just there for show really). i have just looked at my example and notice an pass and a fail on one line that should not be like that. Dates are random and i wil get the same date on multiple lines so i cant use it in the calculation.

Hope that clears it up a bit

Mac

[Rod]

Sorry, I'm still confused. What exactly am I looking at in your example? Are those number counts? If so why are they all 1? To what does the ID refer?

Meanwhile one way of fulfilling the generic requirement is to use a nested IIf() function. However with a possibility of a count between 3 and 10 (where does 32 come from?) the IIf() needs to be nested to eight levels! Not pretty!

Another way is to use ASCII codes along the lines of: if count >= 3 then OCL = Chr(62 + CountOfFail)

I'm fairly certain the Chr() function works within Access SQL; a simple test will prove it either way.

[orange]

I'm with Rod - I don't understand the requirement. Can you tell us about the application?

[BigMac4]

I cant explain this very well it is very complicated the excel sheet i use at the moment is extremly complicated and messy thats why i am trying to convert to access. Anyway here is another picture that might make more sense. I will input data daily and it will recieve a passing grade if you like A or B. In the OCL coloumn i need to have a rolling 'statistic' from the last 32 entries in order that on every entry it will tell me in some form or another how the last 32 did in terms of pass (A) or fail (B) or (C). If there is 1 fail within the last 32 entries I.e one B i need it to return a value of my choice (something like level 1) if i get 2 B's then return (level 2) and so on.

[BigMac4]

Sorry the 32 is just what i am required to report to it has always been a rolling 32 i dont
understand it myself but i must adhere to my companies standards
.

[orange]

I'm still not understanding, but if you are storing a calculated value based on a rolling set of values, I don't think that value should be stored in a table.

i dont understand it myself but i must adhere to my companies standards

Also, if you don't understand it and can't explain it, I contend you can't build it.
Take the time to understand the issue. Write it down and get management to clarify and agree to the issue if necessary.

[Rod]

Hi Mac,

What you are showing us is an Access Table Datasheet view. If you expect this to behave like an Excel Spreadsheet, then you may be disappointed. Are you expecting to type a new date and a new pass value into this view and have Access calculate the value of OCL? If so, then I'm afraid it's not possible in this view.

I have tried coding the default value but at this time the only things recognised are built-in functions and constants. Your rolling 32 is not a built-in function.

So, still assuming that you want to enter a date and a pass value and have Access calculate the OCL value, you need a continuous form based on this table.

If I'm correct (or wrong for that matter) post back and we'll take it one step further. Incidentally are new entries always appended or is it possible to insert new entries into the body of the list?

PS. Just seen Orange's post that slipped in while composing this. It's all good advice especially the bit about not storing derived values on tables.

[Rod]

Mac,

Sorry about my 'Where did 32 come from' comment in the above - it's even in the title! My only defense is that it was getting late last night and I wasn't firing on all four. Anyway this morning is another day - and the sun is shining, we haven't seen it for four weeks!

Get back to us with the answers to my questions and we'll help out.

[BigMac4]

Ok sounds like we might get somewhere here, ok the last 32 results thing is a standard deviation of results set out to give the best reprisentation of results (or so i am told) for this matter it must be the last 32 entries.

I dont need to store this information in the table with the results but i must be able to run a form or report that shows this information against each entry in the database.

OCL means operating compliance level and this is how it works:
no fails in last 32 = level A0
1 fail in last 32 = Level A1
2 fail in last 32 = Level A2
3 Fail in last 32 = Level B3
4 fail in last 32 = Level B4
5 Fail in last 32 = Level B5
6 or More fail in last 32 = Level C6

So what i need is these "levels" to be returned based on the results of the last 32 entries into the database. As you can imagine this data can change on a entry by entry basis. As i said before this was easy in Excel

=IF(AJ124=$AJ$4,$AJ$5,IF(AJ124=$AK$4,$AK$5,IF(AJ12 4=$AL$4,$AL$5,IF(AJ124=$AM$4,$AM$5,IF(AJ124=$AT$4, $AT$5,IF(AJ124=$AV$4,$AV$5,IF(AJ124=$AW$4,$AW$5,IF ($AX$4,$AX$5,0))))))))

But excel is a flat database system and i want to convert to Access.

I dont know if it helps or makes it more confusing but, we can work out the OCL based on a percentage of passes insted of a sum of passes.

Thanks for all you input so far, i like giving people a challenge.

Mac

[Rod]

Hi Mac,

1. If you only ever report on the last (= most recent) result then I would recommend that the rolling 32 value is calculated at the time of reporting. It is, as Orange has pointed out, a derived value, albeit a complex one. SQL may be the way to go for this. Check up on the reporting requirement because Excel by default - the very nature of a spreadsheet - shows all results whereas Access can report on one result or a list of results according to the needs of the user.
2. If, however, you need to report on a list of results (similar to the examples you have published) then perhaps storing the rolling 32 value in the db is the lesser of two evils. It would be more appropriate (imho) to use VBA for this. This solution must allow for edits to existing data!

I have become interested in solution 2 and am tinkering with a small db to test various ideas. I'll publish this in a few hours so that you can play with it and say what you think.

[BigMac4]

Option 2 seems to be the way i need to go, if i get your demo DB i might be able to tweek it into mine and get it the way i need it. look forward to seeing what you come up with.

Cheers

Mac

[Rod]

Hi Mac,

Here it is with minimal error handling and minimal comments.

Form1 contains a couple of system tools.

Form2 is the continuous form. (Did I have trouble with recordsetclones - when is a clone not a clone? This is the reason for the count modifier.)

MSAF32Records.accdb

[BigMac4]

That is pefect, excatly what i needed i can now play about with it and add it into my database just need to change one thing i think, if i have had 6 or more fails it just needs to say c6 but that should be easy enough to sort out i think.

Thank you guys so much you have saved me hours of messing about with SQL.

Mac

[Rod]

'Snot perfect! Get back if you have problems. The place to define C6 as a ceiling is in the ConvertCountToText procedure.

[orange]

Rod,
Can you post an mdb version of you data base? I have acc2003.