1NF Violations *promoted* in MS Access?

Remember the key phrase here "billable hours"

No joke he I was just paid over $150K during the past 10 months to
untangle the absolutely worse MS Access nightmare I've ever seen. Clients
don't like those "billable hours" of which you speak. It was spreadsheet
design all the way through - with tables storing data for multiple entities,
data for multiple entities being stored in multiple tables, 1NF violations
at every turn, etc. IMHO, MS Access, itself, is at *some* fault here for
ENABLING the idiot who created the mess to get as far as he did. NO, I"m not
blaming the gun for a murder - but if you notice, guns typically have safety
locks and switches that make it hard to do the wrong thing easily. It seems
that with every new version of MS Access, the product makes it progressively
and substantially *easier* to do the wrong thing (specifically, easier to
implement spreadsheet designs in a db). Data integrity goes out the window,
along with runtime performance.

-"Bob"




"Tony Toews [MVP]" wrote in message
...
"Bob Johnson" wrote:

Okay, then two followup questions:

1. Say I come along and have to query this database. How do I query
against
this multi-valued field if I don't have access to the underlying "correct"
tables?

SELECT blah blah blah WHERE MyMultiValuedField = ?????

What do I put for the ????? in the above query?

MyMultiValuedField.Value = ?????
http://office.microsoft.com/en-ca/ac...CH100645681033

2. Say I have to extract data from the database and export it to
godknowswhat. How do I extract this in a way that preserves data integrity
if I don't have access to the underlying "correct" tables? Or will it be
exported as a multivalued filed (comma separated or whatever)?

You can do a sum query against the above value and get the data in the
lookup/master table which you would then append into new table. Then
you can do a similar query to pull in the "junction" table data to
create that table.

Remember the key phrase here "billable hours"

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

SELECT blah blah blah WHERE MyMultiValuedField = ?????

SELECT blah blah blah WHERE
MyMultiValuedColor = "red" and MyMultiValuecolorField = "blue"

The above answer in regular sql is a question I often ask prospective hires
of mine.

care to offer a solution? (the multi-value answer as above does work).

Read carefully the question:

I want all customers who have a favorite color of red and ALSO blue....

I talk about this problem he

http://www.members.shaw.ca/AlbertKal...000000006.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

I suspect Albert meant:

SELECT blah blah blah WHERE
MyMultiValuecolorField = "red" and MyMultiValuecolorField = "blue"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Albert D. Kallal" wrote in message
...
SELECT blah blah blah WHERE MyMultiValuedField = ?????

SELECT blah blah blah WHERE
MyMultiValuedColor = "red" and MyMultiValuecolorField = "blue"

The above answer in regular sql is a question I often ask prospective
hires of mine.

care to offer a solution? (the multi-value answer as above does work).

Read carefully the question:

I want all customers who have a favorite color of red and ALSO blue....

I talk about this problem he

http://www.members.shaw.ca/AlbertKal...000000006.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

The above answer in regular sql is a question I often ask prospective
hires of mine

I hope you don't let them pass simply because they got the SQL statement
"correct" (correct for the MV column deal). I'd also look for them to be
able to articulate the important tradeoffs of MV columns and why they run
contrary to 1NF - and they do from the user's perspective even if "under the
covers" the db engine is creating multiple proper supporting table. I'd also
ensure the candidate can articulate specific problems incurred from 1NF
violations.


RE the following quote from your article (first bullet point)
quote We did not have to create another table to hold the additional phone
numbers (as we *should* in a traditional sql database system create a new
table) /quote

How is the beginner dba supposed to learn how we *should* implement proper
designs (I agree with your parenthetical comment) if we give them MV
columns? MS Access is busy at work here making the improper thing very easy
to do... promoting the spreadsheet metaphore of a database (which is
completely wrong) by providing MV columns - which are akin to merging
columns vertically in Excel. What a shame!

Trying to be generious, I guess MV columns and other such
should-be-non-starters in a db (like a table column defined to get its value
from a query - "geeze") can be understood as possibly okay to have when the
following two assumptions are met:
1. the person using these things is incapable of learning the proper way of
designing a normalized database. If not incapable, then incredibly unlikely
to learn how to do it right anyway...
AND (not OR)...
2. The application in question is guaranteed to never grow beyond the
generally advertised/intended use as a desktop db in support of non
mission-critical data and with relatively few users.

It's like we're saying, "if you're never going to get there anyway, and who
really cares about your data anyway, then, well, let's make your life
easier." The application and it's creator and users are operating in a sort
of sandbox where things don't really matter. At least we're providing them
with a screaming great way to crank out mailing labels, for example. In that
sort of situation I can agree that MS Access would be better than Excel.

Again - please understand that I'm not having a rant here. I'm just
surprised to find such a feature as MV columns and wanted to know what the
Access community thinks about it, and trying for myself to come up with a
reasonable justification for having this and other features that promote the
spreadsheet metaphore of a database - as improper as that metaphore is. It's
kind of like situational ethics... "when is it right to do the wrong thing?"

-"Bob"




"Albert D. Kallal" wrote in message
...
SELECT blah blah blah WHERE MyMultiValuedField = ?????

SELECT blah blah blah WHERE
MyMultiValuedColor = "red" and MyMultiValuecolorField = "blue"

The above answer in regular sql is a question I often ask prospective
hires of mine.

care to offer a solution? (the multi-value answer as above does work).

Read carefully the question:

I want all customers who have a favorite color of red and ALSO blue....

I talk about this problem he

http://www.members.shaw.ca/AlbertKal...000000006.html


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Bob Johnson" wrote in
:

Features like that in question here (plus others added to MS
access in the past couple of years, like a column definition for
which the value is defined on a freaking query)

Can you unpack that and explain what you mean? All I can think of is
lookukp fields, and those have been around since at least Access 95.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Yes - it's lookup fields to which I refer. I guess they've been around a lot
longer than I thought (still doesn't make them a "good thing").

http://www.mvps.org/access/lookupfields.htm

My point isn't to enumerate what I perceive to be the problems of MS Access.
I was just really surprised to see such a blatant encouragement for users to
violate 1NF with these new multi-valued fields (thus my OP here), and was
subsequently referring to other such ways in which Access promotes the
database-table-is-really-a-spreadsheet mentality to neophytes.

-"Bob"


"David W. Fenton" wrote in message
. 1...
"Bob Johnson" wrote in
:

Features like that in question here (plus others added to MS
access in the past couple of years, like a column definition for
which the value is defined on a freaking query)

Can you unpack that and explain what you mean? All I can think of is
lookukp fields, and those have been around since at least Access 95.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Bob Johnson" wrote in message news:%231Wwe%

RE the following quote from your article (first bullet point)
quote We did not have to create another table to hold the additional
phone numbers (as we *should* in a traditional sql database system create
a new table) /quote

Well, in the above, I talking about a *true* multi-value database, not the
new extensions to "jet" that we have. Note the date of the above article is
December 2001!! -- so, I wrote this LONG before ms-access adopted this. In
those true multi-value database systems, a new table is NOT created. Don't
confuse a true multi-value database system with that of a relational
database like "jet". In a traditional "relational" systems (be it the new
JET, or oracle), to achieve the above..you create a new table. In a MV
system..you do NOT have to do *anything* at all.


How is the beginner dba supposed to learn how we *should* implement proper
designs (I agree with your parenthetical comment) if we give them MV
columns? MS Access is busy at work here making the improper thing very
easy to do... promoting the spreadsheet metaphore of a database (which is
completely wrong) by providing MV columns - which are akin to merging
columns vertically in Excel. What a shame!

Well, actually, since ms-access does in fact create the tables, then you
wind up with normalized data..not a spreadsheet. The end user will benefit
from this, and it will take far longer for the appcation to fall apart, and
have to bring in professional help.


1. the person using these things is incapable of learning the proper way
of designing a normalized database. If not incapable, then incredibly
unlikely to learn how to do it right anyway...
AND (not OR)...

It is question of balance. Should you have to know binary counting, or
assembler to use ms-access? A guy sits down and needs to store a persons
favorite foods..and now in ms-access that is trivial. The fact that this
data is actually normalized is terrific thing. Before, the user would
actually try and stuff data in to the one field separated by comma (or go
food1, food2, food3 to store favorite foods). Now, a new table, and separate
child values are stored. To me, this is step forward, not backwards. Should
we prevent users from driving cars by only offering standard transmissions?

2. The application in question is guaranteed to never grow beyond the
generally advertised/intended use as a desktop db in support of non
mission-critical data and with relatively few users.

Well, sure. But, a very large portion of those applications are written by
those users. There is ZILLIONS of successful ms-access applications running
using ONLY macros. This is a question of empowerment to the people. And,
further, in code (both dao/ado, you can work with the multi-data!).

Which is worse:

having users stuff values into one field separated by comma,
or
having ms-access create another table, and have the user store
normalized data?

It seems to me the 2nd approach is 100 times better. Sure, the driver will
never learn how to use a clutch (or normalize data), but in both cases..the
user does not care one bit. They want their reports..and they want to store
this data. They want to drive to work in their automatic car! That is all
they care about. They also don't care about the c++ language used to write
ms-access.

I see the MV fields as encouraging users to not stuff values into a single
field. That is actually good. As a developer, I can use code to extract and
export this data anyway.

Many of us developers are simply asking that a "toggle" feature be enabled
to display those extra tables. Really, that is all we need here to end this
controversy.



Again - please understand that I'm not having a rant here. I'm just
surprised to find such a feature as MV columns and wanted to know what the
Access community thinks about it

Oh, no rant taken at all. I think it is good you ask, debate..and get a
feeling for what this feature means. When this feature was proposed at the
developers conference, you had a near riot. Some of us had to apologize to
the Microsoft developers because we were not kind!!

You have to understand that I am one of the people that tends to see this
feature as a good thing. I suppose the fact that I worked on d3 (pick),
Revelation, IBM's Universe and several others for about 10 years likely
taints my view. Those mentioned systems are mainframe multi-value database
systems that have been in use for 30+ years. I really do like multi-value
database systems.

I also don't consider the new extensions to ms-access a true multi-value
database because in the systems mentioned, ANY field can instantly become
multi-valued, and no new tables need be crated behind the scenes as in a
relational database.

So, the access community has had some lively debate, and we all grown up
people. so, some hard questions on your part is a very good thing.

At the end of the day, we don't have to use macors, nor do we have to use
these new multi-value fields. I can assumer that a lot of users will use
lookup fields, will use multi-value fields, and will use macros....none of
which I use!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Many of us developers are simply asking that a "toggle" feature be
enabled to display those extra tables. Really, that is all we need here to
end this controversy

Agreed.

If we can never get to those underlying tables, then they might as well not
even exist (given that we could only get to denormalized views of the data).
Getting to that normalized data would be a "life saver" for a real dba
coming along behind a neophyte when the Access app takes on for more
importance and # of users than originally planned (happens all the time) and
has to be scaled to something more robust (both the database and client
application).

Thanks for the lively debate.

-"Bob"



"Albert D. Kallal" wrote in message
...
"Bob Johnson" wrote in message news:%231Wwe%

RE the following quote from your article (first bullet point)
quote We did not have to create another table to hold the additional
phone numbers (as we *should* in a traditional sql database system create
a new table) /quote

Well, in the above, I talking about a *true* multi-value database, not the
new extensions to "jet" that we have. Note the date of the above article
is December 2001!! -- so, I wrote this LONG before ms-access adopted this.
In those true multi-value database systems, a new table is NOT created.
Don't confuse a true multi-value database system with that of a relational
database like "jet". In a traditional "relational" systems (be it the new
JET, or oracle), to achieve the above..you create a new table. In a MV
system..you do NOT have to do *anything* at all.


How is the beginner dba supposed to learn how we *should* implement
proper designs (I agree with your parenthetical comment) if we give them
MV columns? MS Access is busy at work here making the improper thing very
easy to do... promoting the spreadsheet metaphore of a database (which is
completely wrong) by providing MV columns - which are akin to merging
columns vertically in Excel. What a shame!

Well, actually, since ms-access does in fact create the tables, then you
wind up with normalized data..not a spreadsheet. The end user will benefit
from this, and it will take far longer for the appcation to fall apart,
and have to bring in professional help.


1. the person using these things is incapable of learning the proper way
of designing a normalized database. If not incapable, then incredibly
unlikely to learn how to do it right anyway...
AND (not OR)...

It is question of balance. Should you have to know binary counting, or
assembler to use ms-access? A guy sits down and needs to store a persons
favorite foods..and now in ms-access that is trivial. The fact that this
data is actually normalized is terrific thing. Before, the user would
actually try and stuff data in to the one field separated by comma (or go
food1, food2, food3 to store favorite foods). Now, a new table, and
separate child values are stored. To me, this is step forward, not
backwards. Should we prevent users from driving cars by only offering
standard transmissions?

2. The application in question is guaranteed to never grow beyond the
generally advertised/intended use as a desktop db in support of non
mission-critical data and with relatively few users.

Well, sure. But, a very large portion of those applications are written by
those users. There is ZILLIONS of successful ms-access applications
running using ONLY macros. This is a question of empowerment to the
people. And, further, in code (both dao/ado, you can work with the
multi-data!).

Which is worse:

having users stuff values into one field separated by comma,
or
having ms-access create another table, and have the user store
normalized data?

It seems to me the 2nd approach is 100 times better. Sure, the driver will
never learn how to use a clutch (or normalize data), but in both
cases..the user does not care one bit. They want their reports..and they
want to store this data. They want to drive to work in their automatic
car! That is all they care about. They also don't care about the c++
language used to write ms-access.

I see the MV fields as encouraging users to not stuff values into a single
field. That is actually good. As a developer, I can use code to extract
and export this data anyway.

Many of us developers are simply asking that a "toggle" feature be enabled
to display those extra tables. Really, that is all we need here to end
this controversy.



Again - please understand that I'm not having a rant here. I'm just
surprised to find such a feature as MV columns and wanted to know what
the Access community thinks about it

Oh, no rant taken at all. I think it is good you ask, debate..and get a
feeling for what this feature means. When this feature was proposed at the
developers conference, you had a near riot. Some of us had to apologize to
the Microsoft developers because we were not kind!!

You have to understand that I am one of the people that tends to see this
feature as a good thing. I suppose the fact that I worked on d3 (pick),
Revelation, IBM's Universe and several others for about 10 years likely
taints my view. Those mentioned systems are mainframe multi-value database
systems that have been in use for 30+ years. I really do like multi-value
database systems.

I also don't consider the new extensions to ms-access a true multi-value
database because in the systems mentioned, ANY field can instantly become
multi-valued, and no new tables need be crated behind the scenes as in a
relational database.

So, the access community has had some lively debate, and we all grown up
people. so, some hard questions on your part is a very good thing.

At the end of the day, we don't have to use macors, nor do we have to use
these new multi-value fields. I can assumer that a lot of users will use
lookup fields, will use multi-value fields, and will use macros....none of
which I use!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Bob Johnson" wrote in
:

My point isn't to enumerate what I perceive to be the problems of
MS Access. I was just really surprised to see such a blatant
encouragement for users to violate 1NF with these new multi-valued
fields (thus my OP here), and was subsequently referring to other
such ways in which Access promotes the
database-table-is-really-a-spreadsheet mentality to neophytes.

I don't think that multi-value fields are nearly as much of an issue
as lookup tables, because, behind the scenese, they've been properly
implemented and a programmer can get access to those
behind-the-scenes structures. It certainly makes Albert's example
query a helluva lot easier, don't you think?

I won't use them myself, but for end users, I think it really is a
good benefit.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Albert D. Kallal" wrote in
:

You have to understand that I am one of the people that tends to
see this feature as a good thing. I suppose the fact that I worked
on d3 (pick), Revelation, IBM's Universe and several others for
about 10 years likely taints my view. Those mentioned systems are
mainframe multi-value database systems that have been in use for
30+ years. I really do like multi-value database systems.

I would say, Albert, that your advocacy of MV databases changed my
mind on the subject.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/