Limiting Records
KneeDown2Up
number of arguments within that table?
i.e I need to set up a table that has a couple of lookup columns, and if the
number of these records > givennumber, then I want to prevent the user from
entering more.
Detail
I have a table of names, classes,times,days - if a given class (within a
given day, time etc) happens to exceed say, 12, then I want to restrict
further input.
Any pointers much appreciated.
David F Cox
add new records. Only select records with data.
or have a field with the maximimum records allowed and check against that.
"KneeDown2Up" <KneeD...@discussions.microsoft.com> wrote in message
news:70370AFC-6C3A-4070...@microsoft.com...
JK
Private Sub Form_Current()
Dim maxRec As Long
maxRec = 9999 ' maximum allowed records
If DCount("[Table_ID]", "YrTable") >= maxRec Then
Me.Form.AllowAdditions = False
Else
Me.Form.AllowAdditions = True
End If
End Sub
Regards/JK
"KneeDown2Up" <KneeD...@discussions.microsoft.com> wrote in message
news:70370AFC-6C3A-4070...@microsoft.com...
Jamie Collins
Approach 1: use referential integrity:
CREATE TABLE Students (
student_ID INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Classes (
class_ID INTEGER NOT NULL UNIQUE,
seating_capacity INTEGER NOT NULL,
CHECK (seating_capacity > 0),
UNIQUE (seating_capacity, class_ID)
)
;
CREATE TABLE Enrolment (
class_ID INTEGER NOT NULL,
seating_capacity INTEGER NOT NULL,
FOREIGN KEY (seating_capacity, class_ID)
REFERENCES Classes (seating_capacity, class_ID),
student_ID INTEGER NOT NULL
REFERENCES Students (student_ID),
UNIQUE (class_ID, student_ID),
seat_number INTEGER NOT NULL,
UNIQUE (class_ID, seat_number),
CONSTRAINT row_level_CHECK_constraint
CHECK (seat_number <= seating_capacity)
)
;
The pros include ease of implementation in Access because the row-level
CHECK constraint can be replaced by a record-level Validation Rule. The
cons include the otherwise-redundant repeating seating_capacity on each
row and the burdensome need to maintain a sequence for seat_number...
Approach 2: 'hide' the need for both the repeating seating_capacity and
the sequence of seat numbers in a table-level CHECK constraint:
CREATE TABLE Enrolment (
class_ID INTEGER NOT NULL
REFERENCES Classes (class_ID),
student_ID INTEGER NOT NULL
REFERENCES Students (student_ID),
UNIQUE (class_ID, student_ID),
CONSTRAINT table_level_CHECK_constraint
CHECK ((
SELECT C1.seating_capacity
FROM Classes AS C1
WHERE Enrolment.class_ID = C1.class_ID
) >= (
SELECT COUNT(*)
FROM Enrolment AS E1
WHERE Enrolment.class_ID = E1.class_ID)
)
)
;
Jamie.
--
KneeDown2Up
(more to learn from than anything) and get back to you if I'm unsure of
anything - thanks again, much appreciated.
KneeDown2Up
Your level of knowledge far exceeds mine as far as this goes, I'm afraid I
need a bit more help with this as I'm not anywhere near to your expert level.
It sounds as though you've grasped exactly what I need to do but I haven't a
clue where to start when it comes to implementing your ideas! Sorry to be so
thick, but could I bother you to break it down a bit please?
Thanks
Jamie Collins
KneeDown2Up wrote:
> It sounds as though you've grasped exactly what I need to do but I haven't a
> clue where to start when it comes to implementing your ideas!
>
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.
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>---------
Jamie.
--
KneeDown2Up
Well, I have to say my learning curve is sooo steep! Brilliant stuff and
it's fascinating. I have the DB running and I can see it's going to be
exactly what I need (once I understand a bit more, I will be able to
customise it accordingly).
Out of curiosity, where you are stating the "SELECT class_ID,..." etc I take
it this is in some form of a module or expression table? Sorry, I am only
used to Excel and whilst I am able to try the database out, it is only
through adding records in a (what I perceive as) conventional way, I am
intrigued to know how to do it via your intructions.
Your response to the above may well answer this next section, but how do I
create a user input, i.e. a form (?) that gives my users as much info as they
need but without giving too many choices outside of what the constraints will
be?
Also, is it possible to make the seat number category incremental and
automatic?
Thanks again for your time, I really appreciate you giving it up to help me,
and it's probably very frustrating for you, dealing with someone with so
little knowledge - thanks for your patience!
Martin
Jamie Collins
KneeDown2Up wrote:
> Out of curiosity, where you are stating the "SELECT class_ID,..." etc I take
> it this is in some form of a module or expression table?
A SELECT query would go in the SQL view of a Query object.
> how do I
> create a user input, i.e. a form (?) that gives my users as much info as they
> need but without giving too many choices outside of what the constraints will
> be?
I recommend you repost your question in microsoft.public.access.forms.
I can't help with forms :(
> Also, is it possible to make the seat number category incremental and
> automatic?
Yes, the procedure I posted does this i.e.
SELECT ... MIN(Q1.seq) AS seat_number ...
WHERE ...Q1.seq > (
SELECT ... MAX(E1.seat_number) ... etc
> Thanks again for your time, I really appreciate you giving it up to help me,
> and it's probably very frustrating for you, dealing with someone with so
> little knowledge - thanks for your patience!
No worries, happy to help.
Jamie.
--