Parsing memo field into texy fields

[Guest]

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;

 

[Jamie Collins]

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

It would appear you are relying on the pseudo field names always being
present and in the same order e.g. "Event Time" is always followed by
"Equipment Description" (or whatever) with the data element for "Event
Time" found between the two. Correct?

It may be possible to model the pseudo field names plus their order in
a two column table e.g.

('Event Date', 'Event Time')
('Event Time', 'Equipment Description')
('Equipment Description', 'Event Summary')
etc

You could then use this table to parse out the values.

I'd have to make a number of assumptions about delimiters etc so,
rather than a solution, here's a quick demo to give you some ideas:

CREATE TABLE Test (
key_col INTEGER NOT NULL UNIQUE,
data_col MEMO NOT NULL
)
;
INSERT INTO Test VALUES (
1, 'Event Date: 2006-01-01 Event Time: 23:59:59 Equipment Description:
Crampons Event Summary: Mountain rescue')
;
INSERT INTO Test VALUES (
2, 'Event Date: 2006-08-02 Event Time: 13:23:25 Equipment Description:
Sandwich box Event Summary: Lunch')
;

CREATE TABLE Pseudos (
pseudo_name_1 VARCHAR(30) NOT NULL,
pseudo_name_2 VARCHAR(30) NOT NULL)
;
INSERT INTO Pseudos
VALUES ('Event Date', 'Event Time')
;
INSERT INTO Pseudos
VALUES ('Event Time', 'Equipment Description')
;
INSERT INTO Pseudos VALUES
('Equipment Description', 'Event Summary')
;

And the query:

SELECT DT1.key_col, DT1.data_col,
DT2.pseudo_name_1, DT2.pseudo_name_2,
MID$(DT1.data_col, INSTR(1, DT1.data_col, DT2.pseudo_name_1) +
LEN(DT2.pseudo_name_1),
INSTR(INSTR(1, DT1.data_col, DT2.pseudo_name_1)
+ LEN(DT2.pseudo_name_1), DT1.data_col, DT2.pseudo_name_2)
- INSTR(1, DT1.data_col, DT2.pseudo_name_1)
- LEN(DT2.pseudo_name_1)) AS data_element
FROM
(
SELECT key_col, ' ' & data_col & ' ' AS data_col
FROM Test
)
AS DT1,
(
SELECT ' ' & pseudo_name_1 & ': ' AS pseudo_name_1,
' ' & pseudo_name_2 & ': ' AS pseudo_name_2
FROM Pseudos
) AS DT2

The derived tables (DT1 and DT1) are to suffix/prefix the text with
delimiters.

Here's the VBA to reproduce the above scenario. It creates a new
database C:\DropMe.mdb with tables, data and stored query. Run the
code, open the db and take a look at the query:

Sub pseudos()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test ( key_col INTEGER NOT" & _
" NULL UNIQUE, data_col MEMO NOT NULL);"
.Execute _
"INSERT INTO Test VALUES (1, 'Event Date:" & _
" 2006-01-01 Event Time: 23:59:59 Equipment" & _
" Description: Crampons Event Summary: Mountain" & _
" rescue') "
.Execute _
"INSERT INTO Test VALUES (2, 'Event Date:" & _
" 2006-08-02 Event Time: 13:23:25 Equipment" & _
" Description: Sandwich box Event Summary:" & _
" Lunch') "

.Execute _
"CREATE TABLE Pseudos ( pseudo_name_1 VARCHAR(30)" & _
" NOT NULL, pseudo_name_2 VARCHAR(30) NOT" & _
" NULL)"
.Execute _
"INSERT INTO Pseudos VALUES ('Event Date'," & _
" 'Event Time') "
.Execute _
"INSERT INTO Pseudos VALUES ('Event Time'," & _
" 'Equipment Description') "
.Execute _
"INSERT INTO Pseudos VALUES ('Equipment Description'," & _
" 'Event Summary') "

.Execute _
"CREATE VIEW qryTest AS SELECT DT1.key_col" & _
" AS key_col, DT1.data_col AS data_col, DT2.pseudo_name_1" & _
" AS pseudo_name_1, DT2.pseudo_name_2 AS" & _
" pseudo_name_2, MID$(DT1.data_col, INSTR(1," & _
" DT1.data_col, DT2.pseudo_name_1) + LEN(DT2.pseudo_name_1)," & _
" INSTR(INSTR(1, DT1.data_col, DT2.pseudo_name_1)" & _
" + LEN(DT2.pseudo_name_1), DT1.data_col," & _
" DT2.pseudo_name_2) - INSTR(1, DT1.data_col," & _
" DT2.pseudo_name_1) - LEN(DT2.pseudo_name_1))" & _
" AS data_element FROM ( SELECT key_col," & _
" ' ' & data_col & ' ' AS data_col FROM Test" & _
" ) AS DT1, ( SELECT ' ' & pseudo_name_1" & _
" & ': ' AS pseudo_name_1, ' ' & pseudo_name_2" & _
" & ': ' AS pseudo_name_2 FROM Pseudos )" & _
" AS DT2;"

Dim rs
Set rs = .Execute( _
"SELECT key_col, pseudo_name_1 AS pseudo_field_name," & _
" data_element FROM qryTest ORDER BY key_col," & _
" pseudo_name_1;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Guest]

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.

 

[Jamie Collins]

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.

It works for me

Try this:

1) Open Excel.
2) Navigate the Visual Basic Editor e.g. from the menu, choose: Tools,
Macro, Visual Basic Editor.
3) Add a Standard code module e.g. from the menu, choose: Insert,
Module.
4) Copy and paste the code (modified slightly, below) into the module.
5) Run the code e.g. from the menu, choose: Tool, Macros and with
'pseudos' selected press the 'Run' button.

Here's the code (tweaked):

Sub pseudos()
On Error Resume Next
Kill "C:\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test ( key_col INTEGER NOT" & _
" NULL UNIQUE, data_col MEMO NOT NULL);"
.Execute _
"INSERT INTO Test VALUES (1, 'Event Date:" & _
" 2006-01-01 Event Time: 23:59:59 Equipment" & _
" Description: Crampons Event Summary: Mountain" & _
" rescue') "
.Execute _
"INSERT INTO Test VALUES (2, 'Event Date:" & _
" 2006-08-02 Event Time: 13:23:25 Equipment" & _
" Description: Sandwich box Event Summary:" & _
" Lunch') "

.Execute _
"CREATE TABLE Pseudos ( pseudo_name_1 VARCHAR(30)" & _
" NOT NULL, pseudo_name_2 VARCHAR(30) NOT" & _
" NULL)"
.Execute _
"INSERT INTO Pseudos VALUES ('Event Date'," & _
" 'Event Time') "
.Execute _
"INSERT INTO Pseudos VALUES ('Event Time'," & _
" 'Equipment Description') "
.Execute _
"INSERT INTO Pseudos VALUES ('Equipment Description'," & _
" 'Event Summary') "

.Execute _
"CREATE VIEW qryTest AS SELECT DT1.key_col" & _
" , DT1.data_col, DT2.pseudo_name_1" & _
" , DT2.pseudo_name_2" & _
" , MID$(DT1.data_col, INSTR(1," & _
" DT1.data_col, DT2.pseudo_name_1) + LEN(DT2.pseudo_name_1)," & _
" INSTR(INSTR(1, DT1.data_col, DT2.pseudo_name_1)" & _
" + LEN(DT2.pseudo_name_1), DT1.data_col," & _
" DT2.pseudo_name_2) - INSTR(1, DT1.data_col," & _
" DT2.pseudo_name_1) - LEN(DT2.pseudo_name_1))" & _
" AS data_element FROM ( SELECT key_col," & _
" ' ' & data_col & ' ' AS data_col FROM Test" & _
" ) AS DT1, ( SELECT ' ' & pseudo_name_1" & _
" & ': ' AS pseudo_name_1, ' ' & pseudo_name_2" & _
" & ': ' AS pseudo_name_2 FROM Pseudos )" & _
" AS DT2;"

Dim rs
Set rs = .Execute( _
"SELECT key_col, pseudo_name_1 AS pseudo_field_name," & _
" data_element FROM qryTest ORDER BY key_col," & _
" pseudo_name_1;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

HTH,
Jamie.

--

 

[Guest]

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.

 

[Guest]

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;

 

[Jamie Collins]

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;

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.

--

 

[Guest]

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

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;

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.