If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#9
|
|||
|
|||
Use a variable number of fields
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. -- |
Thread Tools | |
Display Modes | |
|
|