Selecting either one or several reasons

[BruceM]

I am working on a database to generate a report (in the general sense) about
production processes in a manufacturing facility. There are six possible
reasons for generating the report; one of the reasons is Other, which needs
to include a description of what that means.
In the current Word version of the report there are six check boxes,
including the one for Other. The user selects as many of the check boxes as
are applicable (usually one, sometimes two, very rarely three). At least
one must be selected.
In the Access version of the report I could have six yes/no fields, plus a
place for the description when Other is selected, but that seems clumsy. I
suppose I could use a multi-select list box, but then if two reasons are
selected I would need to turn that into two records in the related Reasons
table. Also, it seems to me that the Reasons table would need a text field
for the description if Other is selected. I can't see where else to put
that information
Another part of this is that the selected reasons need to appear in a
subform (I suppose) when somebody returns to the record. I expect I could
use Duane Hookum's Concatenate function to combine the subform records into
a manageable list.
Is the multi-select list box is a workable strategy? If it is, could the
same principle be applied to a series of unbound check boxes on the form
when a new report record is created? I can't work out how I would turn
multiple selections into multiple records. Or am I over-complicating this?
The simplest to implement would be just to have the six yes/no Reason fields
in the main table. I realize that if another Reason is added it would mean
adding a field to the main table, but the list has stayed the same for some
years; in fact, the Other field is not used very often, so the five other
Reasons are adequate for most reports.

 

[BruceM]

As you know, I don't understand the CREATE TABLE syntax well enough to know
what to do when it doesn't actually create a table. As a consequence, I
don't know what if anything you are telling me about what I would like to
do.

 

[BruceM]

As you know, I don't understand the CREATE TABLE syntax well enough to
know
what to do when it doesn't actually create a table. As a consequence, I
don't know what if anything you are telling me about what I would like to
do.

[First a flippant comment to set the mood:] Personalised answers eh?
-- people will talk <g>; High maintenance client, aren't you <g>?;
You sure like to get your money's worth <g>!; I can take rejection
(sob, sob) <g>; Don't understand SQL or won't understand SQL <vbg>?
etc -- take you pick.

I am trying to learn SQL, and VBA, and a lot of other things. My job has
other requirements, too, some of which involve learning new things, and all
of which involve time. Sometimes I need to make choices. I am learning SQL
as well as I can and as other requirements of my job and life allow, so it's
not a question of my being incapable or unwilling to learn. I do what I
can.
I finally learned that I need to use SQL Server Compatible Syntax in order
to use CREATE TABLE. At least, that's how I understood it. If I look at a
CREATE TABLE statement from your first reply:

CREATE TABLE Reports (
report_ID INTEGER IDENTITY(1, 1) NOT NULL PRIMARY KEY);

I can see that the intention is to create a table named Reports with
report_ID as an integer PK. When I went to the immediate window and
entered:
DoCmd.RunSQL "CREATE TABLE ... PRIMARY KEY"
it created a table with an autonumber PK (rather to my surprise). However,
I don't know what (1,1) signifies.

By the way, I do understand that <<other columns here>> is not part of the
SQL. Even I can figure out that one.

CREATE TABLE ReportReasons (
report_ID INTEGER NOT NULL
REFERENCES Reports (report_ID),
reason VARCHAR(12) NOT NULL,
UNIQUE (reason, report_ID)
)
;

is to create a table named ReportReasons, with report_ID an integer field
that is required, but duplicates are OK (a foreign key, to my way of
thinking). By the way, I ran the code to create Reasons first. Anyhow,
REFERENCES apparently establishes a relationship with Reports. Then there
is VARCHAR(12). I have no guess what that means. Maybe I was supposed to
create ReportReasons before Reasons. Anyhow, there is no relationship with
Reasons, although there is in the VBA version of the SQL you provided in
this post. I realize that there should be. What I don't get is if I should
have been able to figure that out from the SQL.

Then there is:
SELECT R1.report_ID, IIF(A1.reason IS NULL, 'Other (' & R1.reason &
')', R1.reason) AS reason
FROM ReportReasons AS R1
LEFT JOIN Reasons AS A1
ON R1.reason = A1.reason
;

I don't know what I am supposed to do with this. It seems to be a query
sort of thing, but is not stored as a named query, but I can't use RunSQL,
which gives me an error message about needing a valid SQL string or
something like that.

What you are not telling me is what you, the OP, expect from me, the
respondent.

In my original post I wondered about using a multi-select list box in case
there are several reasons. If not that, maybe some unbound check boxes to
accomplish the same thing. After making one or several selections, each
selection would become a record in the ReportReasons. Then I asked:

"Is the multi-select list box is a workable strategy? If it is, could the
same principle be applied to a series of unbound check boxes on the form
when a new report record is created? I can't work out how I would turn
multiple selections into multiple records. Or am I over-complicating this?
"

That was what I wanted to know. Those were the specific question for which
I sought answers. You took it several steps further by pointing out that I
would be storing text whether the reason is one of the standard reasons
(Equipment Failure, let's say), or Other (A wild animal ran off with the
finished product). Excellent point, and well worth implementing, now that I
understand it. I still don't know if I can implement it as I had hoped with
something like a multi-select list box for the standard reasons, and maybe
an input box or something for the Other reason if there is one.

When you posted the message to which I am now replying I saw not only the
many-to-many relationship between Reports and Reasons, but also the
ReasonsUndefined in a one-to-many with Reports, and the union query to bring
together ReasonsDefined and ReasonsUndefined. I suppose the union query
could be the source of a subform that would be visible if the Reports record
is not new, with an option to make the original subform (the one used to
select a Reason) visible if it is necessary to add another Reason, or
something like that.

I could suggest you execute each semicolon-separated SQL statement
against an ADO connection to the mdb (or similar) of your choice,
first removing any obvious comments such as "<<other columns here>>"
because Access/Jet SQL syntax does not support explicit comments
(shame).

I explained how I got the SQL to work. I don't know if this is what you
mean by the above.

I could take a few minutes of my time to do this for you and post a
VBA procedure which you can paste into any VBA environment (e.g. a
Standard Module in the Visual Basic Editor of an Excel workbook) that
will create a new mdb with tables, constraints and test data, run a
test query and show the results in a messagebox, before shelling-out
Access to open the mdb in the user interface for examination there
e.g.

Sub JustForBruceM()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0

Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat

' Create mdb
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection

' Create tables
.Execute _
"CREATE TABLE Reports ( " & vbCr & "report_ID" & _
" INTEGER IDENTITY(1, 1) NOT NULL" & _
" PRIMARY KEY, " & vbCr & "report_name CHAR(15)" & _
" NOT NULL UNIQUE" & vbCr & ") " & vbCr & ";"
.Execute _
"CREATE TABLE Reasons ( " & vbCr & "reason" & _
" VARCHAR(12) NOT NULL PRIMARY KEY " & vbCr & ")" & _
" " & vbCr & ";"
.Execute _
"CREATE TABLE ReportReasonsDefined" & _
" ( " & vbCr & "report_ID INTEGER NOT NULL" & _
" " & vbCr & "REFERENCES Reports (report_ID)," & _
" " & vbCr & "reason VARCHAR(12) NOT NULL" & _
" " & vbCr & "REFERENCES Reasons (reason)," & _
" " & vbCr & "PRIMARY KEY (reason, report_ID) " & vbCr & ")" & _
" " & vbCr & ";"
.Execute _
"CREATE TABLE ReportReasonsUndefined" & _
" ( " & vbCr & "report_ID INTEGER NOT NULL PRIMARY KEY" & _
" " & vbCr & "REFERENCES Reports (report_ID)," & _
" " & vbCr & "reason VARCHAR(12) NOT NULL," & _
" " & vbCr & "CONSTRAINT undefined_reason_cannot_be_defined" & _
" " & vbCr & "CHECK (NOT EXISTS ( " & vbCr & "SELECT" & _
" * " & vbCr & "FROM ReportReasonsDefined" & _
" AS D1, " & vbCr & "ReportReasonsUndefined" & _
" AS U1 " & vbCr & "WHERE D1.reason = U1.reason))" & _
" " & vbCr & ") " & vbCr & ";"

' Insert test data
.Execute _
"INSERT INTO Reasons (reason) VALUES" & _
" ('Because');"
.Execute _
"INSERT INTO Reasons (reason) VALUES" & _
" ('Felt like it');"
.Execute _
"INSERT INTO Reasons (reason) VALUES" & _
" ('Was asked to');"

.Execute _
"INSERT INTO Reports (report_ID," & _
" report_name) VALUES (1, 'Sales');"

.Execute _
"INSERT INTO ReportReasonsDefined" & _
" (report_ID, reason) VALUES (1," & _
" 'Was asked to');"
.Execute _
"INSERT INTO ReportReasonsDefined" & _
" (report_ID, reason) VALUES (1," & _
" 'Felt like it');"

.Execute _
"INSERT INTO ReportReasonsUndefined" & _
" (report_ID, reason) VALUES (1," & _
" 'MVP advised');"

' Create Procedure (Access = stored Query object)
.Execute _
"CREATE PROC GetReportReasons " & _
" AS " & vbCr & "SELECT D1.report_ID AS report_ID," & _
" D1.reason AS reason" & vbCr & "FROM ReportReasonsDefined" & _
" AS D1 " & vbCr & "UNION ALL " & vbCr & "SELECT U1.report_ID," &
_
" 'Other (' & U1.reason & ')' " & vbCr & "FROM" & _
" ReportReasonsUndefined AS U1;"

Dim rs
Set rs = .Execute( _
"EXECUTE GetReportReasons;")
MsgBox rs.GetString

End With
Set .ActiveConnection = Nothing
End With

On Error Resume Next
Shell "msaccess.exe " & Environ$("temp") & "\DropMe.mdb"
End Sub

[As I think I mentioned to you before, I formerly did this in
virtually all my posts but I didn't get any feedback, and asked
whether you'd find it helpful, but I didn't get any feedback.]

I could post a link to one of the many SQL tutorial sites that explain
CREATE TABLE syntax (e.g. http://www.firstsql.com/tutor6.htm#createtable)
and urge you to learn a skill that if not vital then certainly adds
interest if you work with at least one SQL product.

Thanks for the link. It has taken me well over two hours to try out the
things you have suggested, and to run the code and study how it all fits
together, so I really don't have time to study it now, as other aspects of
my job demand my attention. I mention the time I have put into trying to
understand this because I would like to disabuse you of the notion you seem
to have that I am asking to be spoon fed the answer.

I could point out that my SQL DDL is more of a thumbnail sketch than
an implementation script and shouldn't be interpreted too literally; I
could post the same in Tutorial D and I'm sure if you 'suspended
disbelief' for a few moments you could figure out the suggested design
(actually I couldn't post in Tutorial D but you get what I mean <g>).

I thought I understood what you were saying about selecting the actual text
of the Reason rather than a Yes/No field plus text in another field, but I
did not see how I was supposed to incorporate Other. Since that was one of
the questions in my OP, I assumed you meant I was supposed to get from the
thumbnail sketches how to implement that. That was when I got frustrated.

I could reciprocate in kind and post a description of a suggested
structure but, as you know, language is a very important part of the
process of communicating ideas and approaches, and you have not
indicated your expected structure of such a description, despite me
asking you to previously (but I do recall you agreeing in principle
that posting SQL DDL is a good idea), consequently there would be a
high risk of me investing my time unwisely if such a description is
fails to transcend culture, dialect, style, skill level, etc e.g. you
could have a single-column lookup table for your five 'defined'
reasons -- let's call this table Reasons -- then create a
'relationship table' (a.k.a. 'junction table', 'join table', etc) to
model the relationship between reports and reasons -- let's call this
relationship table ReportReasonsDefined -- then create a many-to-many
foreign key Relationship with RI enforced between these two tables but
with a unique constraint (primary key or index no duplicates) on
report_ID (i.e. the foreign key from the Reports table) and reason
(foreign key from the Reasons table) so that there is no duplicate
rows (redundancy) i.e. where a report has the same two defined
reasons. Then you could have a almost identical table for reasons
defined as 'Other' or, put another way, 'undefined' reasons -- let's
call this table ReportReasonsUndefined -- the difference being there
is no 'undefined reasons' table with which to create a relationship
and, because your spec suggests to me that a report can only have one
reason defined as 'Other', therefore the unique constraint (primary
key or index no duplicates) should this time be on report_ID (i.e. the
foreign key from the Reports table). There remains an issue you
haven't addressed in your spec: can the undefined reason (i.e. the
description associated with a reason defined as 'Other' in your
existing design) be the same as a defined reason?

As I think you went on to speculate, Other would different from a defined
reason. I doubt a user would select "Equipment Failure", then type in
"Equipment Failure" under Other. If they do, I'll retrain them or
something. It wouldn't be a big problem.

I think it is a safe
to assume the modality of such a business rule is alethic i.e. if it
was violated the data would lose meaning (an undefined reason that is
defined appears nonsense) therefore there should be a constraint to
enforce the rule. The only way I can think of implementing this in
Access/Jet is to use a table-level CHECK constraint which does a
simple NOT EXISTS check in the ReportReasonsUndefined table for a row
in the ReportReasonsDefined table.

Is a table level check constraint known by another name, because I don't
quite understand what you mean. Anyhow, as I mentioned, it is not worth
worrying about it.

Actually, I can think of another
*approach*, more on this below. The arbitrary distinction between
defined and undefined can be kept 'under the covers' by exposing the
data *combined* in a virtual table (VIEW or stored SELECT Query) --
let's call this virtual table ReportReasons -- via a UNION ALL of the
two tables, exposing either as a concatenation of the label 'Other: '
plus the undefined reason or showing 'Other' for the reason and having
a separate column for 'other reason description', applying an
appropriate label (e.g. '{{NA}}') to the set of defined reasons in the
resultset. And here's the aforementioned alternative approach to the
table-level CHECK constraint: accept there will be duplication
(redundancy) between defined and undefined reason and use UNION in the
virtual table to 'filter out' the duplicates in the resultset. [Please
be aware that this description took about half an hour to write where
as the original SQL DDL sketch took about five minutes to produce plus
another five to correct and wrap as a VBA function.]

I could also be open to taking an alternative approach, just let me
know.

If you have any feedback on any of the points raised above, please
post it here.

One possibility is to have said: "There is a many-to-many relationship
between Reports and Reasons, which means a ReasonsDefined junction table is
needed to resolve the relationship. For UndefinedReasons (Other), there
could be another table structured like ReasonsDefined, but in a separate
one-to-many relationship with Reports. A union query could bring the tables
together as the record source for a subform containing both defined and
undefined reasons. I don't see how you could select several options and
have them turn into several separate records in ReasonsDefined. You would
need to enter them one at a time."

Or something like that. My point is that I am capable of understanding such
concepts, and you could have avoided spending a half hour writing code. If
I needed clarification, I could have followed up with another question. As
it is I'm not sure if I'm supposed to feel guilty about the amount of time
you spent crafting the latest response, or what exactly, but please
understand that I appreciate the effort you have put into the reply, and
have as always made every effort to understand.

 

[BruceM]

Yes, coming from me it would be ;-)

My point exactly.

It means it is an incrementing autonumber, seeded at 1 and
incrementing in steps of 1. Needless to say these are customizable
when you use SQL DDL. FWIW here's the syntax for random autonumber and
replication ID respectively:

ID_col INTEGER DEFAULT GenUniqueID() NOT NULL PRIMARY KEY
ID_col GUID DEFAULT GenGUID() NOT NULL PRIMARY KEY


Well, it's not a foreign key until you consider...


...so the "required, but duplicates are OK" is the 'many' side of a
one-to-many.

I guess we just see things differently. For me, a relationship is
modelled with a table not with a foreign key which for me is a
something that 'references' another table. I think its potentially
dangerous for me to try to translate my SQL mindset into another
mindset when the SQL speaks for itself.


It is a text column of varying ('VAR') width up to and including 12
characters ('CHAR').

I checked the tutorial, and picked up on that.

Well, it's just a query. Run it, as you would any other query!

OK, now I see that I would have ben better off running these by opening a
query, switching to SQL view, and pasting in the code than by using VBA.
Trouble is, I had figured out how to use VBA to execute CREATE TABLE, and
tried to do the same for the SELECT statement.

In my VBA version I tried use the CREATE VIEW ('stored query') syntax
but I got a spurious error (' Unions not allowed in a subquery'),
presumably a bug in Jet, so used the CREATE PROCEDURE syntax instead
but noticed that when it shelled-out to Access (I installed Access
2007 for the occasion <g>!) it the proc didn't show up on the Query
tab, presumably a bug Access, even though it can still be used as a
virtual table in both Access and Jet.

I'm not sure if this is the same thing, but the union query showed up in the
database window in Access 2003.

Well, this group is the tablesdbdesign (Usenet) or Database design
(Office Online) group and, while I won't quibble about you asking
about front end Form controls here, you did mention tables and where
to put 'information', so I think it's reasonable of me to pick up on
those latter points here.

I wasn't sure if it was a design question or a forms question, so I did a
rare (for me) cross post to the Forms group. I had assumed that would show
up in the message header, as it does with mine.

Considering your tables are not yet defined, are you saying that your
first priority is to design the front end forms to the level of detail
of choosing which controls to use? Put another way, do you design your
tables to fit your predetermined forms and controls? I'm not being
judgemental here (though admittedly it's not my approach: I consider
the schema to be the foundations of which the application(s) is built)
but I am very interested in your approach

I asked a question about a particular part of the project, but could see no
need to give an overview of parts of the project that aren't relevant to the
question at hand. I have asked detailed questions that have been ignored, I
assume because of their complexity, so now I try to keep the question to a
specific point. I *absolutely do not* design my tables to fit a
preconceived front end. Each Report could be about one or several
PartNumbers, for instance, but I understand how that needs to work, so I
couldn't see including it in my question. However, I couldn't see how to
handle the Reasons, so I asked about that. When I did so I assumed I would
be selecting predetermined reasons, then adding more information to a text
field if the selected reason was Other, as I believe I described in my OP.
With that schema in mind, I asked about how to manage it in the front end.
Now I see there is a better way to handle the related tables. One reason I
posted to the tables and design group as well as the forms group is because
I wondered if I was taking the best possible approach.

It's something I've often
wondered about Access users e.g. if you are using a bound form then
say you need an updateable table therefore a virtual table with a
UNION must be disregarded in favour of a single base table, so you
would potentially be denormalizing to be able to use bound forms,
which is fine by me

I'm not sure I see your point. I have not tried using a Union query as a
record source, but have often used named queries, or SELECT statements in
the form's Record Source property. I believe a union query is not updatable
(although I could be wrong about that), but if by "virtual table" you mean a
query, then I'm not following you.

(I've often wonder if there is a case to

denormalize in Jet to be able to avoid CHECK constraints -- which most
users seem to do! -- and still be able to write effective integrity
constraints) but it doesn't explain why everyone bangs on about
redundancy and normalizing round here so much.

Sooner or later we all need to decide if we want to store the street number
separately from the street name. In some cases one would want an address
list sorted by street, then number, so those two would be stored separately.
In many other cases there is no need for such a listing (in a directory of
far-flung vendors, for instance), so it makes sense to apply a different
normalization rule (at least to me it does).

Point taken


But it would be redundancy i.e. the same entity (reason) can appear in
two places. I don't really understand this 'self-fulfilling prophesy'
approach to data integrity e.g. we'll train the people not to enter
bad data therefore there will be no bad data. Again, if no one worries
about it and it isn't a big deal then why do people get jumped on with
cries of "Normalize!" when someone posts a design with redundancy?

My point is that in the normal course of things people will not select a
reason, then type in what they have just selected, particularly if the
choice they already made is displayed in front of them. It isn't so much
about training as it is about people not doing more than they need to do.
Indeed, the challenge can be to get people to do as much as they need to do.

A Validation Rule is analogous but not quite the same animal. A
Validation Rule can be row (record) level (confusingly called a Table
Validation Rule!) meaning it can check data in multiple columns of the
same row; a column-level Validation Rule can only refer to one column
of the row. CHECK constraints are truly table-level in that they can
additionally refer to other (all) rows in the same table and even
other tables. A distinction important to this thread is that CHECK
constraints are part of the SQL DDL syntax and Validation Rules are
not.

FWIW CHECK constraints are not visible in the Access user interface,
even the Access2007 guise still lags behind the enhancements to the
engine in many ways. For some scant details see MSDN:

ACC2000: How to Create a Jet CHECK Constraint
http://support.microsoft.com/default.aspx?scid=kb;en-us;201888

but the self-appointed authority on the subject seems to be this guy:

http://groups.google.com/group/micr...2fd3ce3967/c711008cd57d4a36?#c711008cd57d4a36


I'd have had to stop there because the rest...


...is OT for me.


Erm, but it only took me five minutes to write the code...?

Well, something took a half hour, you said.

 

[BruceM]

but if by "virtual table" you mean a
query, then I'm not following you.

Another of those vocabulary problems, methinks. In Access land, the
word 'query' is too generic: 'stored query' (virtual table or VIEW
i.e. can be queried as per a base table), 'parameterized
query' (PROCEDURE i.e. can be executed), an append/update/delete query
(an INSERT/UPDATE/DELETE *statement*), a 'make-table query' (an
abomination <g>) and probably others. It isn't that straight forward
either e.g. a 'stored query' could contain a 'delete query' i.e. a
PROCEDURE with zero parameters.

Sooner or later we all need to decide if we want to store the street
number
separately from the street name... in a directory of
far-flung vendors, for instance

Addresses are hard but for me that one's a no-brainer: don't split
them. One approach is that an address is a single atomic attribute of
a 'mailable' entity, which may be split into subatomic elements for
practical purposes e.g. six lines of 30 character fixed width text to
aid printing (as per the international standard 11180) of labels or a
separate column for city to be able to make geographical
generalizations. But what utility is found at the street/number level
e.g. how many of our customers live on a street called 'Main Street'
or live in a house named 'Dunrobin'. [Actually, that latter example
may hold something, aside from the owner's taste. We once successfully
got a fraudster jailed for ripping off customers by fabricating
pension policies and he operated from a property of that name. The
writing was on the wall (literally 'Done robbing' -- geddit?) My
recollection that the city was Fakenham may be faulty...] Splitting at
street level would make sense at a very large scale (i.e. a high
concentration of addresses in a small geographical area) but for me
this doesn't fit with your 'far-flung' factor.

Your ellipses left out this: "In some cases one would want an address list
sorted by street, then number". Then I went on to say that in a directory
of "far-flung vendors" that wouldn't be necessary. You are arguing against
a point I never made. Actually, it's more in local cases involving the need
for a door-to-door listing that separating the number from the street makes
sense.

And my point is, if a design allows redundancy to happen for whatever
reason (access to the database by means other than via your form,
typos, lack of training, misplaced effort, etc) then it is flawed. If
you are comfortable with the flaws then I'm not here to make you feel
bad about it. After all, SQL is an imperfect relational language and
Access/Jet is an imperfect SQL product. I'd say in the example here
the table-level CHECK constraint to prevent redundant data is
justified but you wouldn't be the first to shun CHECK constraints for
some Access 'purist' reasons or other so don't feel bad about that
either. You'll rarely find me highlighting a flaw without an attempt
at amelioration, mitigation or, dare I say, resolution.

I would consider it a waste of time to guard against the possibility of
somebody needlessly entering the exact same text in two different fields or
related records, particularly since it takes so little for two text fields
to be different from each other. Of all the things I could care about, that
one is low on the list

That's what DEFAULT is for ;-)

Default does not require somebody to provide a complete explanation. It can
require data entry, but it cannot reasonably be expected to compel adherence
to content requirements.