Re: Mutivalued datatypes considered harmful

I suspected that Access SQL would support such a syntax

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?

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

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

I'm thinking that perhaps in the near future SQL may become a useful query
tool for MV (pick-like) databases without us having to try and normalise our
data first....

Regards
Simon

--
================================
Simon Verona
Dealer Management Service Ltd
Stewart House
Centurion Business Park
Julian Way
Sheffield
S9 1GD

Tel: 0870 080 2300
Fax: 0870 735 0011

"Albert D.Kallal" <PleaseNOOOsPAMmkallal@xxxxxxx> wrote in message
news:Q2Jvg.210348$iF6.143740@xxxxxxxxxxx
As for the query (for selecting people with specific multiple hobbies),
wouldn't it be logical that the following would work:

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

I'm not quite sure what the resultant query would return though!! Using
standard RD theory, this would return two rows, with all the fields being
identical except for the "Hobby" field... I'm not a massive expert in
SQL, but wouldn't the "DISTINCT" modifier then restrict this to one row ?
If so I wonder what would happen to the Hobbies field!

Well, in normal systems, you don't have muti valued fields. what that
article FAILS TO MENTION is that the sql
in ms-access NOW SUPPORTS this....

So, the above query is perfectly legal!!!, and WILL WORK on multi-value
fields...

then presumably the SQL language needs modifying to cope

The sql in ms-access has been modified. Not only that, both the DAO, and
ADO object model have been also extended...

So, those people in the article were NOT aware that you can do this, and
the supposed problems of multi-value fields does work!!

As I point out in the other thread, in some ways, the sql extensions are
easier to use then the pick query language...

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



.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)