Sub Form & Query

[New to Access100]

hello

I have a practical exam in college that I am preparing for. Some of it requires the student to have some Database knowledge . so studying hard at the moment

Unfortunately the official book for the course does not provide guidance on how to actually create sub forms or cross tab/complex queries. strangely enough, it has examples of what they look like but not actually how to create them

i am trying to solve a particular problem on a question paper. it's asking to create a basic booking system, the scenario given is a booking system for ski lessons. you are meant to create a form in order to select numerous items. such as selecting the instructor, customer name, time of the lesson. when you select these it should automatically display other information such as customers age, price of the lesson and the skill level

so far i have imported the necessary text files into access, created one (questioned asked to do that). i made the relationship between the tables

but now i am not sure if i am suppose to create a simple query or a crosstab query
i do know that i am suppose to make a sub form. i know because i looked at the screenshots for the answers. i have no clue on how to do this

please if anyone can assist me with this, i would be very grateful. i have attached the mdb file, what i have done so far which is little, because i do not know how to progress . i have attached the question paper, it is Question 6

thanking you in advance

[June7]

Question 5 is about the booking database, question 6 is a JavaScript exercise.

The relationships are not correctly set. They should not be linking on the BookingID. InstructorID should link to InstructorID, CustomerID to CustomerID. The ID fields in Booking must be long integer number type, not text. Time should be Date/Time type.

CROSSTAB queries are not useful for data entry, only for manipulating data for output.


Date is a reserved word and should not use reserved words as names for anything. Also, advise not to use spaces or punctuation/special characters (underscore only exception) in naming convention. Use CamelCase and/or underscore, like: InstructorLevel or Instructor_Level.

[New to Access100]

i am very sorry for getting the question number wrong. i guess i am stressed

thank you for the quick information you provided. i appreciate it

any chance of advice about the sub form?

[June7]

Topics to research to accomplish the assignment requirements:

"IST would like a ski lesson booking form to create and view bookings."
Many to many relationship forms
Options for data entry forms:
1. one form bound to Booking table with comboboxes to select customer and instructor and time
2. main form bound to Customers and subform bound to Booking with comboboxes to select instructor and time
3. main form bound to Instructors and subform bound to Booking with comboboxes to select customer and time

"To make a ski lesson booking, IST want a user to be able to select the following from a list for the booking: customer, instructor, date, time"
This implies use of option 1 form with combobox for instructors and combobox for customers and combobox for time and textbox for date (because choosing date from a list makes no sense) but this makes it difficult to enter new customer record. Option 2 form arrangement would make it easier to enter a new customer record.

"The list of instructors available for selection should be restricted to those that match the level of the customer."
"Make sure that the instructor that can be selected can only be an instructor appropriate for that customer’s skiing level and no other level"
This would involve cascading (dependent) comboboxes

"The form should also automatically display the: customer level, customer age, instructor level, price of the lesson"
"Make the customer’s level and age appear automatically when the customer is selected."
Multi-column combobox (expression in textbox can reference combobox column in order to display related info)
Alternatively, using option 1 form, the form RecordSource would include all 3 tables. Bind textboxes to all fields but fields bound to Customers and Instructors fields must be set as Locked Yes and TabStop No to prevent edits.
Option 2 form design would have subform with a RecordSource that includes Booking and Instructor tables. Bind textboxes to all fields but fields bound to Instructors fields must be set as Locked Yes and TabStop No to prevent edits.

"IST want users to be able to select the times from the following list:"
Combobox RowSource based on ValueList or build a table to use as the combobox RowSource