thanks for your reply. You are correct that I would like some form of
validation that if its a three day course I can only add three days of
information into the table.
I will be honest though, I have no idea what to do with your code.
Funny, that's what the OP said in the other thread. Did you read the
break down I posted there?
Here is a repost:
could I bother you to break it down a bit please?
Below is some VBA code to create a new .mdb file containing three base
tables (Students, Classes and Enrolment) and an auxiliary table
(Sequence table of integers), including Validation Rules (replacing the
CHECK constraints to be more Access-friendly), referential integrity
(foreign keys), UNIQUE constraints (but no PKs) plus limited test data.
Note I've used text for ID columns to aid data readability.
[At this point you should run the below VBA code (search this post for
"<VBA code starts>") to create the example MDB and have it open in
Access before proceeding.]
Take a look at the Enrolment table:
SELECT class_ID, seating_capacity, student_ID, seat_number
FROM Enrolment
ORDER BY class_ID, seat_number;
Note all classes are currently full.
To demonstrate the constraints work as intended, first try to add enrol
a student twice on the same course:
UPDATE Enrolment
SET student_ID = 'Katewudes'
WHERE student_ID = 'Tinatotac';
"The changes you requested to the table were not successful because
they would create duplicate values".
Attempt to add another student to an already full course:
INSERT INTO Enrolment (class_ID, seating_capacity, student_ID,
seat_number)
VALUES ('Jet4.0SP8', 3, 'Lisadefus', 4);
generates the error, "seat_number cannot exceed seating_capacity";
INSERT INTO Enrolment (class_ID, seating_capacity, student_ID,
seat_number)
VALUES ('Jet4.0SP8', 3, 'Lisadefus', 4);
generates the error, "You cannot add or change a record because a
related record is required in table 'Classes'": in other words, the
seat capacity for this class is not 4;
INSERT INTO Enrolment (class_ID, seating_capacity, student_ID,
seat_number)
VALUES ('Jet4.0SP8', 3, 'Lisadefus', 3);
generates the error, "The changes you requested to the table were not
successful because they would create duplicate values": in other words,
that seat is taken.
To add a student we first need to increase the class capacity:
UPDATE Classes
SET seating_capacity = 5
WHERE class_ID = 'Jet4.0SP8';
Note the CASCADE referential action changes the values in the Enrolment
table. Then enrole the student...
Well, instead of having to generate a seat sequence number and get the
seat capacity let's use a helper procedure**:
CREATE PROCEDURE Enrole (
arg_student_ID CHAR(9),
arg_class_ID CHAR(9) = 'Databases'
)
AS
INSERT INTO Enrolment (class_ID, seating_capacity, student_ID,
seat_number)
SELECT C1.class_ID, C1.seating_capacity, S1.student_ID, MIN(Q1.seq) AS
seat_number
FROM Classes AS C1, Students AS S1, Sequence AS Q1
WHERE C1.class_ID = arg_class_ID
AND S1.student_ID = arg_student_ID
AND Q1.seq > (
SELECT IIF(MAX(E1.seat_number) IS NULL, 0, MAX(E1.seat_number))
FROM Enrolment AS E1
WHERE E1.class_ID = C1.class_ID
)
AND NOT EXISTS (
SELECT *
FROM Enrolment AS E1
WHERE E1.class_ID = C1.class_ID
AND E1.student_ID = S1.student_ID
)
GROUP BY C1.class_ID, C1.seating_capacity, S1.student_ID
HAVING MIN(Q1.seq) <= C1.seating_capacity;
To execute the proc, use:
EXECUTE Enrole 'Lisadefus', 'Jet4.0SP8';
BTW attempting to execute a second time does not generate an error by
design; instead, check the records (rows) affected property: it will
either be 1 or 0 depending on whether the INSERT was successful. This
is merely an alternative approach, one that avoids errors: personally I
prefer to catch the errors to give feedback on *why* the INSERT failed.
** In case you are not in ANSI-92 query mode (see
http://office.microsoft.com/en-us/assistance/HP030704831033.aspx), here
is the equivalent Access-friendly SQL:
PARAMETERS arg_student_ID Text ( 9 ), arg_class_ID Text ( 9 );
INSERT INTO Enrolment (class_ID, seating_capacity, student_ID,
seat_number)
SELECT C1.class_ID, C1.seating_capacity, S1.student_ID, MIN(Q1.seq) AS
seat_number
FROM Classes AS C1, Students AS S1, Sequence AS Q1
WHERE C1.class_ID = arg_class_ID
AND S1.student_ID = arg_student_ID
AND Q1.seq > (
SELECT IIF(MAX(E1.seat_number) IS NULL, 0, MAX(E1.seat_number))
FROM Enrolment AS E1
WHERE E1.class_ID = C1.class_ID
)
AND NOT EXISTS (
SELECT *
FROM Enrolment AS E1
WHERE E1.class_ID = C1.class_ID
AND E1.student_ID = S1.student_ID
)
GROUP BY C1.class_ID, C1.seating_capacity, S1.student_ID
HAVING MIN(Q1.seq) <= C1.seating_capacity;
I hope this gives you enough info to implement the approach in your
app, or at least give you some ideas on how to proceed.
As promised, here follows the VBA code to reproduce the test database.
The VBA can be executed from anywhere e.g. open Excel, create a new
blank workbook, navigate the Visual Basic Editor (e.g. ctrl+F11), add a
new blank Standard Module (e.g. from the menu, choose: Insert, Module),
copy+paste in the code and run it (e.g. choose: Debug, Run To Cursor):
' ---------<VBA code starts>---------
Sub CreateTempDB()
Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
' Create database
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
' Create Tables
With .ActiveConnection
.Execute _
"CREATE TABLE Students ( student_ID CHAR(9)" & _
" NOT NULL UNIQUE );"
.Execute _
"CREATE TABLE Classes ( class_ID CHAR(9)" & _
" NOT NULL UNIQUE, seating_capacity INTEGER" & _
" NOT NULL, UNIQUE (seating_capacity, class_ID)" & _
");"
.Execute _
"CREATE TABLE Enrolment ( class_ID CHAR(9)" & _
" NOT NULL, seating_capacity INTEGER NOT" & _
" NULL, CONSTRAINT fk__Enrolment__Classes" & _
" FOREIGN KEY (seating_capacity, class_ID)" & _
" REFERENCES Classes (seating_capacity, class_ID)" & _
" ON DELETE CASCADE ON UPDATE CASCADE, student_ID" & _
" CHAR(9) NOT NULL CONSTRAINT fk__Enrolment__Students" & _
" REFERENCES Students (student_ID) ON DELETE" & _
" CASCADE ON UPDATE CASCADE, UNIQUE (class_ID," & _
" student_ID), seat_number INTEGER NOT NULL," & _
" UNIQUE (class_ID, seat_number) ) ; "
.Execute _
"CREATE TABLE Sequence (seq INTEGER NOT NULL" & _
" UNIQUE);"
' Create helper procedure
.Execute _
"CREATE PROCEDURE Enrole ( arg_student_ID" & _
" CHAR(9), arg_class_ID CHAR(9) = 'Databases'" & _
" ) AS INSERT INTO Enrolment (class_ID, seating_capacity," & _
" student_ID, seat_number) SELECT C1.class_ID," & _
" C1.seating_capacity, S1.student_ID, MIN(Q1.seq)" & _
" AS seat_number FROM Classes AS C1, Students" & _
" AS S1, Sequence AS Q1 WHERE C1.class_ID" & _
" = arg_class_ID AND S1.student_ID = arg_student_ID" & _
" AND Q1.seq > ( SELECT IIF(MAX(E1.seat_number)" & _
" IS NULL, 0, MAX(E1.seat_number)) FROM Enrolment" & _
" AS E1 WHERE E1.class_ID = C1.class_ID )" & _
" AND NOT EXISTS ( SELECT * FROM Enrolment" & _
" AS E1 WHERE E1.class_ID = C1.class_ID AND" & _
" E1.student_ID = S1.student_ID ) GROUP BY" & _
" C1.class_ID, C1.seating_capacity, S1.student_ID" & _
" HAVING MIN(Q1.seq) <= C1.seating_capacity;"
End With
' Create Validation Rules
Dim jeng
Set jeng = CreateObject("JRO.JetEngine")
jeng.RefreshCache .ActiveConnection
.Tables("Classes").Columns("seating_capacity") _
.Properties("Jet OLEDB:Column Validation Rule").Value = _
"> 0"
.Tables("Classes").Columns("seating_capacity") _
.Properties("Jet OLEDB:Column Validation Text").Value = _
"seating_capacity must be greater than zero"
.Tables("Enrolment") _
.Properties("Jet OLEDB:Table Validation Rule").Value = _
"seat_number <= seating_capacity"
.Tables("Enrolment") _
.Properties("Jet OLEDB:Table Validation Text").Value = _
"seat_number cannot exceed seating_capacity"
jeng.RefreshCache .ActiveConnection
' Create test data
Dim con
Set con = CreateObject("ADODB.Connection")
con.ConnectionString = .ActiveConnection.ConnectionString
Set .ActiveConnection = Nothing
End With
With con
.Properties("Jet OLEDB:Global Partial Bulk Ops") _
.Value = 1 ' partial completion
.Open
.Execute _
"INSERT INTO Students (student_ID) VALUES" & _
" ('Norarules');"
.Execute _
"INSERT INTO Students (student_ID) VALUES" & _
" ('Katewudes');"
.Execute _
"INSERT INTO Students (student_ID) VALUES" & _
" ('Tinatotac');"
.Execute _
"INSERT INTO Students (student_ID) VALUES" & _
" ('Lisadefus');"
.Execute _
"INSERT INTO Students (student_ID) VALUES" & _
" ('Peteradel');"
.Execute _
"INSERT INTO Classes (class_ID, seating_capacity)" & _
" VALUES ('Databases', 5);"
.Execute _
"INSERT INTO Classes (class_ID, seating_capacity)" & _
" VALUES ('Normalize', 4);"
.Execute _
"INSERT INTO Classes (class_ID, seating_capacity)" & _
" VALUES ('Jet4.0SP8', 3);"
.Execute _
"INSERT INTO [Sequence] (seq) SELECT (SELECT" & _
" COUNT(*) FROM Students AS T2 WHERE T1.student_ID" & _
" <= T2.student_ID) FROM Students AS T1;"
' Fill Enrolement 'randomly'
.Execute _
"INSERT INTO Enrolment (class_ID, seating_capacity," & _
" student_ID, seat_number) SELECT C1.class_ID," & _
" C1.seating_capacity, S1.student_ID, Q1.seq" & _
" FROM Classes AS C1, Students AS S1, Sequence" & _
" AS Q1;"
.Close
End With
End Sub
' ---------<VBA code ends>---------
