EFA LR and SFA LR Funding Summary claim reports

[GazSpectator]

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

[Martin WestSpectator]

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

[Martin WestSpectator]

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

[Martin WestSpectator]

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