The Memo field in Access 2003

[Guest]

I have a memo field that is populated from an exchange import. The data is
the excat same format in every entry. I want to turn this data into a new
table using the memo field to populate the rows and columns.

I figured I could use the trim function or something like that but I'm
wondering if there might be an easier way.

I hope that question makes sense.

 

[Guest]

There probably is no easy way but I would need to know more. Are the data
segments in the memo field separated in some manner like with dashes, commas,
five spaces, or each occupies 15 characters.

Is there always text followed by a number in the second position?

Do the records always contain the same number of data elements? What is
between the third and fifth element if the fourth is missing/null?

 

[Guest]

The data segments are as follows:

Report is as of HH:MM ET, Day, MM-DD-YY

Mkt MarketName CallType Avg Age(hrs)
-------------------------------------
## Text (This is a different count) but it's always followed by Voice ##

So the data segments are relatively easy except for Market Name
Market Name causes me issues.

It ranges from 1 character to 15 but it's always followed by voice.

I am just stummped, I know there is a way to do this and for some reason I
just can't brain on this one.

I can get the date into a column and I can get Mkt, Market Name, Call Type,
Ave Age(hrs) all into headers. I can get the Mkt filled in but then I get
stumped on the Market name.

Does that answer your questions?

 

[Guest]

It should not be too hard to do. Post 2-3 actual records.

 

[Guest]

Report is as of 07:03 ET, Friday, 10-13-06

Mkt Market Name CallType Avg Age(hrs)

 

[Guest]

Try this --
SELECT Left([memofield],InStr([memofield]," ")-1) AS Mkt,
Trim(Right(Trim(Left([memofield],InStr([memofield],"voice")-4)),Len(Trim(Left([memofield],InStr([memofield],"voice")-5)))-Left([memofield],InStr([memofield],"
")))) AS [Market Name],
Val(Trim(Mid([memofield],InStr([memofield],"voice")+5))) AS [Age(hrs)]
FROM Girlshawn;

 

[Guest]

Hello Karl,

This almost worked. I started to play with it and realized I need to move
the first 2 lines;
the date line
the blank line

I wanted to try and figure it out myself and fought with it most of the
afternoon yesterday.

I can't figure it out.

How do I remove the first 2 lines so this will work?

When I manually remove the first 2 lines this little piece of heaven makes
life awesome.

Thanks again for your input.

Shawn

Try this --
SELECT Left([memofield],InStr([memofield]," ")-1) AS Mkt,
Trim(Right(Trim(Left([memofield],InStr([memofield],"voice")-4)),Len(Trim(Left([memofield],InStr([memofield],"voice")-5)))-Left([memofield],InStr([memofield],"
")))) AS [Market Name],
Val(Trim(Mid([memofield],InStr([memofield],"voice")+5))) AS [Age(hrs)]
FROM Girlshawn;

Report is as of 07:03 ET, Friday, 10-13-06

Mkt Market Name CallType Avg Age(hrs)
--------------------------------------
01 Louisville Voice 4.4
02 New Orleans Voice 5.0
03 Miami Voice 5.0
04 Orlando Voice 4.8

 

[Guest]

Now I think I understand your data. It is ALL in one memo record.

You need to copy and paste special into Excel spreadsheet as text. Then
import into a text field in Access. Using this method the separate data will
be in it's own record.

Then use what I gave you.

Hello Karl,

This almost worked. I started to play with it and realized I need to move
the first 2 lines;
the date line
the blank line

I wanted to try and figure it out myself and fought with it most of the
afternoon yesterday.

I can't figure it out.

How do I remove the first 2 lines so this will work?

When I manually remove the first 2 lines this little piece of heaven makes
life awesome.

Thanks again for your input.

Shawn

Try this --
SELECT Left([memofield],InStr([memofield]," ")-1) AS Mkt,
Trim(Right(Trim(Left([memofield],InStr([memofield],"voice")-4)),Len(Trim(Left([memofield],InStr([memofield],"voice")-5)))-Left([memofield],InStr([memofield],"
")))) AS [Market Name],
Val(Trim(Mid([memofield],InStr([memofield],"voice")+5))) AS [Age(hrs)]
FROM Girlshawn;

Report is as of 07:03 ET, Friday, 10-13-06

Mkt Market Name CallType Avg Age(hrs)
--------------------------------------
01 Louisville Voice 4.4
02 New Orleans Voice 5.0
03 Miami Voice 5.0
04 Orlando Voice 4.8

:

It should not be too hard to do. Post 2-3 actual records.

:

The data segments are as follows:

Report is as of HH:MM ET, Day, MM-DD-YY

Mkt MarketName CallType Avg Age(hrs)
-------------------------------------
## Text (This is a different count) but it's always followed by Voice ##

So the data segments are relatively easy except for Market Name
Market Name causes me issues.

It ranges from 1 character to 15 but it's always followed by voice.

I am just stummped, I know there is a way to do this and for some reason I
just can't brain on this one.

I can get the date into a column and I can get Mkt, Market Name, Call Type,
Ave Age(hrs) all into headers. I can get the Mkt filled in but then I get
stumped on the Market name.

Does that answer your questions?

:

There probably is no easy way but I would need to know more. Are the data
segments in the memo field separated in some manner like with dashes, commas,
five spaces, or each occupies 15 characters.

Is there always text followed by a number in the second position?

Do the records always contain the same number of data elements? What is
between the third and fifth element if the fourth is missing/null?

:

I have a memo field that is populated from an exchange import. The data is
the excat same format in every entry. I want to turn this data into a new
table using the memo field to populate the rows and columns.

I figured I could use the trim function or something like that but I'm
wondering if there might be an easier way.

I hope that question makes sense.