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.
What you are not telling me is what you, the OP, expect from me, the
respondent.
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 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.
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 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? 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. 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.
Jamie.
--