Jamie

Thanks for your again detailed reply. this is making my head hurt, I am
impressed. Your brain must obviously work in a very abstract way I could not
work in that environment I need to see things graphically. I feel like I am
running to catch a bus and falling behind.

to recap my tables work like this: -

Attendees

captures all demographic data for participants (firstname surname etc.)

Events

Store information on the courses such as startdate, enddate, type of course

registration

this is where partcipants get booked on to courses

Events Location

this stores individual dates of the courses e.g. day 1 day 2 etc, plus
location, themes etc.


From what you are saying I need to add Number of days field (1,3,5) and a
day field 1,2,3,4,5 etc and I can add this to the existing tbleventslocation,
will this column autocomplete from what would be your classes table or do I
type it each time. How do I then set up the validation rule. Sorry I had
difficulty following SQL

Thanks for explaining the sequence table, I presume I should add extra
numbers so that if I ever ran a 6 day course in two years time and wondered
why it didnt work.

How do I change the tables that I already have to replcate something similar
to your example. is it possible dont foget i am constrained by the GUI.

thankyou for your time and effort it is really appreciated,

thanks

Phil

"Jamie Collins" wrote:


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.

--