Quote:
Originally Posted by daniel aristidou
right .
The databse im making keeps track of all my work that i file eg notes homework
I tryed to create a master table holding all the subjects. and then the tables chapters, homework, notes, tests were linked to this. however when i created combo boxes to select which subjet and chapter to select. i experienced such problems with cascade updating. The updating combo would not refresh after the master combo was changed. nobody could answer my problem
Thus i created A table for each subject called chapters table has the feilds:
[Chapter] -Txt
[Date Started] -Date/time
[Date finished] -Date/time
[Id] -Autonumber
Then linked to this using chapter, 1 to many, is Notes with the feilds;
[chapter] - txt
[note name] - txt
[date created] -date/time
[page in folder] -number
[file]-attachment
[Id] -Autonumber
Then linked to the chapters table again using the feild chapter,1 to many, is homework with the feilds;
[Chapter] -txt
[homework] -txt
[date issued]-date/time
[date finished]-date/time
[date started]-date/time
[page in folder] -number
[Id] -Autonumber
[Mark recieved]-number
I hve the above tables all named diff acording to the subject. my relations fill the whole screen.
I also have the table test which is not linked to anything.
[Test name]-text
[test date] -date and time
[time given]-date/time
[test on]-memo
[Date returned]-date/time
[mark received]-number
[position in class]-number
There are also many copies for this ; i have all these tables for the folowing subjects: English,Maths,Chemistry,Computing,Physics,
Literature,Greek,Economics,Music.
Thanks .ps when i said id go to microsoft website i did so only because the rules of the forum states u should only ask questions u cant answers for in book or in the help for the program. Anyway i experience problems with my union query already.it will only alow me the join two tables if i join any more a error apear saying it could not find the object ".
This error only appers after a third able is join in the query but the error remains even after the 3rd table is removed. Any ideas?
Thanks in ADvance for your time and effort.
Well, a couple of thoughts based on the information you've given me.
I would suggest again making the Subject table, then linking it to one Chapters table, linked in turn to one Notes table, one Homework table linked to the Subject table as well as linking the Test table to the Subject table.
The linking fields should be the autonumber fields, which should properly be named ChapterID, SubjectID, NoteID, etc... This avoids confusion when using them as foreign key fields in their respective links. So you will end up with more like this:
tblSubjects
SubjectID AutoNumber PK
Subject Text
tblChapters
ChapterID AutoNumber PK
SubjectID Number FK
Chapter Text
DateStarted Date/Time
DateFinished Date/Time
tblNotes
NoteID AutoNumber PK
ChapterID Number FK
NoteName Text
DateCreated Date/Time
FolderPage Number
File Attachment
tblHomework
HomeworkID AutoNumber PK
SubjectID Number FK
DateIssued Date/Time
DateStarted Date/Time
DateFinished Date/Time
FolderPage Number
MarkReceived Number
tblTest
TestID AutoNumber PK
SubjectID Number FK
TestName Text
TestDate Date/Time
TestTime Date/Time
DateReturned Date/Time
MarkReceived Number
ClassPosition Number
Now instead of 36 or more tables, you have 5.
As far as the combo box updating, with the structure I've posted above, there shouldn't be much trouble. I'm assuming that you are referring to a main combo box choosing a Subject, which cascades to a second combo box that allows you to choose Chapters associated with that subject?
Working from that assumption:
cboSubject Unbound
RowSource: SELECT SubjectID, Subject FROM tblSubjects
BoundColumn: 1
NumberofColumns: 2
ColumnWidth: 0", 1.5"
cboChapter Unbound
RowSource: SELECT ChapterID, Chapter FROM tblChapters WHERE tblChapters.SubjectID = Me!cboSubject
BoundColumn: 1
NumberOfColumns: 2
ColumnWidth: 0", 1.5"
In the AfterUpdate event of cboSubjects:
Me!cboChapter.Requery
Me!cboChapter.SetFocus
Now then, you'll likely have a subform in continuous form view to show the results of your two choices. Base this subform on a query, called somthing like sfqryNotes, with SQL similar to this: SELECT tblNotes.NoteID, tblNotes.NoteName, tblNotes.DateCreated, tblNotes.FolderPage, tblNotes.File FROM tblNotes WHERE tblNotes.ChapterID = Forms!frmMain!cboChapter.
In the AfterUpdate event of your cboChapter, you'll have:
Forms!frmMain!sfrmNotes.Form.Requery
These are suggestions off the top of my head, so there may be a few hiccups in getting it to work smoothly, but this is what I would strongly suggest you doing.
If these suggestions are duplicating what you said you have already tried without success, I apologize. However, I don't think it will be!
Let me know what you think about these suggestions before we get into debugging the Union query.
Regards,
Scott
