FIS Access Export 2017/18

Home Forums Data issues FIS Access Export 2017/18

This topic contains 6 replies, has 2 voices, and was last updated by  Paul Dallaway 1 week, 3 days ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts

  • steveh
    Participant

    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)

     
    #205461

    steveh
    Participant

    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?

     
    #205463

    Paul Dallaway
    Participant

    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)!

     
    #205473

    steveh
    Participant

    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…

     
    #205475

    Paul Dallaway
    Participant

    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

     
    #205479

    steveh
    Participant

    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)

     
    #205492

    Paul Dallaway
    Participant

    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.

     
    #205661
Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.