Incremental Counting

[Cory_nz]

Hi people

I'm working on a student assessment database, where an assessment can be marked a number of times (these are called attempts) until the student scores 100% on their assessment. A student will have a number of different assessments (called units).

Within my DB, I would like to have the attempt field automatically increment every time that unit was received.

So for example:

Unit - Date Received - Attempt

112 - 1/09/2009 - 1

111 - 2/09/2009 - 1

112 - 6/09/2009 - 2

112 - 10/09/2009 - 3

...etc

I've included the DB so you can see what I'm on about in further detail.

Anyone have any suggestions?

thanks

Cory

Daenis_DB_2.0.zip

[Søren Dyhr]

While I on the general level must disagree with the choice of relational structure, could what you're after be achieved this way:

--sd

Daenis_DB_2_1.fp7.zip

[Cory_nz]

Relational Structure

What's wrong with the relational structure? Any feedback will be greatly appreciated.

Attempts

As for the attempts - sorry I obviously wasn't clear with what I wanted to do. If you have a look at the layout "DATAENTRY_unit" I have the attempt count as a summary of the count of date_received - but as a running count. While this works on screen, I need the actual attempt number stored permanently in the DB, as this will be called upon for future reports.

Edited September 30, 2009 by Guest

[Søren Dyhr]

In the data entry layout, why can't you just pick the student ID and type in the ID and the basic's like name etc. arrives automaticly.

What is wrong with that is several students names can reside on the same ID ...

I need the actual attempt number stored permanently in the DB, as this will be called upon for future reports.

This is a need which isn't fully justified, since it always can be derived from the dates of attempted, this is what subsummary reports are for.

The exceptions to this is ledgers in book keeping where lookup last can be forgiven, due to the endless numbers of transactions - where only the latest really matters. Unless you track the entire number of students in the Pan Pacific area isn't it fully justified as denormalization.

--sd

Edited September 30, 2009 by Guest

[Cory_nz]

We contract our services out to external providers, and so student_id is an id generated by them. We have no control over how this code is generated, and how accurate the uniqueness is.

_pk_std_id is the unique code for any providers student's within our system, and so there will only be one student per _pk_std_id

As for the rest of the student details arriving when the student_id is entered, all the students details are entered into the system at the DATAENTRY_student screen. Before this stage, the student doesn't exist within our system.

Edited September 30, 2009 by Guest

[Søren Dyhr]

_pk_std_id is the unique code for any providers student's within our system, and so there will only be one student per _pk_std_id

That what I needs to be indeed, but why doesn't the fields populate when picking one students ID and enters it in a newly created record in the Assesment table.

--sd

[Cory_nz]

By having the actual attempt number, stored with each attempt occurrence, I can easily run reports on specific attempts i.e. How many attempt 2 assessments were marked between two date ranges.

[Cory_nz]

* Quote:

_pk_std_id is the unique code for any providers student's within our system, and so there will only be one student per _pk_std_id

That what I needs to be indeed, but why doesn't the fields populate when picking one students ID and enters it in a newly created record in the Assesment table.

You mean if a student already exists within the system?

[Søren Dyhr]

Alright I saw what I misunderstood here, the attempts should be made from the student initially and not starting in the assessments tables layout.

--sd

[Cory_nz]

No - an assessment can come in many times. So the initial assessment details are stored in the ASSESSMENT table (who owns the assessment, what the unit is etc), but each time that assessment comes back to be marked it is updated in the ATTEMPTS table (the date it came in, the number of times it's come in (aka attempt), the date it was marked etc).

Does that help to make things clearer?

It is the number of times an assessment has come in to be marked that I need calculated.

[Søren Dyhr]

By having the actual attempt number, stored with each attempt occurrence, I can easily run reports on specific attempts i.e. How many attempt 2 assessments were marked between two date ranges.

What prevents you from doing this in a subsummary report instead, making the counting happen there and nowhere else ... what you wish is to store is redundant since is almost effortless can derived at any time.

Think of you can have several breakers, and since you're on fm10 could your reporting be done in the vicinity of this:

http://www.kevinfrank.com/download/2009/report-in-browse-mode.zip

But back to this:

What's wrong with the relational structure? Any feedback will be greatly appreciated.

What is it 1NF says about Attempt2, Attempt3 ?? Take a look here:

http://www.troubleshooters.com/littstip/ltnorm.html

Next issue is couldn't assessment be made in several institutions, your grandparental structure, seems a bit stiff - my take here is that assessments should be join table records.

--sd

[Cory_nz]

ok - so how would I go about getting the sub-summary report to work?

If I have a field in ATTEMPT with the attempt number, I can get the report I need to work fine, but am unsure how to go about getting it to work without this field.

I've attached a copy of the report I've generated in the past. This is what I'd need to get the new system to show me.

Any ideas?

Edited October 1, 2009 by Guest

[Søren Dyhr]

but am unsure how to go about getting it to work without this field.

It's not too difficult, or would you consider it to slow for your use?

Take a look at the attachment...

-----------------

Ah! a tiny logical error in the Usher calc'field - should have been:

Let(

    [

        _c = Count( Extend( ATTEMPT::_fk_assessment_id ) );

        _tc = Case( _c  ≤ 5; _c; 6 )

    ];

    Case( _tc = Get( CalculationRepetitionNumber ); 1 )

)

--sd

Daenis_DB_2_3.zip

Edited October 1, 2009 by Guest

[Cory_nz]

Hi Søren - thanks for your help and patience with this mate :

I've just checked out your attachment, and it's not quite what I was after. You've only taken into account the last instance of an attempt on a unit. So if Scuba Steve took 4 attempts to finish his 111 assessment, then in your report only the attempt 4 is showing. I also need to take into account the first 3 attempts. This means that Glenfield, should be showing 5 x 1st attempts, 4 x 2nd attempts etc. Would you know how we can get this?

The way I previously did it, when I had the attempt number as a field, was to have fields with case functions:

Case (attempt = "1" ; 1 ; 0)

Case (attempt = "2" ; 1 ; 0) etc

and then have these totaled, using another summary field which worked just fine.

Also, would you mind briefly explaining the LET() formula you gave me? Best if I understand what it's about, so that I can learn for the future

Edited October 1, 2009 by Guest

[Søren Dyhr]

Ah! I see... then change the usher calc' into:

Let(

    [

        _c = Count( Extend( ATTEMPT::_fk_assessment_id ) );

        _tc = Case( _c  ≤ 5; _c; 6 )

    ];

    Case( _tc    ≥     Get( CalculationRepetitionNumber ); 1 )

)

But I'm a little puzzled by 4 attempts giving 5 of first attempt ... why?

--sd

[Cory_nz]

That seems to work a treat - thanks!

I'm trying to get understand your calculation, so would you mind talking me through it a little?

From the looks of it, you've got a repetition of 6. Does that mean it will only go up to the 6th attempt? Or have you got it to count the 6th repetition as >=6?

As for the why - we contract out the service of assessment marking to education providers. So one of the reports we need to generate is how many assessments came in and what attempt were they? We've calculated the time it takes to mark each assessment dependent on what attempt it is.

[comment]

Have you considered producing a "native" Filemaker report - i.e. not a crosstab, but something like:

Christchurch

• Attempt 1: 172

• Attempt 2: 111

• Attempt 3: 36

• Attempt 4: 6

• Attempt 5: 1

Total: 326

Gisburne

• Attempt 1: 76

• Attempt 2: 19

• Attempt 3: 8

Total: 103

...

This would be a lot simpler (not to mention faster) that any attempt to force Filemaker into functioning like a spreadsheet.

[Søren Dyhr]

Or have you got it to count the 6th repetition as >=6?

Indeed - I nicked it right off you image the 6+ thingy!

--sd