By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

filtering multiple tables simultaneously

daniel aristidou
100+
P: 491
hi i was wondering if it is possible to filter multiple numbers of tables at the same time. the filter would be automatically applied ie. as in a query.

However i want only one list to appear when it is run.therefore not a exactly a multiple filter but a filter on one table where all the data is temporarily put when the filter is run. this filter would auto matically run whenever the databased is open presumably by using and autoexecute macro.

Every table has exactly the same feilds. and the feild to be filtered is (date finished). it would filter for records which are in multiple tables for once which had not been finished , by filtering for records with no data in the feild date finished. Thanks for any help in advance.
I use access 07
i am not extremely familar with vb and have only ever used it to create cascade update combos, but i should be fine providing instructions are clear.
Sep 13 '07 #1
Share this Question
Share on Google+
12 Replies


Scott Price
Expert 100+
P: 1,384
I hesitate to ask, but why do you have multiple tables with the exact same structure?

Have you read and understood the information in this tutorial? Database Normalisation and Table Structures

On to your question... You can accomplish something similar to this by using a Union query. However, if (as I suspect) you have a design issue, taking care of the design issue will greatly simplify the task of querying for what you want to see.

Regards,
Scott
Sep 15 '07 #2

daniel aristidou
100+
P: 491
I hesitate to ask, but why do you have multiple tables with the exact same structure?

Have you read and understood the information in this tutorial? Database Normalisation and Table Structures

On to your question... You can accomplish something similar to this by using a Union query. However, if (as I suspect) you have a design issue, taking care of the design issue will greatly simplify the task of querying for what you want to see.

Regards,
Scott
i have multiple tables with the same feilds but they have slight diff relation ships depending but all are based on the same feilds.
Thanks anyway ill look up union querys on microsft office thanks
Sep 15 '07 #3

Scott Price
Expert 100+
P: 1,384
i have multiple tables with the same feilds but they have slight diff relation ships depending but all are based on the same feilds.
Thanks anyway ill look up union querys on microsft office thanks
Daniel,

My reply was not intended to send you to another site! If you have questions about how to normalize your tables, feel free to ask them!

I think you will find that if you normalize the tables, you will not need to investigate union queries. A simple select query will do the job.

If you wish to give me an idea of the structure of your tables, I can try to help you with how to change them. If so, please post the table name, field names and data types for each table, and a brief sample of the data contained in each field...

Regards,
Scott
Sep 15 '07 #4

daniel aristidou
100+
P: 491
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.
Sep 16 '07 #5

Scott Price
Expert 100+
P: 1,384
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
Sep 17 '07 #6

daniel aristidou
100+
P: 491
Hi thanks, um ill try it out ,before i tried something similar but using diff code for the cmbos
ill post here if i experince any problems thanks!!!
Note should i not bind the combo boxes since i want them to enter data to the selected record ? .....How will it affect the subform if the combos are bound if the subform is updated acording to selected chapter.?
Sep 17 '07 #7

Scott Price
Expert 100+
P: 1,384
Hi thanks, um ill try it out ,before i tried something similar but using diff code for the cmbos
ill post here if i experince any problems thanks!!!
Note should i not bind the combo boxes since i want them to enter data to the selected record ? .....How will it affect the subform if the combos are bound if the subform is updated acording to selected chapter.?
For searching/applying filters you should use unbound combo boxes.

The updating should be done using other, bound, controls.

This separates the two functions (searching and updating) and lessens the code you have to write.

Regards,
Scott
Sep 17 '07 #8

daniel aristidou
100+
P: 491
For searching/applying filters you should use unbound combo boxes.

The updating should be done using other, bound, controls.

This separates the two functions (searching and updating) and lessens the code you have to write.

Regards,
Scott
Hi again just thought id ask should the feild called [subjectID] the Fk which is in format of number be indexed as :yes allow multiples?
Sep 18 '07 #9

Scott Price
Expert 100+
P: 1,384
Hi again just thought id ask should the feild called [subjectID] the Fk which is in format of number be indexed as :yes allow multiples?
You are correct; it should be indexed, and should also allow duplicates.

Regards,
Scott
Sep 18 '07 #10

daniel aristidou
100+
P: 491
You are correct; it should be indexed, and should also allow duplicates.

Regards,
Scott
Thanks im now in process of completing The form designs and i shall be doing the querys soon.
Sep 19 '07 #11

daniel aristidou
100+
P: 491
Thanks im now in process of completing The form designs and i shall be doing the querys soon.
Hi i have a combo box and an option botton, im trying to make it so that if the option button is checked a combo is bound but if not it is unbound
I tried a code like this
Expand|Select|Wrap|Line Numbers
  1. If istrue then
  2. [combo16].bound
  3. Else
  4. End if
But i thought i may be possible by making it allow data entry and edits
and denying edits.
Any ideas,?
Its nothing too major it is not neccesary so............ i just wana know if i can do it and with what code.
Thanks
Sep 20 '07 #12

Scott Price
Expert 100+
P: 1,384
Hi i have a combo box and an option botton, im trying to make it so that if the option button is checked a combo is bound but if not it is unbound
I tried a code like this
Expand|Select|Wrap|Line Numbers
  1. If istrue then
  2. [combo16].bound
  3. Else
  4. End if
But i thought i may be possible by making it allow data entry and edits
and denying edits.
Any ideas,?
Its nothing too major it is not neccesary so............ i just wana know if i can do it and with what code.
Thanks
Yep, it's possible...

Use the .Controlsource setting... For example.

Expand|Select|Wrap|Line Numbers
  1. Me!combo16.Controlsource = ""
Regards,
Scott
Sep 20 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.