Expert advice needed

  • Thread starter Thread starter Mark Andrews
  • Start date Start date
M

Mark Andrews

Hi, I'm designing an application that lets the user use a variety of forms
to basically build queries.
The users will be using the Access runtime and will not have any knowledge
of sql or Access.

Example: User uses my forms to build a query to return
Show contacts who have a status of 'Active'
and who have any donation date greater than 1/1/2009

For the user I call it:
- a filter is the entire query
- a criteria set is a set of criteria (these are considered all AND)
- you can have one or multiple criteria sets (these are all OR)
- you can also have Show/No Show criteria sets (AND NOT)

All criteria return ContactsIDs and the end result is used to filter down
the list of contacts for viewing/reporting

My question is on the approach.
- table design to store everything no problem
- forms to allow users to pick various type of fields
(example: text lets you pick choices from a multi-select list, number lets
you choose <>= betweeen etc...) no problem

At some point the filter (filtersets and filter criteria records) need to be
transformed into a query so I can use it to join to tblContact to return a
subset of contacts.

Note: One concept that complicates matters:
There are three main types of filter criteria (contact related, donation
related, pledge related).
- Contact related work off a query for contact data or summary data for each
contact
- Donation related work of the related donations records and then group by
contactid to get the list of contacts
- Pledge related work similar to donation except off pledges

So in my example:
Show contacts who have a status of 'Active'
and who have any donation date greater than 1/1/2009
the first line is a contact filter criteria and the second line is a
donation filter criteria
This is a ONE FilterSET example with 2 FilterCriteria lines (pretty simple
example)



My current design/idea is to do the following:
- build actual queries (1 to 3 queries for the filter criteria) and one to
join these queries if needed (for each FilterSET) (building using
CreateQueryDef etc...)
- Then if you have multiple Filtersets another query is used to UNION the
filtersets together (one for Show and one for No Show)
- If you have both Show and NO Show criteria another query to join the Show
and No Show queries appropriately to shwo the correct records

So for a large Filter (you might end up having 20 queries created all via
code).

Is this the best approach? What other ideas would work. Keep in mind a
"Filter"'s end goal is just to be a list of ContactIDs so that it can be
joined into the mix to help with reporting or viewing sub-sets of data.

Thanks in advance,
I hope this made sense and is not too difficult to follow or get any
replies,
Mark
 
Gina,

This is a generic little form that helps build a query. What I am doing is
much more complex and professional.
However with complexity comes complications and I was asking for help with
these complications and possible approaches to use. I might not of
explained it well enough?

I understand it's probably a question that is too tough for any MVP to take
a crack at, but if anyone wants a break from answering questions about
cascading combo boxes or how to refer to controls on a subform etc..., then
this is your chance.

Care to take another shot?

Mark
 
Mark -

Can you build a query that joins all tables correctly (this would be a view
in higher-end databases), then always apply your criteria to this one query
(view)? That will make it much easier to code.
 
In sql server I would probably use a view or stored proc that does much more
(many sub queuries etc...).
However in Access struggling a bit on sub queries and wondering if
performance is better when you use numerous regular queries???

Currently for ONE filter I have the following:
qryFilter (high level query) (intelligently joins the next two queries)
qryFilterSHOW (all the SHOW sets of criteria) (union of all SHOW sets)
achieves set1 OR set2 OR ...
qryFilterNOSHOW (all the NO SHOW sets) (union of all NO SHOW sets) achieves
set1 OR set2 OR ...

For every set of criteria (most times this number will be 1 to 3):
qrySetXXX (intelligently joins 1 to 3 queries)
- qrySetXXXContact (if any contact related criteria apply)
- qrySetXXXDonation (if any donation related criteria apply)
- qrySetXXXPledge (if any pledge related criteria apply)

So right now I have code that deletes all queries that start with "qrySet"
and then creates the set queries needed and then updates qryFilter,
qryFilterNOShow and qryFilterSHOW. I don't like all that deleting of
queries and creating new queries (wondering how the front-end will grow).

qryFilter is then uses to filter the contact list or report.

I would be happy to give anyone the sql in the numerous queries for one
filter to see if you can combine it all together into one query! maybe an
easy one with one SHOW set and one NOSHOW set.

Suggestions welcome,
Mark
 
Mark Andrews said:
At some point the filter (filtersets and filter criteria records) need to be
transformed into a query so I can use it to join to tblContact to return a
subset of contacts.
Click to expand...

Why? Why not just a filter or a Where clause when opening a form.
Note: One concept that complicates matters:
There are three main types of filter criteria (contact related, donation
related, pledge related).
Click to expand...

Then maybe those should be three separate search forms.
My current design/idea is to do the following:
- build actual queries (1 to 3 queries for the filter criteria) and one to
join these queries if needed (for each FilterSET) (building using
CreateQueryDef etc...)
- Then if you have multiple Filtersets another query is used to UNION the
filtersets together (one for Show and one for No Show)
Click to expand...

Not sure what you mean by Show and No Show.
- If you have both Show and NO Show criteria another query to join the Show
and No Show queries appropriately to shwo the correct records

So for a large Filter (you might end up having 20 queries created all via
code).
Click to expand...

Sounds way too complex. Start off much simpler and see if the user
has any objections to that.

Hmm, if you have to digitally sign your app will creating new
querydefs invalidate the digital signature? Not sure about that.

No
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
Tony,
Thanks for the advice. It's difficult to explain why I am going down this
route, however I feel it is the right approach. Think of these are saved
filters that the user makes that they reuse. They all end up resulting in a
filter of ContactID IN (2,4,6,7 ...)
Mark
 
Bruce,

I have the system built, my main question is should it go off an create
multiple queries or should it go off and create one query.
I would prefer the one query approach but not sure if I can put all the
subquery syntax together or if this way runs as fast in Access.

Currently for ONE filter I have the following:
qryFilter (high level query) (intelligently joins the next two queries)
qryFilterSHOW (all the SHOW sets of criteria) (union of all SHOW sets)
achieves set1 OR set2 OR ...
qryFilterNOSHOW (all the NO SHOW sets) (union of all NO SHOW sets) achieves
set1 OR set2 OR ...

For every set of criteria (most times this number will be 1 to 5):
qrySetXXX (intelligently joins 1 to 3 queries)
- qrySetXXXContact (if any contact related criteria apply)
- qrySetXXXDonation (if any donation related criteria apply)
- qrySetXXXPledge (if any pledge related criteria apply)

So right now I have code that deletes all queries that start with "qrySet"
and then creates the set queries needed and then updates qryFilter,
qryFilterNOShow and qryFilterSHOW. I don't like all that deleting of
queries and creating new queries (wondering how the front-end will grow).

qryFilter is then used to filter the contact list or report.

Mark
 
Bruce,

Thanks I think I just changed it around to build one sql statement (with
tons of subqueries). By building it the way that creates individual queries
it actually helped make it easy to build one sql statement.

I think I should be able to either use this sql to do a variety of things.
However it is very difficult to read.

to explain my logic:

one set of criteria might have
1. ContactStatus = 'active' and ContactType = "xxx' (one query used to
restrict contacts)
2. Any donation date > 1/1/2009 (which is select contactid from tblDonation
joined with tblcontact where donationdate > 1/1/2009 groupby contactid)
3. Any pledge date (similar to donation)

So within each of the three types I could have multiple criteria requiring
an 'AND' and to 'AND' the three types I need to JOIN them together.

Now if I have a second set of criteria (set1 OR set2) I need to do a UNION
operation (to show set1 OR set2)

Now if I have sets of SHOW Criteria and sets of NOSHOW criteria then I need
to do a Left join with NOSHOW.ContactID is NULL to return result in SHOW and
NOT IN NOSHOW.

By "intelligently join" I mean when building this massive string I build the
query in the best possible way.
Example: If the string is really simple "show contacts with contactstatus =
"active" it ends up being a very simple query, however it there are 20 lines
of criteria the query is much bigger.

By having the 1,2 and 3 above is what made this complex to implement. I
started by just having 1 and it was just one query.

However after now thinking about it a bit more I might be able to change my
design to be simplier by combining 1,2 and 3 above into one query. I think
I might of gone off and built something a bit too complex (but it works), I
hate when I do that! I have had the flu for the past few days (so I blame
the sickness).

Hope that made sense,
If you can think of anything I could change let me know,
Mark


BruceM via AccessMonster.com said:
I would go with creating one query. I would create it on the fly rather
than
saving, then deleting named queries. I doubt very much that there is a
significant performance difference runnign the queries one way or the
other,
but even if using named queries is marginally more efficient I suspect
deleting queries involves more overhead than would be saved.

I don't think there is a way to be specificabout one approach over the
other
based on a general description. I don't understand the use of Union
queries
in this instance. Rather than derive one set of data for SHOW and another
for NOSHOW, then Union them together, why not include both criteria in one
query? What do you mean by "intelligently join"? Can you provide an
example?


BTW, the term "Filter" is sometimes used rather loosely, but I am taking
it
to mean that you load a recordset, then restrict it based on certain
criteria.
A query with criteria (parameters), on the other hand, limits the
recordset
that is loaded. It sounds as if you are limiting the recordset that is
loaded, which tends to be a more efficient approach, particularly if an
unrestricted initial recordset is large and contains records you are
unlikely
to need.

Mark said:
Bruce,

I have the system built, my main question is should it go off an create
multiple queries or should it go off and create one query.
I would prefer the one query approach but not sure if I can put all the
subquery syntax together or if this way runs as fast in Access.

Currently for ONE filter I have the following:
qryFilter (high level query) (intelligently joins the next two queries)
qryFilterSHOW (all the SHOW sets of criteria) (union of all SHOW sets)
achieves set1 OR set2 OR ...
qryFilterNOSHOW (all the NO SHOW sets) (union of all NO SHOW sets)
achieves
set1 OR set2 OR ...

For every set of criteria (most times this number will be 1 to 5):
qrySetXXX (intelligently joins 1 to 3 queries)
- qrySetXXXContact (if any contact related criteria apply)
- qrySetXXXDonation (if any donation related criteria apply)
- qrySetXXXPledge (if any pledge related criteria apply)

So right now I have code that deletes all queries that start with "qrySet"
and then creates the set queries needed and then updates qryFilter,
qryFilterNOShow and qryFilterSHOW. I don't like all that deleting of
queries and creating new queries (wondering how the front-end will grow).

qryFilter is then used to filter the contact list or report.

Mark
Here is an example of how to build a filter string from a multi-select
list
Click to expand...
[quoted text clipped - 101 lines]
replies,
Mark
Click to expand...
Click to expand...
Click to expand...
 
Ok here's my last question:

The table structures are as follow:

tblContact (primary key ContactID)
tblDonation (donations made by each contact, foreign key ContactID)
tblPledge (pledges made by each contact, foreign key ContactID)

I always want to return a list of contactIDs
Option A: Use a base query of tblContact joined with tblDonation and also
joined with tblPledge
and then add in the entire where clause and then group By ContactID. The
where clause has a mixture of ANDs, ORs and NOTs.

Option B: The "I don't know what I was thinking route but maybe it does run
faster???"
Construct multiple queries if needed (Contact criteria using tblContact),
(Donation Criteria using tblDonation),
(pledge Criteria usign table pledge) then join all of those results together
(to do the equivilent of AND from above). To do the equivalent of OR (from
above) you union sets of queries together and to do the equivalent of NOT
(from above) you left join/is not null two queries.

Example: Show Contacts with status = "active" is a contact criteria
(returns 600 contacts)
and show contacts with any donation date < 1/1/2009 is a donation criteria
(returns 400 contacts)
both criteria together returns 300 contacts
OR
Show Contacts with status = "pending" (returns 450 contacts)
and show contacts with any pledge date < 3/1/2009 (returns 200 contacts)
both criteria together return 150 contacts

both sets of criteria return 389 contacts

You could then have another set of DO NOT SHOW (exclude from results)
criteria of 100 contacts limiting the final result set to 289 contacts.



Would there ever be a reason why I should do Option B? I could have 10,000
contacts and 30,000 donations and 5,000 pledges

I'm off to go change my code back to closer to Option A (which is what I
started with but then I decide to add in Donation criteria (such as "Any
Donation date < 1/1/2009") and I changed things all around to do multiple
sub queries with UNIONs and all that jazz (not sure what I was thinking).
It helped me see the light just by writing my last reply to you on this
newsgroup.

Mark

BruceM via AccessMonster.com said:
Why does it matter if it is difficult to read? That affects only the
developer. I really think the filtering code in the link from Allen
Browne
that I provided in my first response can do what you need, except you
would
be using it to build a SQL string to use as a record source. In any case,
you can build the SQL like this:

Dim strSQL as String

strSQL = "SELECT Freld1 FROM SomeTable "
strSQL = strSQL & "WHERE Field2 = True "

And so forth. It may be easier to read that way. The complexity coems
from
the WHERE condition in your case, I think. I would just have the code
look
at every criteria text/combo/list/check box. If there is a value, add it
to
the string with the appropriate syntax (quotes properly arranged for text
values, date delimiters for date values, etc.). If there is no value,
move
to the next control.

If you can build a query to do what you need, you can assemble the SQL
using
VBA. Access is going to read the SQL in any case. It doesn't care (at
least
not much) if it is a named query or a string in VBA.

I doubt you need a union query. You should be able to use OR for the
criteria, then sort by that field. You have lost me with Show and No
Show.
It may help if you could describe the table structure, then describe in
non-
database terms the results you would like. For instance:

All active contacts who pledged in the first half of the current year and
made the actual donation during the third quarter, or all inactive
contacts
who pledged any time last year and made the actual donation the same year
as
the pledege or never made the donation.

Mark said:
Bruce,

Thanks I think I just changed it around to build one sql statement (with
tons of subqueries). By building it the way that creates individual
queries
it actually helped make it easy to build one sql statement.

I think I should be able to either use this sql to do a variety of things.
However it is very difficult to read.

to explain my logic:

one set of criteria might have
1. ContactStatus = 'active' and ContactType = "xxx' (one query used to
restrict contacts)
2. Any donation date > 1/1/2009 (which is select contactid from
tblDonation
joined with tblcontact where donationdate > 1/1/2009 groupby contactid)
3. Any pledge date (similar to donation)

So within each of the three types I could have multiple criteria requiring
an 'AND' and to 'AND' the three types I need to JOIN them together.

Now if I have a second set of criteria (set1 OR set2) I need to do a UNION
operation (to show set1 OR set2)

Now if I have sets of SHOW Criteria and sets of NOSHOW criteria then I
need
to do a Left join with NOSHOW.ContactID is NULL to return result in SHOW
and
NOT IN NOSHOW.

By "intelligently join" I mean when building this massive string I build
the
query in the best possible way.
Example: If the string is really simple "show contacts with contactstatus
=
"active" it ends up being a very simple query, however it there are 20
lines
of criteria the query is much bigger.

By having the 1,2 and 3 above is what made this complex to implement. I
started by just having 1 and it was just one query.

However after now thinking about it a bit more I might be able to change
my
design to be simplier by combining 1,2 and 3 above into one query. I
think
I might of gone off and built something a bit too complex (but it works),
I
hate when I do that! I have had the flu for the past few days (so I blame
the sickness).

Hope that made sense,
If you can think of anything I could change let me know,
Mark
I would go with creating one query. I would create it on the fly rather
than
Click to expand...
[quoted text clipped - 60 lines]
replies,
Mark
Click to expand...
Click to expand...
Click to expand...
 
Bruce,

Thanks that is the path I'm on now, a base query and building a wherestring.

I think you missed my question, which was
A - use a base query (10,000 contacts joined with 30,000 donations and also
joined with 5,000 pledges)
and then build a where clause and group by result to get contactIDs
OR
B - use my other more difficult method (described below option B) which is
one query that uses a variety of subqueries to achieve the same result.

Both build a sql string, but the sql strings vary a good deal, but they both
get the same result.


I have both methods written so I'll probably load my database with more
records and do some tests.
Hoping Option A is the better method because the sql generated is much
simplier.

Mark

PS: Yes Donations are also related to Pledges (to optionally indicate
whether this donation fulfills part of a pledge), but not needed for this
exercise.




BruceM via AccessMonster.com said:
I am not clear about the Where condition. Do you mean that you want to see
only active contacts with a donation date before the first of the year, or
that you want to see all active contacts regardless of donation date, and
all
contacts (active and inactive) who made a dontation before the first of
the
year, and all active contacts who made a donation before the first of the
year. However, I get the drift of what you are trying, I think.

I would not construct and delete multiple queries. You could use a base
query, build a Where string, and combine the two:

strSQL = "SELECT * FROM qryMain "
strSQL = strSQL & strWhere

This assumes you have constructed your Where condition, represented by
strWhere.

You could use several base queries if the opportunity presents itself.
These
would be unchanging queries. You can build and delete queries on the fly,
but if I was doing that I would not bother to name the queries, but rather
just use the resultant SQL.

A base query comprising all three tables (or just two of them) will
probably
be read-only, which is fine if you just want to view the results.

Again, if you can build a query that gives you the desired result you can
assemble the SQL in VBA.

In terms of design, I guess I would have expected donations to be related
to
pledges so that you could know when a pledge has been fulfilled.

Remember, in building a WHERE, including AND, OR, and what have you, you
can
build the first part, then add the second part (let's say an OR) only if
it
contains anything. Assuming you use controls to assemble the criteria, if
control is blank you can ignore it. If a group of controls is for the OR
conditon, and if all are blank, you can ignore the OR. If the only
control
with a value is Active, your Where condition will be:

"WHERE Active = True"

The point is that you can build as little or as much Where as you need.
Example: Show Contacts with status = "active" is a contact criteria
(returns 600 contacts)
and show contacts with any donation date < 1/1/2009 is a donation criteria
(returns 400 contacts)
both criteria together returns 300 contacts
OR
Show Contacts with status = "pending" (returns 450 contacts)
and show contacts with any pledge date < 3/1/2009 (returns 200 contacts)
both criteria together return 150 contacts

both sets of criteria return 389 contacts

You could then have another set of DO NOT SHOW (exclude from results)
criteria of 100 contacts limiting the final result set to 289 contacts.

Would there ever be a reason why I should do Option B? I could have
10,000
contacts and 30,000 donations and 5,000 pledges
Click to expand...
Mark said:
Ok here's my last question:

The table structures are as follow:

tblContact (primary key ContactID)
tblDonation (donations made by each contact, foreign key ContactID)
tblPledge (pledges made by each contact, foreign key ContactID)

I always want to return a list of contactIDs
Option A: Use a base query of tblContact joined with tblDonation and also
joined with tblPledge
and then add in the entire where clause and then group By ContactID. The
where clause has a mixture of ANDs, ORs and NOTs.

Option B: The "I don't know what I was thinking route but maybe it does
run
faster???"
Construct multiple queries if needed (Contact criteria using tblContact),
(Donation Criteria using tblDonation),
(pledge Criteria usign table pledge) then join all of those results
together
(to do the equivilent of AND from above). To do the equivalent of OR
(from
above) you union sets of queries together and to do the equivalent of NOT
(from above) you left join/is not null two queries.

Example: Show Contacts with status = "active" is a contact criteria
(returns 600 contacts)
and show contacts with any donation date < 1/1/2009 is a donation criteria
(returns 400 contacts)
both criteria together returns 300 contacts
OR
Show Contacts with status = "pending" (returns 450 contacts)
and show contacts with any pledge date < 3/1/2009 (returns 200 contacts)
both criteria together return 150 contacts

both sets of criteria return 389 contacts

You could then have another set of DO NOT SHOW (exclude from results)
criteria of 100 contacts limiting the final result set to 289 contacts.

Would there ever be a reason why I should do Option B? I could have
10,000
contacts and 30,000 donations and 5,000 pledges

I'm off to go change my code back to closer to Option A (which is what I
started with but then I decide to add in Donation criteria (such as "Any
Donation date < 1/1/2009") and I changed things all around to do multiple
sub queries with UNIONs and all that jazz (not sure what I was thinking).
It helped me see the light just by writing my last reply to you on this
newsgroup.

Mark
Why does it matter if it is difficult to read? That affects only the
developer. I really think the filtering code in the link from Allen
Click to expand...
[quoted text clipped - 102 lines]
replies,
Mark
Click to expand...
Click to expand...
Click to expand...
 
Back
Top