Parsing memo field into texy fields

N

NJP

I need to separate the text in a memo field by the psuedo field headings (not
my design)

A portion of the memo field looks like this

Date(mm/dd/yy):
Time(23:15):
Event Summary:
Tigger met?(Y is yes):

and so on...
I'm using queries with trims, mids, and instr to break this text out into
mutliple fields.
Please let me know if there is a better way

Here's the the queries SQL:INSERT INTO tbltmpParsed ( PlantName,
ProductionUnit, RecordID, Author, [Event Date], [Event Time], [Equipment
Description], Summary, Rci, [RCI Triggers], GUPE, FUPE, OUPE, NM, CriC, SUSD,
EPRelease, CAS, SL, ce, Asses, Audit, ENV, safe, [proc], qual, reli, AU,
secu, Success, OTJ, Reported, Projected, [Pos Cause], [Act Res Ver], [Ver
Cause to Complete], [Actions Completed], [Additional Actions], DiscMeth,
CompType, FugTagNo, ProcStream, GovReg, CompLocDesc, [Verify Tag], [Repair
Att], [Repair Att Dt], [Repair Mthd], [Leak Stop], [Leak Still], Comments,
Status, UniqueID, ModifyDT, Equipment, NoteType )
SELECT tblObservation1.PlantName, tblObservation1.ProductionUnit,
tblObservation1.RecordID, tblObservation1.Author,
IIf(IsDate(Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event
Date",1)+22,12))),Format(Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event
Date",1)+22,12)),"Short Date"),Format([tblObservation1]![CreateDT],"Short
Date")) AS [Event Date],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event
Time",1)+25,InStr(1,[tblObservation1]![Observation],"Equipment
Description",1)-(InStr(1,[tblObservation1]![Observation],"Event
Time",1)+25))) AS [Event Time],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Equipment
Description",1)+23,InStr(1,[tblObservation1]![Observation],"Event
Summary",1)-(InStr(1,[tblObservation1]![Observation],"Equipment
Description",1)+23))) AS [Equipment Description],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event
Summary",1)+14,InStr(1,[tblObservation1]![Observation],"RCI Trigger
Met",1)-(InStr(1,[tblObservation1]![Observation],"Event Summary",1)+14))) AS
Summary,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"RCI
Trigger Met",1)+28),5)),1)="Y",-1,0) AS Rci,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
RCI Triggers Met",1)+22,InStr(1,[tblObservation1]![Observation],"Global UPE
Trigger Met",1)-(InStr(1,[tblObservation1]![Observation],"List RCI Triggers
Met",1)+22))) AS [RCI Triggers],
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Global
UPE Trigger Met",1)+35),5)),1)="Y",-1,0) AS GUPE,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Facility
UPE Trigger Met",1)+37),5)),1)="Y",-1,0) AS FUPE,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Other
UPE trigger met",1)+34),5)),1)="Y",-1,0) AS OUPE,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Near
Miss",1)+22),5)),1)="Y",-1,0) AS NM,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Crisis
Criteria Met",1)+32),5)),1)="Y",-1,0) AS CriC,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Start
Up/Shut Down Applicable",1)+44),5)),1)="Y",-1,0) AS SUSD,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"EP
Release Report? (Y if yes):",1)+30),5)),1)="Y",-1,0) AS EPRelease,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
CAS No(s)",1)+33,InStr(1,[tblObservation1]![Observation],"Sensory
Leak",1)-(InStr(1,[tblObservation1]![Observation],"List CAS No(s)",1)+33)))
AS CAS,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Sensory
Leak? (Y if yes and fill out below):",1)+44),5)),1)="Y",-1,0) AS SL,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Contractor
Event",1)+30),5)),1)="Y",-1,0) AS ce,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Assessment?
(Y if yes):",1)+24),5)),1)="Y",-1,0) AS Asses,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Audit?
(Y if yes):",1)+19),5)),1)="Y",-1,0) AS Audit,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Environmental?
(Y if yes):",1)+27),5)),1)="Y",-1,0) AS ENV,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Personal
Safety? (Y if yes)",1)+29),5)),1)="Y",-1,0) AS safe,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Process
Safety? (Y if yes)",1)+29),5)),1)="Y",-1,0) AS [proc],
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Quality?
(Y if yes):",1)+21),8)),1)="Y",-1,0) AS qual,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Reliability?
(Y if yes)",1)+25),7)),1)="Y",-1,0) AS reli,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Asset
Utilization? (Y if yes)",1)+31),7)),1)="Y",-1,0) AS AU,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Security?
(Y if yes)",1)+22),7)),1)="Y",-1,0) AS secu,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Success
Analysis? (Y if yes)",1)+30),7)),1)="Y",-1,0) AS Success,
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Off
The Job? (Y if yes)",1)+25),7)),1)="Y",-1,0) AS OTJ,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Event
Reported by",1)+36,InStr(1,[tblObservation1]![Observation],"Projected $
Impact",1)-(InStr(1,[tblObservation1]![Observation],"Event Reported
by",1)+36))) AS Reported,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Projected
$ Impact",1)+19,InStr(1,[tblObservation1]![Observation],"List Possible
causes",1)-(InStr(1,[tblObservation1]![Observation],"Projected $
Impact",1)+19))) AS Projected,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
Possible causes",1)+37,InStr(1,[tblObservation1]![Observation],"List
action",1)-(InStr(1,[tblObservation1]![Observation],"List Possible
causes",1)+37))) AS [Pos Cause],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
action and results",1)+52,InStr(1,[tblObservation1]![Observation],"List
verified cause",1)-(InStr(1,[tblObservation1]![Observation],"List action and
results",1)+52))) AS [Act Res Ver],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
verified cause",1)+42,InStr(1,[tblObservation1]![Observation],"List actions
completed",1)-(InStr(1,[tblObservation1]![Observation],"List verified
cause",1)+42))) AS [Ver Cause to Complete],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
actions completed ",1)+59,InStr(1,[tblObservation1]![Observation],"List
Additional Actions",1)-(InStr(1,[tblObservation1]![Observation],"List actions
completed ",1)+59))) AS [Actions Completed],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"List
Additional
Actions",1)+58,InStr(1,[tblObservation1]![Observation],"-------------",1)-(InStr(1,[tblObservation1]![Observation],"List
Additional Actions",1)+58))) AS [Additional Actions],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Discovery
Method",1)+54,InStr(1,[tblObservation1]![Observation],"Component
Type",1)-(InStr(1,[tblObservation1]![Observation],"Discovery Method",1)+54)))
AS DiscMeth,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Component
Type",1)+51,InStr(1,[tblObservation1]![Observation],"Fugitive
Tag",1)-(InStr(1,[tblObservation1]![Observation],"Component Type",1)+51))) AS
CompType,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Fugitive
Tag",1)+31,InStr(1,[tblObservation1]![Observation],"Process
Stream",1)-(InStr(1,[tblObservation1]![Observation],"Fugitive Tag",1)+31)))
AS FugTagNo,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Process
Stream",1)+26,InStr(1,[tblObservation1]![Observation],"Governing
Regulation",1)-(InStr(1,[tblObservation1]![Observation],"Process
Stream",1)+26))) AS ProcStream,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Governing
Regulation",1)+32,InStr(1,[tblObservation1]![Observation],"Component
Location",1)-(InStr(1,[tblObservation1]![Observation],"Governing
Regulation",1)+32))) AS GovReg,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Component
Location",1)+31,InStr(1,[tblObservation1]![Observation],"Initial to
Verify",1)-(InStr(1,[tblObservation1]![Observation],"Component
Location",1)+31))) AS CompLocDesc,
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Initial
to Verify",1)+36,InStr(1,[tblObservation1]![Observation],"Repair Attempt
made",1)-(InStr(1,[tblObservation1]![Observation],"Initial to
Verify",1)+36))) AS [Verify Tag],
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Repair
Attempt made (Y if yes)",1)+32),7)),1)="Y",-1,0) AS [Repair Att],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Date
Repair Attempt",1)+42,InStr(1,[tblObservation1]![Observation],"Repair
Method",1)-(InStr(1,[tblObservation1]![Observation],"Date Repair
Attempt",1)+42))) AS [Repair Att Dt],
Trim(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Repair
Method",1)+36,InStr(1,[tblObservation1]![Observation],"Was leak
stopped",1)-(InStr(1,[tblObservation1]![Observation],"Repair Method",1)+36)))
AS [Repair Mthd],
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"Was
leak stopped?",1)+50),7)),1)="Y",-1,0) AS [Leak Stop],
IIf(Left(Trim(Left(Mid([tblObservation1]![Observation],InStr(1,[tblObservation1]![Observation],"If
still leaking",1)+71),7)),1)="Y",-1,0) AS [Leak Still],
Right([tblObservation1]![Observation],(Len([tblObservation1]![Observation])-InStr(1,[tblObservation1]![Observation],"Comments",1)-8))
AS Comments, tblObservation1.Status, tblObservation1.UniqueID,
tblObservation1.ModifyDT, tblObservation1.Equipment, tblObservation1.NoteType
FROM tblObservation1
ORDER BY tblObservation1.PlantName;
 
N

NJP

Jamie,

Thanks for the direction, hoever I get and error message when executing the
query in the dropme database: Circular reference caused by alias 'data_col'
in query definition;s SELECT list.
 
N

NJP

Jamie,

Thanks so very much for your assistance. I will be able to apply to assist
in data retrieval no matter the fields.

I then used a cross-tab to transpose the data and append an existing
application that assist in data roll-up and analyses.
 
N

NJP

I'm having trouble with my crosstab
This is an example of my queries result
key FieldName Psuedo Results
11-74100 Event Date Event Date (mm/dd/yy): 7/10/06
11-74100 Event TimeEvent Time (e.g., 2315): 0900
11-74100 Summary Event Summary: #1 Hyper tripped on HiHi Rod Runout on #1
throw inboard cylinder
11-74113 Comments Comments: << 7/22/2006 5:01:09 PM **AutoUpdate** All
follow-up actions are complete. Status changed to 3 Closed. If the problem
still exists, you can add new follow-up actions to reopen the problem.
11-74113 reli Reliability? (Y if yes): Y


I need to transpose this data to then update a table

I need the key for the row and field for the colum and the text as the data.
I get a 1038 error. I know it's data related but don't know what to do
about it

Here's and example of the crosstab sql
TRANSFORM Max(qryParseMemo.results) AS MaxOfresults
SELECT qryParseMemo.KEY, Max(qryParseMemo.results) AS [Total Of results]
FROM qryParseMemo
GROUP BY qryParseMemo.KEY
PIVOT qryParseMemo.FieldName;
 
N

NJP

Jamie,

I did not realized by expression was causing some fields to have errors, the
error prevented the crosstab to from functioning. I was "assuming" that the
psuedo headers were not disturbed. So I coded around the missing headers.
I actually had to shorten the field names to not excede the expression's
character limits.

Thanks again for putting me on the right track. Here's what I ended up
with....

SELECT DT1.KEY, DT2.FieldName, DT2.HDR,
IIf(InStr(1,[DT1].[DATA],[DT2].[HDR])=0,"Bad
Entry",IIf(InStr(1,[DT1].[DATA],[DT2].[HDR_N])=0,"Bad
Entry",IIf(InStr(1,[DT1].[DATA],[DT3].[HDR_R])=0,Trim(Mid$([DT1].[DATA],InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),IIf([dt2].[HDR_N]="",Len([DT1].[DATA]),InStr(InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),[DT1].[DATA],[DT2].[HDR_N])-InStr(1,[DT1].[DATA],[DT2].[HDR])-Len([DT2].[HDR])))),Trim(Mid$([DT1].[DATA],InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),IIf([dt2].[HDR_N]="",InStr(InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),[DT1].[DATA],[DT3].[HDR_R])-InStr(1,[DT1].[DATA],[DT2].[HDR])-Len([DT2].[HDR]),InStr(InStr(1,[DT1].[DATA],[DT2].[HDR])+Len([DT2].[HDR]),[DT1].[DATA],[DT2].[HDR_N])-InStr(1,[DT1].[DATA],[DT2].[HDR])-Len([DT2].[HDR]))))))) AS results, DT1.DATA
FROM [SELECT KEY, DATA FROM Test]. AS DT1, [SELECT HDR, HDR_N, HDR_ID,
FieldName FROM Pseudos]. AS DT2, [SELECT HDR_R,HDRN_R FROM pseudo_remove]. AS
DT3
ORDER BY DT1.KEY;

--
Nita J. Perez


Jamie Collins said:
I'm having trouble with my crosstab
This is an example of my queries result
key FieldName Psuedo Results
11-74100 Event Date Event Date (mm/dd/yy): 7/10/06
11-74100 Event TimeEvent Time (e.g., 2315): 0900
11-74100 Summary Event Summary: #1 Hyper tripped on HiHi Rod Runout on #1
throw inboard cylinder
11-74113 Comments Comments: << 7/22/2006 5:01:09 PM **AutoUpdate** All
follow-up actions are complete. Status changed to 3 Closed. If the problem
still exists, you can add new follow-up actions to reopen the problem.
11-74113 reli Reliability? (Y if yes): Y


I need to transpose this data to then update a table

I need the key for the row and field for the colum and the text as the data.
I get a 1038 error. I know it's data related but don't know what to do
about it

Here's and example of the crosstab sql
TRANSFORM Max(qryParseMemo.results) AS MaxOfresults
SELECT qryParseMemo.KEY, Max(qryParseMemo.results) AS [Total Of results]
FROM qryParseMemo
GROUP BY qryParseMemo.KEY
PIVOT qryParseMemo.FieldName;
Click to expand...

I'm sorry, I lack the knowledge to help you with your crosstab problem;
I suggest you consider re-posting to attract then attention of someone
who can.

Jamie.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Memo Field Truncation 1
Truncating memo field 3
Parsing name into correct fields 3
Macro to populate contact fields no longer working 2
Parse a field into 2 fields 4
Help with compile error 2
Combine Two Different Worksheet_Change codes 9
Convert numbers to words 2

Top