Link to home
Start Free TrialLog in
Avatar of Andy Brown
Andy BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Multiple queries for a form

Hi - I am creating an Access 2016 database and need some advice/help on structuring queries efficiently.

I have a single form  that pulls all of its data (RecordSource) from a query called `qryUD_tblMain`.  However, this query pulls data through another query called `qryUD_tblMain_Master_frmMOS`, which is created/driven by what each user is allowed to see (this can get a little complex, but everything is indexed correctly and seems to work well).

1 - qryUD_tblMain_Master_frmMOS
2 - qryUD_tblMain

The users also need to create their own filters, which are then stored for future use.  To complicate matters further, they may need to include data from related tables, which is where it starts to get tricky.  In the past, I have simply modified `qryUD_tblMain` with the query strings, but this can get extremely complex and slow.  

So I was wondering about three possible options.

Option 1 - Before the records are pulled into the form, use an append query to update a local/linked table with the record ID numbers (of records that meet the query criteria), and then simply modify `qryUD_tblMain` to pull in all of the records with a link to this table.  The only downside I can see, is the transferring of a few thousand IDs to a local table (not too worried about that), and the fact that the data isn't in real-time, but again, I can get around that.

Option 2 - Create multiple queries that sit between the two main queries mentioned above, and feed `qryUD_tblMain`.  This would be real-time, easy to create/manage, but I fear slow as you are using multiple queries and some of the used fields may not be indexed.

Option 3 - Go the old way and just modify `qryUD_tblMain` as and when required.  Again, some of the fields may not be indexed and it just feels clumsy.

I think I am leaning towards option 1, but could really do with a good brain (or two) to help me figure out the best solution.

Thanks everyone.
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<< To complicate matters further, they may need to include data from related tables, which is where it starts to get tricky. >>

 Not sure I understand what the problem is....the form sees what it sees from the main query and the users can filter that data.    Just let them apply a filter (use the forms filter by) as needed.  

Jim.
Avatar of Andy Brown

ASKER

Hi Jim - Hope all is well,

There are a total of four tables.  The main query `qryUD_tblMain` acts as the source for the form (nice and easy so far).  However, the other three tables, each have their own subform.  Within these subforms, users may pick one (say Info tbl_InformationRequests / frmMOS_Sub_InfoRequests), and want to see all of the records (in the main screen), where the users have been sent two different types of emails.  They may then want to break it down even further to say I only want to see the records based in London etc. etc. etc..  In short it gets quite long-winded.

So finding a fast way to pull and present all of the records that meet this criteria is key.  The old method of simply having a complex query can become very slow.  My default, simple test is to hold down the page-down key and see how it's performing.
Sorry, but I'm still not seeing/understanding your problem.

 Typically you would use the forms query to filter the rows that a user should not be able to see.  You'd hide controls for fields the user should not be able to see.     Then you allow the user to filter what they see using the forms filter property, or you requery the form and make the query point to controls on the form for the additional filtering

That keeps all the queries static and efficient.

Jim.
So, would several filters (and they really can get complex), be fast enough?  Keeping in mind that the users will want to see all of the records in `qryUD_tblMain_frmMOS` that have been sent an email called, 'Info request', have been called several times (based on another sub-table) and have a tick in a box, which again may well be on another sub-table.

Sorry to keep on :O)
I'm guessing that what I'm asking for in a nutshell is this:

If you guys had a 50k record database with; 1 main table, 3 related sub-tables, a main contact form with the three subforms also visible and you needed to give the user the ability to filter on several bits of information within each subform, so that they could simply see the Main records that meet their search criteria, which way would you go?

I am slowly leaning more to the filter option as it seems to be a nice, clean way of doing things.
Thanks as always.
In order to filter by the subforms, the main query needs to join to those tables.  Does it make sense to join to all of them?

When a query includes multiple tables, there must be some relationship between them or you end up with a Cartesian product.  For example, you have three tables - tblStudents, tblClasses, tblPets.  tblClasses and tblPets both contain StudentID and are related to tblStudents but just because they are both related logically to tblStudents doesn't mean that they are related to each other.  If you create a query that contains all three of these tables, you get nonsense results.

Sam, math, dog
Sam, math, horse
Sam, chemistry, dog
Sam, chemistry, horse
Suzie, history, cat
Suzie, history, fish
Suzie, math, cat
Suzie, math, horse

Are you really looking for students who both take math and have horses?

You probably want the main query to join to all the other tables but to not select any columns from them and to select distinct so you don't double up the main table records.  Then I would use combos (single select) or listboxes (multi-select) for selections on the sub tables.  

You can either build the Where clause on the fly or if it isn't too complex, build it so all the criteria is optional.

Where (fldA = forms!myform!fldA OR forms!myform!fldA Is Null) AND (fldB = forms!myform!fldB OR forms!myform!fldB IsNull) AND .....
Hi Pat - thanks for coming back to me.

As an example:

The "qryUD_tblMain" contains 5000 contacts and is currently showing them on the main contact form.  Within the contact form, the user needs to be able to show all of the contacts (from "qryUD_tblMain"), who have ticked "Yes" on the "Requires_Help" field, in one of the  subforms/tables.

On my filter test, I have a syntax similar to this:

me.form.filter = "RecordID In (SELECT [tblMain_UserInfo].[InfoRecordID] FROM [tblMain_UserInfo] WHERE [Requires_Help] = True;)

Based on the users requirements, this filter string could get very long, and cross over all four tables, but will only ever display records from `qryUD_tblMain` that meet the user criteria/filter.

Hope that makes a bit more sense.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you both so much for your help on this.

I am going to start off using "In (SELECT...." queries on the main table and see how complex I can make the queries before things start to noticeably slow down.  After that (and assuming I need to), I will add a function for complex queries where it adds all of the RecordIDs (for records that exist in the query string) to another table (say tblTemp_RecordID), and will simply put a link/relationship between that table and the main one.

I'm hoping that you think that will be an okay solution.

Thanks again for all of your input/guidance on this, it's much appreciated.
I'll say it differently.  Access does not always optimize subqueries well and you will probably find that joins provide better performance.  Plus building the SQL string on the fly will be more complex.  There are cases where subqueries are the only option.  This is not one.  In addition to poor optimization, the QBE does not have a good way to visually display the sub queries so again, joins are easier to visualize for people reading the query later.  KISS is usually the best approach.  If a left join solves the problem, why complicate the query?
Hi Pat,

I think I'm beginning to understand where you are coming from, but need to have another play to be sure.

Thank you so much for putting up with me :O)
<<I think I'm beginning to understand where you are coming from, but need to have another play to be sure.>>

 There's a big difference in terms of performance of a sub query vs using nested queries.  Your IN (Select would be a subquery.

 As Pat pointed out, those are not optimized at all.  

 Nested queries (where a query is used as a "table") can be optimized because it's nothing more than joins.

Jim.
Thanks Jim - I'll report back when I've had a play tomorrow.