Multiple entries in a text box

[Guest]

Hello,
I have a table in a database, with a text field called "parties-appellant".
The user may enter one name in the field or they may enter several names,
each separated by a comma. I've asked the user to use a comma because I use
a Split function in the VBA code that is part of a form for which the table
is a source. When the user enters a single name into the parties appellant
field, the query in the form works fine. For instance, the user enters "John
Smith" in the field and the query behind the form searches for all instances
in the table where John Smith is the party appellant. But if there is a
parties-appellant field where there is a double entry like: John Smith, Bob
Harris, nothing is picked up. I use the TRIM command and the select
statement is correct. Am I supposed to format the field in the table to
allow for multiple entries?

 

[Guest]

??Am I supposed to format the field in the table to allow for multiple entries?
No as there is no such format. You should not put multiple data in a field.
You should be using a one-to-many table relationship.

But seeing as how you done it here is the way to put your criteria --
Like "*" & [Enter party name] & "*"

This will alow you find the name even if it is the first, in the middle or
at the end.

 

[Larry Daugherty]

OP may be playing in Access 2007. In that case there is a field type
for multiple entries. I don't play there just now and don't know the
field datatype to do it. However, I have advice for one and all:
Don't use that new datatype! It violates relational rules. It will
cause you problems later in your development of your design. When
MVPs are released from their non-disclosure agreements you'll very
likely it enjoys the same approval as "Lookup fields in tables" Check
out www.mvps.org/access

I recommend a correct design instead in which there is a child table

tblparties-appellant

on the many side of a one-to-many relationship with the original
table.
tblparties-appellant will have one name per row.

HTH
--
-Larry-
--

??Am I supposed to format the field in the table to allow for multiple entries?
No as there is no such format. You should not put multiple data in a field.
You should be using a one-to-many table relationship.

But seeing as how you done it here is the way to put your criteria --
Like "*" & [Enter party name] & "*"

This will alow you find the name even if it is the first, in the middle or
at the end.

Hello,
I have a table in a database, with a text field called "parties-appellant".
The user may enter one name in the field or they may enter several names,
each separated by a comma. I've asked the user to use a comma because I use
a Split function in the VBA code that is part of a form for which the table
is a source. When the user enters a single name into the parties appellant
field, the query in the form works fine. For instance, the user enters "John
Smith" in the field and the query behind the form searches for all instances
in the table where John Smith is the party appellant. But if there is a
parties-appellant field where there is a double entry like: John Smith, Bob
Harris, nothing is picked up. I use the TRIM command and the select
statement is correct. Am I supposed to format the field in the table to
allow for multiple entries?

 

[Guest]

Hi Larry,

There is nothing in the non-disclosure agreement that prevents an MVP from
voicing their opinions concerning the new multivalued data type. There is
information available on the Access blog site concerning this new feature. I
don't have a firm opinion on this yet, as I have not even touched a copy of
Access 2007.

From what I have read, the new MVF does not violate database normalization
rules. Here is an article that discusses this new feature in more detail:

http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/print.html

This includes the following quote:

"Suraj tells me that ideally the development team would like the three table
solution to appear in the GUI but that it won’t be possible to do this for
the version currently under development (Access 2007). It may well appear in
later versions."

Personally, I think that Microsoft should either expose the hidden tables in
the GUI, or hold off on introducing this feature. Microsoft's implementation
for Access 2007 appears to be "half-baked".


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Larry Daugherty]

Hi Tom,

Thanks for joining the thread.

I believe that the NDAs prohibit discussing ex-cathedra of
characteristics of the beta product until it's released.

Thanks for the link.

I read the article and understood the author's point of view and
judgement to match my own; right down to whether a concept conforms
to the relational rules and to Microsoft's probable motivations in
providing such "features". My first specific objection is that the
representation of the field in the table design view is not "atomic".
What's represented in the table as the field's content isn't the
field's content at all. Microsoft now even includes some Help that
directly addresses how to write queries to handle some of the issues.

My next objection is that the people who are most likely to use
"Lookup Fields" and other non-relational features in their various
forms are the least likely to be able to address the consequences
later in their application. As John Vinson commented on one of my
rants on this issue: "Billable Hours" (for the people who know the
issues involved to come in and clean up the messes). He wasn't
encouraging the deceptive practice but the consequences of it.

If I were leading the Access group I would probably do all that they
do and even more in trying to flatten the learning curve. A flatter
learning curve leads to more customers who use the tool and become
enthusiastic supporters of the product and the source of repeat
business. I really do understand their motivation (at least some of
it). I don't think that MS is honor bound to observe the relational
model in all representations at all levels.

A problem with these nifty non-relational features is that they are
most helpful to people using only the table object(s) in their (very
Excel like) solution. The problems surface when those tables are used
in other objects later.

I will continue to warn budding developers not to use them.

Regards,

 

[Guest]

Hi Larry,

I believe that the NDAs prohibit discussing ex-cathedra of
characteristics of the beta product until it's released.

For inside knowledge, yes, you are correct. However, MVF has been discussed
in public by Microsoft, in the Access blogs among other places. As long as we
(MVP's) restrict our discussion to information that is publically available,
there is no problem. I don't want you to have the possible impression that
MVP's must express an opinion in favor of new features, or else keep our
opinions to themselves. That is simply not true.

He wasn't encouraging the deceptive practice but the consequences of it.

I believe I read that post. It sounds familiar. I agree with it: billable
hours (if you can get them). The trouble is when one works for a large
company (as I do). There is no such thing as billable hours for salaried
personnel. We will likely get stuck with working lots of really tedious hours
cleaning up such messes. But, I will reserve judgement until I purchase a new
PC, get A2007 loaded, and have a chance to experiment with MVF.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jamie Collins]

From what I have read, the new MVF does not violate database normalization
rules. Here is an article that discusses this new feature in more detail:

http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/print.html

The OP's design seems to be a 1NF violation and looks awfully like the
multivalued examples in the article. Also, the article makes clear
that, although the Access 2007 team are "fully aware of the
implications of introducing this new data type", it remains a violation
of Codd's guaranteed access rule; there is nothing in the article to
refute this.

The two quoted reasons for introducing the multivalued 'type' are
"*Microsoft* is keen for Access to be compatible with SharePoint" and
"The *development team* feels that power users find the creation of
many-to-many joins using three tables conceptually very difficult" (the
emphases are mine). Clearly there were no *external* pressures for the
introduction of this feature.

The article points out that none of the other popular SQL products have
such a 'feature' (Oracle, DB2, MySQL and even Microsoft's own SQL
Server are name-checked). Either Access is way ahead of the pack or
taking a retrograde step. I'm sure there will be less of an outcry
introducing a multivalued 'type' to Access than there would be if the
same was being done to the SQL Server community.

I'm just glad the Access team also had time to get round to fixing that
DECIMAL sort order bug <g>.

Jamie.

--

 

[Larry Daugherty]

Hi Tom,

I'm not and never have been of the opinion that MVPs are required to
have an opinion about any specific thing at all nor that they might be
required to espouse a particular point of view. It's my belief and
hope that they express opinions, or at least guidance, that reflect
what they believe.

My aim in this instance is to try to steer novices away from the
quicksand. In the final analysis they will do what they will do. We
don't rule, we only advise. Thanks be for that!

Regards,

 

[Jamie Collins]

The OP's design seems to be a 1NF violation and looks awfully like the
multivalued examples in the article.

I consulted the big JC on this and he points out there actually is a
normal form for this: it's called the Non-First Normal Form. See:

http://en.wikipedia.org/wiki/Database_normalization

Jamie.

--

 

[Jeff Conrad]

in message:

I believe that the NDAs prohibit discussing ex-cathedra of
characteristics of the beta product until it's released.

Not quite true. Any Beta NDAs we have signed prohibit discussing things that are not publicly
available. Once Beta 2 was released in June, the field was pretty much wide open. Since any person
can download the beta and test it out for themselves we're pretty much free to discuss anything,
including voicing our own opinions. Things we cannot discuss are issues/features being resolved in
later builds and/or conversations/discussions held in the private beta groups.

 

[Pat Hartman \(MVP\)]

The pressure to implement this data type was internal. The powers that be
are really pushing SharePoint and SharePoint, which is a non-relational
database, supports this data type as well as the two other non-relational
data types added in the pending release. These data types are bad on many
levels including the fact that they can't be "upsized" using the upsizing
wizard.

 

[Jamie Collins]

The pressure to implement this data type was internal. The powers that be
are really pushing SharePoint and SharePoint, which is a non-relational
database, supports this data type as well

I wonder how our very own Albert D. Kallal MVP (MVP = multi-value
pundit <g>) feels about this new 'type' for Access:

http://www.dbdebunk.com/page/page/3368007.htm

If it's true the implementation was right for PICK because it was
designed from scratch for this data model and it has the needed
operators, does this mean the Access team have got MV wrong?

Jamie.

--

 

[Pat Hartman \(MVP\)]

I managed a project that used Pick but I never worked with it myself. I
haven't played with the new syntax. I looked at some examples and found
them confusing. you get different results depending on whether you refer to
the group or the field (I don't remember what the two representations are
called) and I think that there are kinds of complex criteria that you can't
seem to apply. For example finding people that like both bananas AND peanut
butter.