SQL for New CRS_01_ChangeIFSPDate - please disregard other 01 Change
table
SQL - it is an old query in the database, not current.
ELECT CRSCUM.[ET ID], [NEW ETrack Import].ScAgency,
IIf([MostRecentIFSPDate]>=#12/1/2006#,"180",(IIf([MostRecentIFSPDate]<#12/1/2006#,"120",(IIf([MostRecentIFSPDate]
Is Null,[MostRecentIFSPDate]))))) AS IFSP_Due_Date_Status, [NEW ETrack
Import].MostRecentIFSPDate, [NEW ETrack Import].MostRecentElig, [NEW
ETrack
Import].PrimryCaregvrFirstName, [NEW ETrack Import].PrimryCaregvrLastName,
[NEW ETrack Import].SrvCoordFirstName, [NEW ETrack
Import].SrvCoordLastName,
[NEW ETrack Import].ChildLastName, [NEW ETrack Import].ChildFirstName,
[NEW
ETrack Import].ChildBirthDate, [NEW ETrack
Import].MostRecentEligStartDate,
[NEW ETrack Import].MostRecIFSPProvName1
FROM CRSCUM INNER JOIN [NEW ETrack Import] ON CRSCUM.[ET ID] = [NEW ETrack
Import].[ET ID];
--
~k sends
Help Me Know said:
Other input data source 'snapshot' data of children receiving services as
of
8/1/09, ET ID's the important input here(so I am told) to glean ACTIVE
case
statuses... Jerry - I truly appreciate you not giving up on this... ~k
sends
ET ID ChildBirthdate MostRecentElig Eligibility Date IFSP Date Elig End
Date Exit Date
255761058 04-Feb-09 At Risk 17-Sep-08 25-Aug-09
610395851 26-Aug-06 At Risk 21-Dec-05 10-Jun-09 26-Aug-09 26-Aug-09
610401717 16-Sep-06 At Risk 26-Jan-06 27-Feb-09 03-Sep-09
610405568 02-Aug-06 At Risk 10-Dec-08 16-Apr-09 20-Aug-09 20-Aug-09
Child Extract File (messy, no?)
ET
ID ChildLastName ChildFirstName ChildBirthDate ChildDueDate ChildSex
ChildEthnicity ChildRace Field9 Field10 ChildSSN ImmunAtEntrance
ImmunAtExit HlthProviderEntrance HlthProviderExit SchoolDist
FamIncomeRange FamFundSource PrimryCaregvrRelation PrimryCaregvrLastName
PrimryCaregvrFirstName PrimryCaregvrPhone PrimryCaregvrSSN
PrimryCaregvrAddress1 PrimryCaregvrAddress2 PrimryCaregvrCity
PrimaryCaregvrZipCode InitialNBHVAgency InitialNBHVLastName
InitialNBHVFirstName InitialNBHVOutcome InitialNBHVVisitDate
InitialNBHVOutcomeDate MostRecentNBHVAgency MostRecentNBHVLastName
MostRecentNBHVFirstName MostRecentNBHVOutcome MostRecentNBHVVisitDate
MostRecentNBHVOutcomeDate InitialRefDate InitialRefSourceTyp
InitialRefSourceName InitialRefToCategory InitialRefOutcome
InitialRefOutcomeDate MostRecentRefDate MostRecentRefSourceTyp
MostRecentRefSourceName MostRecRefToCategory MostRecRefContactDate
MostRecentRefOutcome SrvCoordLastName SrvCoordFirstName SCAssignedDate
ScAgency ScPrimryFunding InitialElig InitialEligStartDate
InitialEligEndDate InitialRisk Field61 Field62 Field63 InitialDelay
Field65 Field66 Field67 InitialDiagnosis Field69 MostRecentElig
MostRecentEligStartDate MostRecentEligEndDate MostRecentRisk Field74
Field75 Field76 MostRecentDelay Field78 Field79 Field80
MostRecentDiagnosis Field82 InitialHearSreenName InitialHearSreenDate
InitialHearScreenResults InitialHearScreenResultsLeftEar
InitialHearScreenResultsRightEar InitialNutScreenName InitialNutScreenDate
InitialNutScreenResults InitialVisScreenName InitialVisScreenDate
InitialVisScreenResults InitialASQDate InitialASQ-SEDate
InitialInformedClinicalOpinionDate
InitialInformedClinicalOpinionDiscipline1
InitialInformedClinicalOpinionDiscipline2 InitialEvalName InitialEvalDate
InitialEvalDiscipline1 InitialEvalDiscipline2 MostRecEvalName
MostRecEvalDate MostRecentEvalDiscipline1 MostRecentEvalDiscipline2
InitialIFSPDate InitialIFSPServLocation MostRecentIFSPDate
MostRecentIFSPLocation MostRecIFSPServType1 MostRecIFSPProvName1
MostRecIFSPServLocation1 MostRecIFSPPaymentSource1 MostRecIFSPServType2
MostRecIFSPProvName2 MostRecIFSPServLocation2 MostRecIFSPPaymentSource2
MostRecIFSPServType3 MostRecIFSPProvName3 MostRecIFSPServLocation3
MostRecIFSPPaymentSource3 MostRecIFSPServType4 MostRecIFSPProvName4
MostRecIFSPServLocation4 MostRecIFSPPaymentSource4 MostRecIFSPServType5
MostRecIFSPProvName5 MostRecIFSPServLocation5 MostRecIFSPPaymentSource5
TransPlanConfMtgDate InitialExitDate InitialExitReason InitialExitDest
MostRecExitDate MostRecExitReason MostRecExitDest
615762949 LYLES CAYDEN 19-Sep-08 M Non-Hispanic White Mother DELANEY
KIMBERLY 513-521-2089 3116
GLENAIRE DR Cincinnati 45251 American Nursing Care HUEGEN LISA Visit
Complete not referred to ongoing HMG Services 9/24/2008 American Nursing
Care HUEGEN LISA Visit Complete not referred to ongoing HMG
Services 9/24/2008 10/9/2008 Family
not interested in ongoing HMG Services No Destination
Identified 10/9/2008 Family not interested in ongoing HMG Services No
Destination Identified
615774825 WARD JAYDA 06-Nov-08 F Non-Hispanic Black or African
American Mother WARD BRITTANY 513-429-5919 2720 QUEEN CITY AVE # E
16 Cincinnati 45238 American Nursing Care LUCAS KAREN Visit Complete not
referred to ongoing HMG Services 11/11/2008 American Nursing
Care LUCAS KAREN Visit Complete not referred to ongoing HMG
Services 11/11/2008 11/23/2008 Family
not interested in ongoing HMG Services No Destination
Identified 11/23/2008 Family not interested in ongoing HMG Services No
Destination Identified
615727471 MOSSBURGER BRYCEN 29-Jan-08 M Non-Hispanic White Mother
MOSSBERGER NICOLE 513-502-1738 10350
WEST ROAD # 46 Harrison 45030 American Nursing Care OTIS TAMMY Visit
Complete
not referred to ongoing HMG Services 2/5/2008 American Nursing
Care OTIS TAMMY Visit Complete not referred to ongoing HMG
Services 2/5/2008 4/29/2008 Family
not interested in ongoing HMG Services No Destination
Identified 4/29/2008 Family not interested in ongoing HMG Services No
Destination Identified
615718409 GOETZ DANIEL 29-Dec-07 M Non-Hispanic White Mother GOETZ KARIN
513-985-9825 7404
EUCLID AVE Cincinnati 45243 American Nursing Care BRANDHORST AMY Visit
Complete not referred to ongoing HMG Services 1/4/2008 American Nursing
Care BRANDHORST AMY Visit Complete not referred to ongoing HMG
Services 1/4/2008 3/20/2008 Family
not interested in ongoing HMG Services No Destination
Identified 3/20/2008 Family not interested in ongoing HMG Services No
Destination Identified
--
~k sends
Help Me Know said:
SQL for New CRS_01_ChangeIFSPDATE
SELECT [DailyImport - ETrack 3].[ET ID], [DailyImport - ETrack
3].[SERVICE_COORDINATOR_AGENCY NAME], [DailyImport - ETrack
3].ELIGIBILITY_DESC_1, [DailyImport - ETrack 3].IFSP_DATE_1,
Max_IFSP_05.Current_IFSP_Date,
IIf([Current_IFSP_Date]>=#12/1/2006#,"180",(IIf([Current_IFSP_Date]<#12/1/2006#,"120",(IIf([Current_IFSP_Date]
Is Null,[Current_IFSP_Date]))))) AS IFSP_Due_Date_Status,
[DailyImport -
ETrack 3].PRIMARY_CAREGIVER_FIRST_NAME, [DailyImport - ETrack
3].PRIMARY_CAREGIVER_LAST_NAME, [DailyImport - ETrack
3].SERVICE_COORDINATOR_FIRST_NAME, [DailyImport - ETrack
3].SERVICE_COORDINATOR_LAST_NAME, [DailyImport - ETrack
3].CHILDS_LAST_NAME,
[DailyImport - ETrack 3].CHILDS_FIRST_NAME, [DailyImport - ETrack
3].CHILDS_BIRTH_DATE, [DailyImport - ETrack 3].ELIGIBILITY_DATE_1,
[DailyImport - ETrack 3].SERVICE_PROVIDER_TYPE_DESC_1
FROM [CRS_06-01-08] INNER JOIN ([DailyImport - ETrack 3] LEFT JOIN
Max_IFSP_05 ON [DailyImport - ETrack 3].[ET ID] = Max_IFSP_05.[ET ID])
ON
[CRS_06-01-08].[ET ID] = [DailyImport - ETrack 3].[ET ID]
ORDER BY Max_IFSP_05.Current_IFSP_Date;
Input into Query NEW CRS 02 SQL=
SELECT [New CRS_01_ChangeTable].[ET ID], [New
CRS_01_ChangeTable].ScAgency,
[New CRS_01_ChangeTable].MostRecentElig, IIf([MostRecentIFSPDate] Is
Not
Null,(DateAdd('d',[IFSP_Due_Date_Status],[MostRecentIFSPDate])),[MostRecentIFSPDate])
AS IFSP_Due_Date, [New CRS_01_ChangeTable].IFSP_Due_Date_Status, [New
CRS_01_ChangeTable].SrvCoordFirstName, [New
CRS_01_ChangeTable].SrvCoordLastName, [New
CRS_01_ChangeTable].MostRecentEligStartDate, [New
CRS_01_ChangeTable].ChildBirthDate, [New
CRS_01_ChangeTable].PrimryCaregvrFirstName, [New
CRS_01_ChangeTable].PrimryCaregvrLastName, [New
CRS_01_ChangeTable].ChildLastName, [New
CRS_01_ChangeTable].ChildFirstName,
[New CRS_01_ChangeTable].MostRecIFSPProvName1
FROM [New CRS_01_ChangeTable];
Into Query New CRS_03_ChangeIFSPDate SQL =
SELECT CRS_02.*, IIf([IFSP_Due_Date]>=#1/1/2009#,"Active
IFSP",(IIf([IFSP_Due_Date] Is Null,"Missing","OverDue"))) AS
IFSPStatus,
CRS_02.SERVICE_PROVIDER_TYPE_DESC_1
FROM CRS_02;
--
~k sends
:
I guess part of my problem understanding your situation is the 'messy
data'
you describe. After years of using Access, I tend to think of
queries in
terms of normalized data structures (yes, yes, I know, "normal" isn't
...
normal<g>).
Please be aware that the structure of the data you have available
does NOT
have to limit how you store it in Access. Even if your import table
is a
'flat-file', you can still use queries to parse it out into more
permanent
(and well-normalized) Access tables. If you follow this approach,
you'll be
able to make better use of Access' relationally-oriented
features/functions.
In the mean while, what you've provided so far doesn't seem to
include the
SQL statement of your query(s), nor the data structure from which the
query
draws. "How" depends on "what", and I don't have a very clear
picture yet
of what your data looks like...
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
IFSP Status - Individual Family Service Plan is an active case
where
The baby's age as of the report date is <36 months or is null
(prenatal)
Case Exit date is not exited on or before the report date
IFSP date is >= Eligibility Date
IFSP date is <= report date
The report date is the Children Receiving Services date, the first
of the
month prior to the first of the month run date, I.E. first of sept
CRS
was
dated 8/1/2009 - a snapshot report of children receiving services
cases at
that time.. hence, either ACTIVE, OVERDUE or MISSING status...
And you are absolutely correct this is some messy data...nothing
normalized
about it, coming from a very dynamic online application spit out in
Excel
CSV
then imported into our database.
--
~k sends
:
I don't know what an IFPS status is, or how it is germane to what
you are
doing...
How are you defining "active cases"?
A data file with 100+ fields is probably not well-normalized. So
what?
you
ask? So Access is optimized to work with well-normalized data,
not
spreadsheet data. If you feed Access 'sheet data, both you and
Access
have
to work overtime to come up with work-arounds.
"How" depends on "what", and I still don't have a very clear
picture of
what
data/data structure you're working with...
(still) more info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
This database run monthly for billing to service providers for
the at
risk
kids. Data file imported from online application - Child
Extract file,
138
fields long... is as of dated, lots of data about child cases,
serv.
providers, dates, etc. Also imported is a snapshot of children
receiving
services dated first of month for prior month activity.
I.E. First of September, Extract info as of dated when pulled
CRS report dated 8/1/2009 to capture all active cases as of that
date,
providers have 30 days to enter their case info into Early
Track, the
online
ap Ohio Dept of Health.
ET ID's are the common field identifier between the two data
sources.
Progression of Queries ran to render service providers with
billable
child
cases. Neither data source from ET give IFSP STATUS, which is
the main
objective of stripping data and importing it into our auxillary
database
to
calculate the ACTIVE cases, the OVERDUE cases and MISSING...
After the
query
progression below, Change table 3 query is source for a crosstab
query
to
get
results similar to this, except it is not calculating correctly
the
OVERDUES
nor giving me the correct IFSP STATUS based on the query
progression
logic
show here...
Hey guys, I really truly appreciate your help with this... It is
critical
that I get this fixed, and it is beyond my skill level...
Agency Eligibility Active IFSP OverDue Total
EChild At Risk 862 35 897
EChild Part C 293 2 295
MR/DD Part C 5 3 8
Babes At Risk 154 4 158
Babes Part C 24 2 26
LSrvc At Risk 416 3 419
LSrvc Part C 646 1 647
Totals 2400 50 2450
--
Thank-You, Thank-You, Thank-You
~k sends
:
Since we're not there and don't know your topic as well as you
do,
you'll
have to give us a bit more to go on...
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
in
message
Hello Anyone willing to shed some light on this progression
of
Queries...
I
inherited this db and I may have a stroke over it... Can not
get it
to
calculate a correct IFSP Status from what was left me below.
Does this make sense to anyone? Any help is truly
appreciated...
New CRS_01_ChangeIFSPDate
IFSP_Due_Date_Status:
IIf([MostRecentIFSPDate]>=#12/1/2006#,"180",(IIf([MostRecentIFSPDate]<#12/1/2006#,"120",(IIf([MostRecentIFSPDate]
Is Null,[MostRecentIFSPDate])))))
New CRS 02
IFSP_Due_Date: IIf([MostRecentIFSPDate] Is Not
Null,(DateAdd('d',[IFSP_Due_Date_Status],[MostRecentIFSPDate])),[MostRecentIFSPDate])
New CRS_03_ChangeIFSPDate
IFSPStatus: IIf([IFSP_Due_Date]>=#8/1/2009#,"Active
IFSP",(IIf([IFSP_Due_Date] Is Null,"Missing","OverDue")))
