Memo fields

[Guest]

I am converting an Informix database into Access. One of the fields in
Informix is a memo field containing 16 lines of description. In Informix this
was displayed as one line of 30 characters (the main description) and fifteen
seperate lines of 55 characters (approx). So, Informix allows the user to
break up the memo field into logical lines and to only accept the right
number of characters per line on entry/amendment. If a search is done, they
only enter the criteria in the main description and this searches the entire
memo field. e.g. *METAL*. Is there any way in Access to reproduce .....
a) The breaking up of a memo field and limiting the number of characters
that can be entered/amended on the form?
b) Mimicking the search so that criteria entered in the 30 char main
description actually searches the entire memo field?.

 

[Guest]

Hi.

Informix is an object-relational database, while Access is a relational
database. While Informix allows storage of subtypes of an object within the
same record of the object, but Access does not. These subtypes are often
stored in another table, either with a one-to-one relationship or a
one-to-many relationship. However, if the cardinality is more like one to
two (or three), then these two (or three subtypes) could be added to the same
table as additional fields. It depends upon the situation.

For the cardinality of zero to fifteen in your Informix memo field, it
appears that this would be a one-to-many relationship in Access. A search
can be made from a query that joins the two tables, but both of these fields
would need to be searched.

However, if you want to keep the amount of labor for the conversion at a
minimal, then keep the type and subtypes of the Informix memo field all
inside one record in one Access memo field, and make the searches in the one
field. For display purposes on a very minimal amount of forms and queries,
parts of the memo field's text can be parsed for the user in unbound text
boxes on a form, or as calculated columns in a query. For example:

SELECT ID, Left(MemoFld, 30) AS MainDesc, Mid(MemoFld, 31, 55) AS Category
FROM tblMyTable;

.. . . where MemoFld is the name of the memo field, MainDesc will display the
first 30 characters in the memo field, Category will display the next 55
characters in the memo field, and tblMyTable is the name of the table.

However, this presents a problem if each of these "lines" within the memo
field aren't padded with extra spaces whenever the number of characters
within the line is less than the maximum. This wastes disk space and time.
Keeping the memo field record intact, but padding with spaces, would solve
both question a) and b), but a) would require a bit of form design
manipulations and programming to limit the number of characters per
artificial "line."

My advice is to separate the Infomix memo field into two tables, with a
one-to-many relationship, then create queries that join the two tables. This
would solve question a). Question b) would be solved by searching two fields
for a value, not one.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hi
Thanks for that.
It may be a bit wasteful but I have included the individual description
fields from the Informix memo field in my table as well as the memo field.
The form allows the user to enter the correct number of characters in each
of the individual fields and 'After Update' I update the Access memo field by
concatenating all the individual description fields.
The search screen then uses the memo field and not the individual fields.
May not be the idea solution, but it works.
Once again.....many thanks.

 

['69 Camaro]

As long as you've got the disk space and can guarantee that the content of
the memo field matches the contents of the individual text fields, I'd call
that having your cake and eating it, too. ;-)

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.