EFA LR and SFA LR Funding Summary claim reports

Feconnect has been replaced by ESFA communities.

Feconnect is read only and it is not possible to create or reply to topics or posts. To search feconnect we suggest you use site specific searches. ESFA Communities is now live. Feconnect users will need to create a new ESFA communities account to post, reply or subscribe for email alerts.



Home Forums Data issues EFA LR and SFA LR Funding Summary claim reports

This topic contains 3 replies, has 2 voices, and was last updated by  Martin West 7 years, 6 months ago.

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

  • Gaz
    Spectator

    Hi All,

    Just wondering if anyone out there has ever managed to match the funding, SLN and learner head counts in each of the sections on the EFA LR / SFA LR funding summary claim reports and would be willing to share how please?

    SQL would be great.

    We can always get very close but never exact, always seems to be a mystery how the numbers are produced.

    Cheers
    Gaz

     
    #1620

    Martin West
    Spectator

    Hi Gaz,
    I can generally match numbers and funding with the following.
    SQL used to extract funding data from the LIS mdb, allowance for any LIS calculation known issues will have to be allowed for but as these change over time I keep a separate bit of SQL to correct the data where required. You will need to adjust to account for what codes you use in PROVIDERSPECLEARNDELMON.

    For EFA 16-18

    INSERT INTO ALRFundingAnalysis ( LEARNREFNUMBER, SumOfAIMTOTALFUND, PROVIDERSPECLEARNDELMON_C, LastOfPROVIDERSPECLEARNDELMON_D, FirstOfPROVIDERSPECLEARNDELMON_B, FUNDLINETYPE, SumOfALSCASH, SumOfLEARNDELSLN, SumOfONPROGCASH )
    SELECT FM22_Learning_Delivery.LEARNREFNUMBER, Sum(FM22_Learning_Delivery.AIMTOTALFUND) AS SumOfAIMTOTALFUND, FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_C, Last(FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_D) AS LastOfPROVIDERSPECLEARNDELMON_D, First(FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_B) AS FirstOfPROVIDERSPECLEARNDELMON_B, FM22_Learning_Delivery.FUNDLINETYPE, Sum(FM22_Learning_Del_Per.ALSCASH) AS SumOfALSCASH, Sum(FM22_Learning_Del_Per.LEARNDELSLN) AS SumOfLEARNDELSLN, Sum(FM22_Learning_Del_Per.ONPROGCASH) AS SumOfONPROGCASH
    FROM FM22_Learning_Delivery LEFT JOIN FM22_Learning_Del_Per ON (FM22_Learning_Delivery.LEARNREFNUMBER = FM22_Learning_Del_Per.LEARNREFNUMBER) AND (FM22_Learning_Delivery.AIMSEQNUMBER = FM22_Learning_Del_Per.AIMSEQNUMBER)
    GROUP BY FM22_Learning_Delivery.LEARNREFNUMBER, FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_C, FM22_Learning_Delivery.FUNDLINETYPE
    HAVING (((Sum(FM22_Learning_Delivery.AIMTOTALFUND))>0) AND ((FM22_Learning_Delivery.FUNDLINETYPE)=”Adult Learner Responsive 19+”))
    ORDER BY FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_C, Last(FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_D), First(FM22_Learning_Delivery.PROVIDERSPECLEARNDELMON_B);

    For SFA ALR

    INSERT INTO LRFundingAnalysis ( LEARNREFNUMBER, SumOfAIMTOTALFUND, FUNDLINETYPE, SumOfLEARNDELSLN, SumOfALSCASH, SumOfONPROGCASH, PROVIDERSPECLEARNDELMON_C, LastOfPROVIDERSPECLEARNDELMON_D, FirstOfPROVIDERSPECLEARNDELMON_B )
    SELECT FM21_Learning_Delivery.LEARNREFNUMBER, Sum([ALSCASH]+[ONPROGCASH]) AS SumOfAIMTOTALFUND, FM21_Learning_Delivery.FUNDLINETYPE, Sum(FM21_Learning_Del_Per.LEARNDELSLN) AS SumOfLEARNDELSLN, Sum(FM21_Learning_Del_Per.ALSCASH) AS SumOfALSCASH, Sum(FM21_Learning_Del_Per.ONPROGCASH) AS SumOfONPROGCASH, DLookUp(‘ProviderSpecLearnDelMon’,’Learning_Del_Prov_Mon’,’LearnRefNumber=’ & Chr(34) & FM21_Learning_Delivery!LEARNREFNUMBER & Chr(34) & ‘ And LearnDelOccurCode=’ & Chr(34) & ‘C’ & Chr(34)) AS PROVIDERSPECLEARNDELMON_C, DLookUp(‘ProviderSpecLearnDelMon’,’Learning_Del_Prov_Mon’,’LearnRefNumber=’ & Chr(34) & FM21_Learning_Delivery!LEARNREFNUMBER & Chr(34) & ‘ And LearnDelOccurCode=’ & Chr(34) & ‘D’ & Chr(34)) AS LastOfPROVIDERSPECLEARNDELMON_D, DLookUp(‘ProviderSpecLearnDelMon’,’Learning_Del_Prov_Mon’,’LearnRefNumber=’ & Chr(34) & FM21_Learning_Delivery!LEARNREFNUMBER & Chr(34) & ‘ And LearnDelOccurCode=’ & Chr(34) & ‘B’ & Chr(34)) AS FirstOfPROVIDERSPECLEARNDELMON_B
    FROM FM21_Learning_Delivery LEFT JOIN FM21_Learning_Del_Per ON (FM21_Learning_Delivery.LEARNREFNUMBER = FM21_Learning_Del_Per.LEARNREFNUMBER) AND (FM21_Learning_Delivery.AIMSEQNUMBER = FM21_Learning_Del_Per.AIMSEQNUMBER)
    GROUP BY FM21_Learning_Delivery.LEARNREFNUMBER, FM21_Learning_Delivery.FUNDLINETYPE
    HAVING (((Sum([ALSCASH]+[ONPROGCASH]))>0) AND ((FM21_Learning_Delivery.FUNDLINETYPE)”Adult Learner Responsive 16-18″));

    For SFA ER

    INSERT INTO ERFundingAnalysis ( LEARNREFNUMBER, PROVIDERSPECLEARNDELMON_C, LastOfPROVIDERSPECLEARNDELMON_D, FirstOfPROVIDERSPECLEARNDELMON_B, FUNDLINETYPE, SumOfALSCASH, SumOfONPROGCASH, SumOfBALCASH, SumOfACHCASH, SumOfAIMTOTALFUND )
    SELECT FM45_Learning_Delivery.LEARNREFNUMBER, FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_C, Last(FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_D) AS LastOfPROVIDERSPECLEARNDELMON_D, First(FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_B) AS FirstOfPROVIDERSPECLEARNDELMON_B, FM45_Learning_Delivery.FUNDLINETYPE, Sum(FM45_Learning_Del_Per.ALSCASH) AS SumOfALSCASH, Sum(FM45_Learning_Del_Per.ONPROGCASH) AS SumOfONPROGCASH, Sum(FM45_Learning_Del_Per.BALCASH) AS SumOfBALCASH, Sum(FM45_Learning_Del_Per.ACHCASH) AS SumOfACHCASH, Sum(FM45_Learning_Del_Per!ALSCASH+FM45_Learning_Del_Per!ONPROGCASH+FM45_Learning_Del_Per!BALCASH+FM45_Learning_Del_Per!ACHCASH) AS SumOfAIMTOTALFUND
    FROM FM45_Learning_Delivery LEFT JOIN FM45_Learning_Del_Per ON (FM45_Learning_Delivery.LEARNREFNUMBER = FM45_Learning_Del_Per.LEARNREFNUMBER) AND (FM45_Learning_Delivery.AIMSEQNUMBER = FM45_Learning_Del_Per.AIMSEQNUMBER)
    GROUP BY FM45_Learning_Delivery.LEARNREFNUMBER, FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_C, FM45_Learning_Delivery.FUNDLINETYPE
    HAVING (((Sum(FM45_Learning_Delivery.AIMTOTALFUND))>0))
    ORDER BY FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_C, Last(FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_D), First(FM45_Learning_Delivery.PROVIDERSPECLEARNDELMON_B);

    Hope that helps
    Regards

     
    #1621

    Martin West
    Spectator

    Hi Gaz,
    Sorry Error in 16-18 & ALR title.

     
    #1622

    Martin West
    Spectator

    Hi Again Gaz,

    A bit more explanation on the code:
    To get actual numbers for each funding line from each table

    SFA
    DCount(‘LEARNREFNUMBER’,’ALRFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ‘Adult Learner Responsive 19+’ & Chr(34))
    DCount(‘LEARNREFNUMBER’,’ALRFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ‘Adult Learner Responsive 16-18’ & Chr(34))

    EFA
    DCount(‘LEARNREFNUMBER’,’LRFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ’16-18 Learners (excluding High Cost ALS Learners)’ & Chr(34))
    DCount(‘LEARNREFNUMBER’,’LRFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ’16-18 Learners with High Cost ALS’ & Chr(34))
    DCount(‘LEARNREFNUMBER’,’LRFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ’19-24 Learners with High Cost ALS’ & Chr(34))
    To replicate EFA count
    SELECT FM21_Learning_Delivery.LEARNREFNUMBER, Count(FM21_Learning_Delivery.AIMSEQNUMBER) AS CountOfAIMSEQNUMBER, Sum(FM21_Learning_Delivery.PLANNEDDAYSTHISYEAR) AS SumOfPLANNEDDAYSTHISYEAR, Max(FM21_Learning_Delivery.PROGWGTFACTOR) AS MaxOfPROGWGTFACTOR, Max(FM21_Learning_Delivery.TRANSFERREDIND) AS MaxOfTRANSFERREDIND, First(FM21_Learning_Delivery.FUNDLINETYPE) AS FirstOfFUNDLINETYPE, Sum(FM21_Learning_Delivery.LEARNDELANNUALPLANNEDGLH) AS SumOfLEARNDELANNUALPLANNEDGLH, First(LRFundingAnalysis.SumOfAIMTOTALFUND) AS FirstOfSumOfAIMTOTALFUND, LRFundingAnalysis.LastOfPROVIDERSPECLEARNDELMON_D
    FROM FM21_Learning_Delivery LEFT JOIN LRFundingAnalysis ON FM21_Learning_Delivery.LEARNREFNUMBER = LRFundingAnalysis.LEARNREFNUMBER
    GROUP BY FM21_Learning_Delivery.LEARNREFNUMBER, LRFundingAnalysis.LastOfPROVIDERSPECLEARNDELMON_D
    HAVING (((Sum(FM21_Learning_Delivery.PLANNEDDAYSTHISYEAR))>14) AND ((First(FM21_Learning_Delivery.FUNDLINETYPE))=”16-18 Learners (excluding High Cost ALS Learners)”) AND ((First(LRFundingAnalysis.SumOfAIMTOTALFUND))>0))
    ORDER BY LRFundingAnalysis.LastOfPROVIDERSPECLEARNDELMON_D;

    ER
    DCount(‘LEARNREFNUMBER’,’ERFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ’16-18 Apprenticeship’ & Chr(34))
    DCount(‘LEARNREFNUMBER’,’ERFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ’19-24 Apprenticeship’ & Chr(34))
    DCount(‘LEARNREFNUMBER’,’ERFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ’25+ Apprenticeship’ & Chr(34))
    DCount(‘LEARNREFNUMBER’,’ERFundingAnalysis’,’FUNDLINETYPE=’ & Chr(34) & ‘Workplace Learning’ & Chr(34))

    Used In the above for PROVIDERSPECLEARNDELMON
    A Course
    B Parent Course
    C Department/Academy
    D Sub Academy/school

     
    #1623
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.