Selecting either one or several reasons

On Mar 23, 11:08 am, "BruceM" wrote:
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.

Apologies, I didn't mean to skew things.

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

Think about what you just said but in general terms e.g. "I would
consider it a waste of time to guard against the possibility of
somebody creating the same relationship twice". So you end up with
possibility of duplicates that you have to filter out with DISTINCT,
UNION instead of UNION ALL, etc with the associated performance hit.
People usually denormalize for *better* performance ;-)

Consider that most regulars, even autonumber PK advocates, for
relationship tables (junction tables)recommend constraining the
natural key, being the compound of the FK columns, as unique including
using the PRIMARY KEY designation.

Jamie.

--

On Mar 22, 2:00 pm, "BruceM" wrote:
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.

Generalizing your question a little, I was thinking about how to take
a list of reasons (e.g. from a listbox control) and get the data into
proposed two-table design.

As always in these circumstances, I would start with the code I want
to be writing. We would need the report_ID followed by up to six
reasons (five defined and one undefined) but here I've got just three
(two defined and one undefined):

EXECUTE AddReasons 1, 'Because', 'Felt like it', 'Mum said so'
;

Problem is, without an updateable VIEW (Query, virtual table) that
JOINs the -Defined and Undefined tables, I can't think of a way of
inserting rows into the two tables in a single operation. For me, the
next best thing would be two procedures, each targeting a single
table, which could be called succession using the same parameter
values where each procedure would 'disregard' the parameters which did
not apply to its target table:

EXECUTE AddReasons1 1, 'Because', 'Felt like it', 'Mum said so'
;
EXECUTE AddReasons2 1, 'Because', 'Felt like it', 'Mum said so'
;

The parameters are optional so should have the NULL value by default
(i.e. if omitted):

arg_report_ID INTEGER,
arg_reason_1 VARCHAR(12) = NULL,
arg_reason_2 VARCHAR(12) = NULL,
arg_reason_3 VARCHAR(12) = NULL,
arg_reason_4 VARCHAR(12) = NULL,
arg_reason_5 VARCHAR(12) = NULL,
arg_reason_6 VARCHAR(12) = NULL

First target the -Defined table, querying the Reasons table to search
only for the Defined reasons present in the parameter list:

INSERT INTO ReportReasonsDefined (report_ID, reason)
SELECT DISTINCT arg_report_ID, N1.reason
FROM Reasons AS N1
WHERE N1.reason IN (arg_reason_1,
arg_reason_2, arg_reason_3, arg_reason_4,
arg_reason_5, arg_reason_6)

Second, target the -Undefined table. The query is a little more
complex because we don't have a table of undefined reasons. What we
can do is create a derived table (nested subquery) using the parameter
values then using a search condition (WHERE clause) to remove the
NULLs and an NOT EXISTS to remove the defined reasons:

INSERT INTO ReportReasonsUndefined (report_ID, reason)
SELECT DISTINCT arg_report_ID, DT1.reason
FROM
(
SELECT arg_reason_1 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_2 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_3 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_4 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_5 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_6 AS reason
FROM Reasons AS N1
) AS DT1
WHERE DT1.reason IS NOT NULL
AND NOT EXISTS
(
SELECT *
FROM Reasons AS N1
WHERE DT1.reason = N1.reason
)

You can manually create these two objects in the Access user
interface, using the PARAMETERS keyword in a Query object, but here's
the equivalent SQL DDL:

CREATE PROCEDURE AddReasons1 (
arg_report_ID INTEGER,
arg_reason_1 VARCHAR(12) = NULL,
arg_reason_2 VARCHAR(12) = NULL,
arg_reason_3 VARCHAR(12) = NULL,
arg_reason_4 VARCHAR(12) = NULL,
arg_reason_5 VARCHAR(12) = NULL,
arg_reason_6 VARCHAR(12) = NULL
)
AS
INSERT INTO ReportReasonsDefined (report_ID, reason)
SELECT DISTINCT arg_report_ID, N1.reason
FROM Reasons AS N1
WHERE N1.reason IN (arg_reason_1,
arg_reason_2, arg_reason_3, arg_reason_4,
arg_reason_5, arg_reason_6)
;
CREATE PROCEDURE AddReasons2 (
arg_report_ID INTEGER,
arg_reason_1 VARCHAR(12) = NULL,
arg_reason_2 VARCHAR(12) = NULL,
arg_reason_3 VARCHAR(12) = NULL,
arg_reason_4 VARCHAR(12) = NULL,
arg_reason_5 VARCHAR(12) = NULL,
arg_reason_6 VARCHAR(12) = NULL
)
AS
INSERT INTO ReportReasonsUndefined (report_ID, reason)
SELECT DISTINCT arg_report_ID, DT1.reason
FROM
(
SELECT arg_reason_1 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_2 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_3 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_4 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_5 AS reason
FROM Reasons AS N1
UNION ALL
SELECT arg_reason_6 AS reason
FROM Reasons AS N1
) AS DT1
WHERE DT1.reason IS NOT NULL
AND NOT EXISTS
(
SELECT *
FROM Reasons AS N1
WHERE DT1.reason = N1.reason
)
;

I've named them AddReasons1 and AddReasons2 respectively but they can
be executed in either order.

Am I over-complicating this?

I don't think so. Remember the code I started with:

EXECUTE AddReasons1 1, 'Because', 'Felt like it', 'Mum said so'
;
EXECUTE AddReasons2 1, 'Because', 'Felt like it', 'Mum said so'
;

That's the simple part, the pay off. I want to make things as easy and
as uncomplicated as possible on the 'front end guy ' -- it could even
be me g -- so I encapsulate the complexity in a SQL procedure.

Jamie.

--