Re: Mutivalued datatypes considered harmful

Out of interest, and well off topic for this group - what would a sql
query return if you brought back the "hobbies" field in an sql query - say
for example to populate a datagrid? Would it return multiple rows (one
for each hobby?) or would it somehow convert the multi-value field into a
single "flat" field?

It is gives you a reults almost idencital to when you ust a t-file
transalate in pick to anther talbe with a mv set retured. The diffrenc is
that each value is seperated by a commna, and not a space like in PICK..

So, the reuslts restunred by the query are sepeateed by a commna.

Eg

select * from file where Hobby = "Fishing" and Hobby = "Chess"

FirstName LastName Hobby
Albert Kalllal Fishing
John Smith Chess
Simon v Fishing, Chess
T Murthi Fishing, Chess, Painting

Also, are the extensions to Access SQL a "standard" or a Microsoft fudge
to make it work ?

I don't think the indusry has a adopted a particlar standard yet. So, no, it
is not a standard (however, both mysql, and Oracle alwasy had exteneiosn
that could pull out data from a single field sepetared by comma's...and ms
sql does not - so the mysql/oracle feature was never consided multi-vlaued.
The mysql/oracle function was handy when someone stuffed more then on peice
of informaton into a field - however, if you typed in a extra spaces between
the data, or non commas, then things would not work....a kluge at best.

The extensions to ms-access are real and signcivnetly in this matter. Note
that in the above, your sql syntax does not change..but, it does work!!

So, there is lots of quirks and differnces in versions of sql in the
indusutry. However, to simply answer your question...the sql has been
extened to deal with this, and it is not a industry standard. However, do
note that the MAIN reason for these extensions in ms-accees in the first
place was so that ms-accesss would work with Share Point services, which are
XML based. So, many lists, and features of SharePoint are XML based, and the
extensions to ms-access allow ms-access to use what is called sharepoint
lists (which are XML, and in effect are multi-valued). So, at least
ms-access realiaies that if you use XML, you need extensions to the query
lanauge to make it really usefull.

I think this reliazions is a signcialty trend on MS's part. Once all the
foggoy reprots of horrors of ignoring cod relatonal rules blows over (and
even MORE sigivnelty is going to be when people acutally realize those
quiers DO WORK in ms-access!!! Remember, these people can't think in MV
termss..and off the bat think it is a dumb idea. However, ONCE users realize
that the query process does work on these fields..the ball game is going to
complete change. The fact that XML pushed this need in the query lanuge
system is gonig to make a lot of people go...ah-ha...this is very nice!! and
makes perfect sense.

In fact, the non educatonted users will start uing the query lanuge, and
NEVER GIVE it a 2nd thought that they are doing somthing that should not
work....kind like how pick is to new users!!

Does Access now allow dependant multi-values (ie pick like) or would you
still need to normalise this?

Yes, you have to move the data out to another talbe. Note that in our above
example of hobiies, we could/should perhaps have crated a another talbe
called tblHobbies, and we might even have seval extra (dependent fields as
you say) such as the type of hobbie (cooking, sports, Excersize).

Just like in mv land where we often store a set of multi-values that
transloates to a lookup table, the same would be done for ms-access.

So, we would have:

select * from file where Hobby = "Fishing" and Hobby = "Chess"

FirstName LastName Hobby
Albert Kalllal 1
John Smith 2
Simon v 1,2
T Murthi 1,2,3


So, to get/use the contorling/depdnet values, you would in fact move them to
the other table

tblHobbies
id Hobby HobbyType
1 Fishing Sports
2 Chess Games
3 Painting Educational

I not played with the query processor, but you CAN have ms-access display
the lookup values for the above two tables. However, RIGHT NOW while you can
get ms-access to DISPLAY the lookup values, your query would actually be
written as

select * from file where Hobby = "1 and Hobby = 2

Now, pick always had a REVERSE translation available for the above (but, few
of us used it). In other words, a T-FILE translate did support a conversion
of the "text" values back to the number values, and THEN execute the AQL
select (you did have to crated the records to do so in pick). However, most
of use would have simply used the <8> correlative conversion, and that would
ALLOW us to continue to use the text values in the example of multiple "ID"
stored in a field (remember, we are doing a t-file to a id in another
record..and this works well in pick...we are NOT doing a t-file to a mv-set
in another file which in pick is not very good..and returns the list
separated by spaces). So, t-file on a mv ser of data in pick works well, and
if you use correlative conversion (amc 8) to lookup the values, you can
CONTINUE to use the text based values IN PICK for searching hobbies.

So, right now, the controlling/dependent issue is not much of a issue, since
you just move out the data to another table. You would then use muti-value
id's stored in the ms-access multi-value field (just like we OFTEN do in
pick). And, as mentioned, ms-access even supports lookups to the translated
multi-values in this case and WILL DISPLAY the text values from the other
table (and, by the way, this works well in pick also). However, ms-access
does NOT support the concept of conversion vs correlative. That means if we
ARE using a lookup table for the text description of the ID, then pick
allows us to continue to use the text description in the query...ms-access
DOES NOT in this case!! (as above, I explained by moving our lookup in pick
from amc7 (conversion) to am8(correlative), we would take a hit on
processing, but would STILL be able to use the text values of "Fishing" and
"chess" in the above aql example). You can't do this in ms-access right now
WHEN you store "id" values for a lookup. You have to use the id values in
your sql query. (I only tested this a bit...and there might be a way to use
the text values, but I found I had to use the number values in that case)

Note that pick gives you two choices/solutions for the above...you can use
correlative..or the reverse lookup (that few of us use). In the case of
reverse lookup for t-file, pick query processor would convert the two text
values of Fishing and chess to there respective id values, and THEN execute
the query (this means would could put our t-file in am7 conversion..and it
would still work -- however, most of us just move it to amc8
correlative..and take the extra hit on processing however).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
http://www.members.shaw.ca/AlbertKallal


.

Relevant Pages

  • Re: Mutivalued datatypes considered harmful
    ... query return if you brought back the "hobbies" field in an sql query - say ... sql does not - so the mysql/oracle feature was never consider multi-valued. ... The extensions to ms-access are real and significant in this matter. ... translates to a lookup table, the same would be done for ms-access. ...
    (comp.databases.pick)
  • Re: Using criteria in select query for recoding of variables
    ... find a way without the lookup table. ... The greatest book is probably the query designer, at least, for start. ... You can take a look at the generated SQL. ... or Skill, is a field, and if a table is a junction table, it is often made ...
    (microsoft.public.access.queries)
  • RE: Convert RecordSet to comma separated values
    ... Create a Lookup Transform on the pipe line and use your SELECT DISTINCT ... I would like to convert RecordSet to comma separated values in my SSIS 2005 ... I populate a recordset usinq a SQL task: ... I'd like to run a Data Flow Task with a query that would filter its ...
    (microsoft.public.sqlserver.dts)
  • Query on table with a lookup
    ... I created a table with a lookup field to another table. ... I've got a module in which I am using a SQL statement ... created a Query with it. ... CorporateVMXExt table, the field is Dept. ...
    (microsoft.public.access.queries)
  • Re: Mutivalued datatypes considered harmful
    ... SQL, but wouldn't the "DISTINCT" modifier then restrict this to one row? ... So, the above query is perfectly legal!!!, and WILL WORK on multi-value ... The sql in ms-access has been modified. ... supposed problems of multi-value fields does work!! ...
    (comp.databases.pick)