Use a variable number of fields

I dont know if I have phrased this correctly but there must be a more elegant
solution than I am using now.

I have a database for managing education courses, I have tables for

Attendees
Events
registration
Payments
Events Location

the problem I have is that each course has either 1, 3 , 5 days I need to
collect attendance data and use the database for producing timetables.

How would you set up the database to input the dates for each course and
collect registration data.

At the moment my solution is to collect attendance data I have fields on the
registration table for Day 1 , 2 3, 4 5, which link to a form and if it is
only a one day course I only complete that part of the form.

I Also input the dates into the table events location to generate
timetables. the Events table has start date and end date field s which i
independently complete. I know I am repeating data input and this is where
error can occur.

I know this is not explaining my problem well, but any help would be
appreciated

thanks

Phil

Phil

Adding fields to add data is a very spreadsheetly thing to do (in a
spreadsheet, it may be the ONLY way). But in a relational database like
Access, you can reflect the real-world situation you've described...

If I understand, you can have between 1 and 5 days for which you need to
collect/record information. Instead of adding fields, then modifying every
query, form, report, macro and code snippet that refers to the fields, add a
single table to hold the "many" data. You've described what sounds like a
one-to-many relationship -- use the new table to show that.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"Phil" wrote in message
...
I dont know if I have phrased this correctly but there must be a more
elegant
solution than I am using now.

I have a database for managing education courses, I have tables for

Attendees
Events
registration
Payments
Events Location

the problem I have is that each course has either 1, 3 , 5 days I need to
collect attendance data and use the database for producing timetables.

How would you set up the database to input the dates for each course and
collect registration data.

At the moment my solution is to collect attendance data I have fields on
the
registration table for Day 1 , 2 3, 4 5, which link to a form and if it is
only a one day course I only complete that part of the form.

I Also input the dates into the table events location to generate
timetables. the Events table has start date and end date field s which i
independently complete. I know I am repeating data input and this is
where
error can occur.

I know this is not explaining my problem well, but any help would be
appreciated

thanks

Phil

Hi Jeff

thanks for replying, when I originally designed this last year I had no idea
what I was doing, I think I need to have a redesign and have planned time to
do this. One of my worarounds actually uses one to many relationship for the
dates so I can utilise that throughout the database to store the dates. here
is the difficult bit (for me) how do i do this with forms:

How do I show that the course has 1-5 days
How do I then input the data on the form.
How do I set the startdate,enddate

sorry about this, i dont think i am explaining myself well.

thanks

Phil



"Jeff Boyce" wrote:

Phil

Adding fields to add data is a very spreadsheetly thing to do (in a
spreadsheet, it may be the ONLY way). But in a relational database like
Access, you can reflect the real-world situation you've described...

If I understand, you can have between 1 and 5 days for which you need to
collect/record information. Instead of adding fields, then modifying every
query, form, report, macro and code snippet that refers to the fields, add a
single table to hold the "many" data. You've described what sounds like a
one-to-many relationship -- use the new table to show that.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"Phil" wrote in message
...
I dont know if I have phrased this correctly but there must be a more
elegant
solution than I am using now.

I have a database for managing education courses, I have tables for

Attendees
Events
registration
Payments
Events Location

the problem I have is that each course has either 1, 3 , 5 days I need to
collect attendance data and use the database for producing timetables.

How would you set up the database to input the dates for each course and
collect registration data.

At the moment my solution is to collect attendance data I have fields on
the
registration table for Day 1 , 2 3, 4 5, which link to a form and if it is
only a one day course I only complete that part of the form.

I Also input the dates into the table events location to generate
timetables. the Events table has start date and end date field s which i
independently complete. I know I am repeating data input and this is
where
error can occur.

I know this is not explaining my problem well, but any help would be
appreciated

thanks

Phil

On Nov 21, 12:51 pm, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:
If I understand, you can have between 1 and 5 days for which you need to
collect/record information. Instead of adding fields, then modifying every
query, form, report, macro and code snippet that refers to the fields, add a
single table to hold the "many" data. You've described what sounds like a
one-to-many relationship -- use the new table to show that.

Jeff,
I read the OP's spec as needing to go a bit further than this e.g. it's
OK to add day four into the database if it's one of those five-day
courses but not for a three-day course.

Here's something I did for an earlier thread:

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)
)
)
;

http://groups.google.com/group/micro...4430dc8b50ba8f

Jamie.

--

Hi Jamie

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.

I think to start with I will have to look at redesigning my tables and
forms. at the moment I collect the same data in three places and need to
change that, it looks like a big job though.

thanks for your help

Phil



"Jamie Collins" wrote:



On Nov 21, 12:51 pm, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:
If I understand, you can have between 1 and 5 days for which you need to
collect/record information. Instead of adding fields, then modifying every
query, form, report, macro and code snippet that refers to the fields, add a
single table to hold the "many" data. You've described what sounds like a
one-to-many relationship -- use the new table to show that.

Jeff,
I read the OP's spec as needing to go a bit further than this e.g. it's
OK to add day four into the database if it's one of those five-day
courses but not for a three-day course.

Here's something I did for an earlier thread:

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)
)
)
;

http://groups.google.com/group/micro...4430dc8b50ba8f

Jamie.

--

Phil wrote:

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/as...04831033.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

thanks for getting back to me, I have run the code to biuld the database and
this is exactly what I would like to implement in my database. I have used
code in the past however find it difficult to use so please bare with me.


To implement this in my database

your [Classes] table is where I presume I would put the number of days e.g.
1,3,5 and could call the table something like EventTypes which would be used
to limit the number of days in each course

The enrolment Table would be my already existing tbleventslocation which has
the dates information for the course

The students would come from my Events table which already exists

I dont know why you have the sequence table, would i need it, what does it do.

I have only ever used code in forms and cannot see how you implement this in
tables. I have looked at the individual tables and database and cannot find
any code in design view, i did find one validation rule.

what code do I need and where do I put it. I cannot see standalone code in
your post and would not know where to put in tables

I would love to be able to do this as I see it would do what I need, just
one other question will this still work in forms I presume it will as it
works on the underlying tables

sorry about this thicky response, but I appreciate all your help. of course
I understand if I am asking for to much, just let me know

thanks

Phil







"Jamie Collins" wrote:


Phil wrote:

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/as...04831033.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

As you've pointed out, the validation (no "day 4" in a "3-day" course) could
be handled at the table level with constraints added into the table
definition. This approach is consistent with our earlier "conversation"
about embedding as much business logic/validation into the table as
possible.

Aside from the issue of complexity (more difficult to achieve, since the
user interface does not facilitate this), I have a concern (just realizing
it) that embedding logic and business rules into the underlying data
structure forces anyone using the data to agree to the embedded rules, even
if they aren't aware that they are there! (again, with the lack of user
interface...)

I see an advantage using a more multi-tier approach, in which the data is
just data, the user-interface is only that, and the business rules occupy a
middle-ground? What are your thoughts?

Regards

Jeff Boyce
Microsoft Office/Access MVP
"Jamie Collins" wrote in message
oups.com...


On Nov 21, 12:51 pm, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:
If I understand, you can have between 1 and 5 days for which you need to
collect/record information. Instead of adding fields, then modifying
every
query, form, report, macro and code snippet that refers to the fields,
add a
single table to hold the "many" data. You've described what sounds like
a
one-to-many relationship -- use the new table to show that.

Jeff,
I read the OP's spec as needing to go a bit further than this e.g. it's
OK to add day four into the database if it's one of those five-day
courses but not for a three-day course.

Here's something I did for an earlier thread:

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)
)
)
;


http://groups.google.com/group/micro...4430dc8b50ba8f

Jamie.

--

Hi Jeff

thanks for this i appreciate your reply. From your suggestion, I take it
that validation would be better through the form than through the tables.
how would I go about this and ensure that each course had the correct number
of days at the moment I only have a text box which shows if it is 1,3 or 5
days

thanks

Phil

"Jeff Boyce" wrote:

Jamie

As you've pointed out, the validation (no "day 4" in a "3-day" course) could
be handled at the table level with constraints added into the table
definition. This approach is consistent with our earlier "conversation"
about embedding as much business logic/validation into the table as
possible.

Aside from the issue of complexity (more difficult to achieve, since the
user interface does not facilitate this), I have a concern (just realizing
it) that embedding logic and business rules into the underlying data
structure forces anyone using the data to agree to the embedded rules, even
if they aren't aware that they are there! (again, with the lack of user
interface...)

I see an advantage using a more multi-tier approach, in which the data is
just data, the user-interface is only that, and the business rules occupy a
middle-ground? What are your thoughts?

Regards

Jeff Boyce
Microsoft Office/Access MVP
"Jamie Collins" wrote in message
oups.com...


On Nov 21, 12:51 pm, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:
If I understand, you can have between 1 and 5 days for which you need to
collect/record information. Instead of adding fields, then modifying
every
query, form, report, macro and code snippet that refers to the fields,
add a
single table to hold the "many" data. You've described what sounds like
a
one-to-many relationship -- use the new table to show that.

Jeff,
I read the OP's spec as needing to go a bit further than this e.g. it's
OK to add day four into the database if it's one of those five-day
courses but not for a three-day course.

Here's something I did for an earlier thread:

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)
)
)
;


http://groups.google.com/group/micro...4430dc8b50ba8f

Jamie.

--

Phil wrote:
To implement this in my database

your [Classes] table is where I presume I would put the number of days e.g.
1,3,5 and could call the table something like EventTypes which would be used
to limit the number of days in each course

The enrolment Table would be my already existing tbleventslocation which has

Surely you'd want to ensure the number of days for the course
corresponds to the course's dates in tbleventslocation? Therefore, you
may want to put the 'number of days' column (with an appropriate name)
in the same table. The reason for doing this is the same as repeating
the Classes.seating_capacity column in the enrolment table in my
example and that is because the two flavours of Access Validation Rule
(disregarding CHECK constraints) are column-level and row-level. In
other words, in order to ensure the seat number is not higher than the
maximum possible seat number using a row-level Validation Rule, every
row must contain the value for the maximum seat number.

Consider this example (I hope you can follow the logic of the SQL code,
if not the complete syntax):

CREATE TABLE Happenings (
happening_ID CHAR(9) NOT NULL UNIQUE,
happening_start_date DATETIME NOT NULL,
CONSTRAINT happening_start_date__first_time_granule
CHECK(HOUR(happening_start_date) = 0
AND MINUTE(happening_start_date) = 0
AND SECOND(happening_start_date) = 0),
happening_end_date DATETIME NOT NULL,
CONSTRAINT happening_end_date__last_time_granule
CHECK(HOUR(happening_end_date) = 23
AND MINUTE(happening_end_date) = 59
AND SECOND(happening_end_date) = 59),
CONSTRAINT happening___date_order
CHECK(happening_start_date happening_end_date),
happening_duration_days INTEGER NOT NULL,
CONSTRAINT happening___duration_days__dates__interaction
CHECK(happening_duration_days = DATEDIFF('d', happening_start_date,
DATEADD('s', 1, happening_end_date))),
UNIQUE (happening_duration_days, happening_ID)
);

Once again the CHECK constraints can all be replaced by Validation
Rules, notably the one that checks that the duration in days
corresponds to the start and end dates.

The temporal nature of the data can significantly increase the
complexity of the design. For example, to check there are no
overlapping periods for courses for the same location you almost
certainly wouldn't be able to use a Validation Rule

I dont know why you have the sequence table, would i need it, what does it do.

An auxiliary Sequence table of integers is a standard SQL trick and has
a number of uses e.g. see:

ASP FAQ: Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/2516

In my example I am generating sequences of seat number between 1 and
the maximum possible seat number and a Sequence table is ideal for this
e.g. using the example, this:

SELECT C1.class_ID, C1.seating_capacity, S1.seq AS seat_number
FROM Classes AS C1, [Sequence] AS S1
WHERE S1.seq BETWEEN 1 AND C1.seating_capacity
ORDER BY C1.class_ID, S1.seq;

uses the Sequence table to generates all seat numbers for all courses
with exceeding the course maximum.

I have only ever used code in forms and cannot see how you implement this in
tables. I have looked at the individual tables and database and cannot find
any code in design view, i did find one validation rule.

what code do I need and where do I put it. I cannot see standalone code in
your post and would not know where to put in tables

You don't need code to reproduce my example.

There are more than one ways to skin a cat g. I prefer to create
tables, constraints, etc using SQL DDL code. Others prefer to use the
Access GUI tools which I personally find hard work: table design view
for tables, columns, Validation Rules, PRIMARY KEY, UNIQUE (Index No
Duplicates), etc; Relationships window to drag-drop create FOREIGN KEY
(enforce integrity = yes); etc.

It is sure easier for me to produce VBA code that will create the DB
rather than describe the steps required ('open this, click here, answer
'No' when prompted," etc) using the GUI (even if I could remember them
g).

Jamie.

--