I am doing a project for my school (no it is NOT going to be marked, or submitted to anyone or sold, just used by the school). It is used to determine which pupils have been given which school textbooks. To do this i have created a database that will allow the teachers to enter the data into the system.

The teachers will open the database, and a subject list will be displayed. they will select which subject they want, and then they select which year group they wish to assign books to.

The problem is there are 15 subjects, and 7 year groups for each subject, so thats over 100 tables, and 15 different forms for each of the subjects that need to be created. The 100 tables are not a problem, because they are small and easy to copy. whereas the forms are not. they will tke up alot of space, and there is a way around it.

I want the database to save which button the user has pressed on the subject form (eg, they pressed maths, so it would save MAT) and then save the button they pressed on the second form (eg they pressed year 7, so it would sve 7) and then for the values that have been saved, the database will call them, and open the appropriate table.

Im pretty sure this is possible, but i dont know how :S would someone be able to help me create one, once i get one done, it will be a case of copy and pasting, and editing small parts of it for the rest.

I don't understand why you need so many tables or so many forms. Good normalization will cut down on the number of tables; and dropdowns and resetting control sources can negate the need for so many forms.

Can you explain your rationale? Thanks.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

well, if i have it all in one table, the user will still ave to type/select their subject and year from a drop down box. with the system i want, they just click on the subject button, and the year button, and the table opens up.

atm i have a drop down menu on the form with about 100 different options...and the user can select from that, but i need to implement more than one idea, and compare which one is/was easier to use and collect data from.

No, I didn't say have it all in one table; but you don't need 100 either. List the fields you will need and we here can let you know what kind of table set-up you need. It's probably a lot simplier than you realize.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

You need a few tables, not 1 or 100.

Then your forms and look ups are easy.

Can you tell me more about the structure of this monster?



mmcdonal

ok i need thiese subjects: Maths, science, english, ICT, history, RE, geography, performing arts, pe business studies, technology, sociology, pshychology, economics, film studies, M.F.L.

and for each subject, i need year 7 through to year 13.

and the table headings atm are: Book number (auto number), ISBN number, Name of book, Price, Book Code (teachers personal code to see which pupil has which book)

if you would like the database i have created so far, just to have a gander at, i will upload and post a link to it.

tblStudent
   PK AutoNumber
   txtFirstName
   txtLastName
   txtOtherInfo?

tblSubject
   PK AutoNumber
   txtSubjectName
   txtOtherInfo?

tblFaculty
   PK AutoNumber
   txtLastName
   txtFirstName
   txtOtherInfo?

tblBook
   PK AutoNumber
   txtBookName
   txtISBN
   curPrice (this is problematic since it will change, but hopefully only with new editions)
   txtOtherInfo?

tblAssignment
   PK AutoNumber
   numStudentID (LU to tblStudent)
   numSubjectID (LU to tblSubject)
   numFacultyID (LU to tblFaculty)
   numBookID (LU to tblBook)
   LUYear (internal look up to year, unless this is a property of the books, then it would go in tblBook)

This will allow you to sort on each of these tables.
You can create autoforms on each of these tables.
You can also create a form that will allow faculty to see only their students, etc.

You will need perhaps 6 or so forms, (frmStudent, frmSubject, frmFaculty, frmBook, frmAssignment, and maybe some special, report or switchboard)

You will need a few generic queries and reports, and then you can pass parameters from forms to the reports/queries with combo boxes and buttons on your reports form, and generate the info you need that way, rather than creating a query/report for each report.

This is a small database and does not require a lot of tables, forms, queries or reports, especially since your OLAP needs are very main stream.


mmcdonal

Sorry, I didn't read too carefully about assigning books to subjects and years.

I was thinking assigning books to students. How did that happen?

Do this:

tblSubject
   PK AutoNumber
   txtSubjectName
   numYear
   txtOtherInfo?

tblFaculty
   PK AutoNumber
   txtLastName
   txtFirstName
   txtOtherInfo?

tblBook
   PK AutoNumber
   txtBookName
   txtISBN
   curPrice (this is problematic since it will change, but hopefully only with new editions)
   txtOtherInfo?

tblAssignment
   PK AutoNumber
   numSubjectID (LU to tblSubject)
   numFacultyID (LU to tblFaculty)
   numBookID (LU to tblBook)

Please note the year number in the subjects table. You will have one table with more than 100 entries, but this is better than 100 tables.

Create an autoform on tblSubject and that will show you subject, year and allow you to assign faculty and books to that subject.

Then create your main form and put a combo box on it using the wizard and populate the combo box from the subjects table, being sure to include the year in the look up. When they look up in this combo box they will see "Math, 7" "Math, 8" etc.

Then using the wizard again, put a button on the form and ask it to open a form displaying filtered records. Make the Combo box <-> SubjectID.

Then when the faculty select the subject/year in the one combo box and click the button, it will open the form to that subject and year only.

You can duplicate this combo box/button on the subject form so that they can look up another subject/year without having to close the form.

Here is the code I used:

Private Sub btnSubject_Click()
On Error GoTo Err_btnSubject_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSubject"

    stLinkCriteria = "[SubjectID]=" & Me![Combo1]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnSubject_Click:
    Exit Sub

Err_btnSubject_Click:
    MsgBox Err.Description
    Resume Exit_btnSubject_Click

End Sub

You could also add "DoCmd.Close" just before the DoCmd.OpenForm line so that the first form closes before the subject form opens. Less clutter that way.

Please populate all the look up tables before using the combo/box button. Make a main switchbaord with subject, book and faculty data entry forms.

Sorry about the first post.


mmcdonal

hehe thats ok, i was reading the first post and getting a bit scared lol.

im really sorry, but i know precisely nothing about vb. can u tell me where that needs to go, and exactly how it will work?

oh btw, sometimes teachers may enter mmore than one book per year per subject...can this still be done?

You have to design databases for n entries. This will allow more than one teacher to enter more than one book per subject per year.

The code is written by the wizard when you create the look ups and add the button.



mmcdonal