473,320 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

filtering multiple tables simultaneously

daniel aristidou
491 256MB
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
12 4079
Scott Price
1,384 Expert 1GB
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
491 256MB
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
1,384 Expert 1GB
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
491 256MB
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
1,384 Expert 1GB
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
491 256MB
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
1,384 Expert 1GB
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
491 256MB
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
1,384 Expert 1GB
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
491 256MB
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
491 256MB
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
1,384 Expert 1GB
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

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

Similar topics

2
by: scott | last post by:
Hi, I'm having some trouble with something that should be relatively easy. I want to update multiple rows in one of my database tables simultaneously. In my table I have these values: ...
17
by: Steve Jorgensen | last post by:
Terminology question: Is there a term for a set of records related directly or indirectly by key value in several tables? For example, a single invoice record and its line item records -or- a...
3
by: Alex Ayzin | last post by:
Hi, I have a problem that might be easy to solve(possibly, I've just overlooked an easy solution). Here we go: I have a dataset with 2 datatables in it. Now, I need to do the following: if...
3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: Elias Farah | last post by:
Hello All, I hope someone can give me (and other keen access enthusiasts) some helpful information to explain how to most efficiently filter Queries & subqueies. Consider this common simple...
2
by: Sean | last post by:
Greetings all, I am attempting to make a form that will filter through several tables that (I believe) have refretial integrity. I am pulling data from several tables into the form and i would...
3
by: Jorge | last post by:
We have an application with Access 2000 and normally there are three designers working simultaneously. But forms or report modifications requires exclusive use. That force to all others users must...
2
by: Gummy | last post by:
Hello All, I have a webpage that has two dropdown listboxes. Based on what is selected in these dropdown listboxes, it filters a DataGrid . That works fine. In the DataGrid , when I go to edit...
3
by: Harry Haller | last post by:
Hello, I want to implement a generic list which will be used to display 7 columns in a GridView. One should be able to sort, filter and page each of the 7 columns. Ideally the filter should be...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.