FIS Access Export 2017/18

Home Forums Data issues FIS Access Export 2017/18

This topic contains 13 replies, has 3 voices, and was last updated by  Paul Dallaway 6 months, 2 weeks ago.

Viewing 14 posts - 1 through 14 (of 14 total)
  • Author

  • steveh

    This is the thread where we moan about ESFA changing the data structure of the .mdb export BACK to the structure we moaned about four years ago when the FAM codes and other monitoring fields were stored in separate tables for no apparent reason which means we have to rewrite a bunch of queries to do the simplest things…

    16/17 Access query to count enrols on a course:

    SELECT Valid_LearningDelivery.ProvSpecMon_A, Valid_LearningDelivery.LearnAimRef, Count(Valid_LearningDelivery.LearnRefNumber) AS CountOfLearnRefNumber
    FROM Valid_LearningDelivery
    GROUP BY Valid_LearningDelivery.ProvSpecMon_A, Valid_LearningDelivery.LearnAimRef
    ORDER BY Valid_LearningDelivery.ProvSpecMon_A, Valid_LearningDelivery.LearnAimRef;

    17/18 query to do the same:

    SELECT Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMon, Valid_LearningDelivery.LearnAimRef, Count(Valid_LearningDelivery.LearnRefNumber) AS CountOfLearnRefNumber
    FROM Valid_LearningDelivery INNER JOIN Valid_ProviderSpecDeliveryMonitoring ON (Valid_LearningDelivery.AimSeqNumber = Valid_ProviderSpecDeliveryMonitoring.AimSeqNumber) AND (Valid_LearningDelivery.LearnRefNumber = Valid_ProviderSpecDeliveryMonitoring.LearnRefNumber)
    WHERE (((Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMonOccur)=”A”))
    GROUP BY Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMon, Valid_LearningDelivery.LearnAimRef
    ORDER BY Valid_ProviderSpecDeliveryMonitoring.ProvSpecDelMon, Valid_LearningDelivery.LearnAimRef;

    (where ProvSpecMon_A = course code)



    I’m wondering if this is related to the export not working at all in version 005? That maybe rather than properly fixing it they’ve just gone back to the 14/15 configuration export model?


    Paul Dallaway

    Steve – I haven’t had a chance to test yet, but when you say the 14/15 configuration export does that mean the address fields have stayed where they used to be in a separate learner contact table rather than moving into the learner table (where they used to be about five years ago)!



    Ha! No, actually, the only change I really expected was with the address fields and they have, as you say, come into the Learner record due to the change in the structure of the XML entity.

    That, at least, makes sense (and I don’t tend to use the addresses anyway). It’s why all the other tables have reappeared that is annoying me…


    Paul Dallaway

    didn’t think it was likely (or possible) but thought I’d check. I’ve also realised what you mean by looking at the second query – we’ll have to rewrite all our import routines and I thought it was just the learner details…….what a pain



    don’t forget the employment statuses (less of an issue for you I’d imagine?)…

    Definite changes so far:

    Provider Specified Learner Fields in own table rather than in Learner table
    Provider Specified Learning Delivery Fields in own table rather than in Learning Delivery Table
    Learner FAM fields in separate table rather than in Learner Table
    Learning Delivery FAM fields in separate table rather than in Learning Delivery Table
    Employment Monitoring fields in separate table rather than in Employment table
    Learner Contact fields moved into the Learner table
    Rulebase_DV_Learner and Rulebase_DV_LearningDelivery are also absent (although it’s not the first time we’ve been without the derived tables at this time of year)


    Paul Dallaway

    Thanks for the summary – that’s really useful – it does have some pretty big implications for us (and organisations we work with as well).

    I’m going to contact the Agency directly but do you think this is going to be it for the year or is it an interim hiccup before another FIS update changes the export again? It’s a lot of work for us and others we work with, if we then have to change back later in the year.


    Paul Dallaway

    I’ve had this response from Ian Tress (Data Collections Service Product Owner) at the Agency:

    “We are aware the schema provided in the latest FIS mdb export has changed in the current version.

    To provide minimal impact for anyone who might have already updated their export routine; we are looking at providing both the flattened structure and the normalised structure from the most recent update.

    We will also be adding the missing DV and FD Val tables to the export.

    We are endeavouring to deploy this to live between the 6th & 8th of Nov in time for the R04 close.”

    In a conversation last week I’d specifically mentioned the Provider Specified fields not being in the learner/learning delivery table and think this means they’ll be going back into the FIS db export as part of the flattened structure export.



    Have you heard anything else about this, i.e. is it likely that in a short period of time we will have the ‘old’ database schema back?

    I’ve just started reviewing our FIS based reports and realised none of them work due to the changes. I don’t want to spend time converting them if I can wait a short time and they will work without changes!


    Paul Dallaway

    I haven’t since posting above. I’d asked if the update (hopefully putting back the provider specified fields into their respective tables – learner and learning delivery) would just be a main component set update and I received a response saying that was correct.

    As a result, I’m waiting for a main component set update in the next week and hoping it will sort out the issues we have. Judging from Ian’s response (posted above) it means there will be two options open to us – the ‘new’ in year database export, which prompted Steven’s initial post in this thread and this 6th-8th November fix which I’m hoping will mean most of our old import routines will still work.



    Well it’s not happened with component set 008 which I’ve just updated to, Access Export appears the same.

    I’ve not even had the courtesy of a holding response from ESFA, although I suspect including the words “formal complaint” in an email might have this effect…


    Paul Dallaway

    Steve – have you still got my number?



    I’ve now had exactly the same response as you got, not sure why it took three weeks…


    Paul Dallaway

    I’ve heard from a colleague that the release of component set 009 has added an option to export to an Access database in a flattened format. She sent a screen shot and it’s below the normal database export check box.

    I’m not sure about all the field changes in the original post but do know that it adds the provider specified fields and the FAM codes back into the learning delivery table at the very least.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.