Re: design: single or multiple queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Edward Reid wrote:

This is a question about designing queries and forms in terms of single
vs multiple queries and forms, differing sort order and differing
selection.

I need to response to user inputs with several choices of output.
However, I'm going to format all the outputs identically, just varying
the heading. So I could use the same form for all, and I could use the
same query by varying the selection conditions and sort order.

How would one normally do this? By using several similar forms pointing
to several different queries? Or by manipulating the attributes --
using the WhereCondition of OpenForm and ... well, I haven't figured
out how I would set the OrderBy property of the query, or even if
that's the right thing to do. (I'm figuring I'll have a switchboard
home form.)

But the question is, if the form layout and control bindings are the
same, would you attempt to use a single form and a single query and
alter the selection and ordering? Would you have a form and query for
each case? Something in the middle? (I seriously doubt it's the extreme
of a form and query for each case, but I don't know how feasible vs
confusing it will be to make it all work in one.)

Performance *is* a concern. This database currently has almost 2
million records in the main table, and the DB is about half a gigabyte.
It'll probably grow until it gets close to 2GB or starts showing
problems (that will take a few months), and then I'll have to decide
whether to delete old records, or move to MySQL or SQL Server.

And a couple of related questions:

1) How does the query OrderBy property relate to the Sort specs shown
in query design mode? I expected that the OrderBy property would simply
be ordering specified by the Sort specs, but I don't see the Sort specs
anywhere except in SQL view, and I do *not* see the OrderBy property in
the SQL view.

2) I may want to include some calculated values in the form as
displayed. Am I better off calculating these in the query or in the
form? I suppose it only really matters if I end up needing multiple
forms and/or multiple queries -- if I only need one of each, then I
suppose it doesn't matter much.


One form and one query! Anything else would cause a lot of
maintenave headaches.

A single set of bound controls really simplifies the problem
so one form is a no brainer.

The preferred method of filtering a form is to use the
OpenForm's WhereCondition argument, so you were on the right
track there.

Using a prebuilt query with the sorting included is not
reall an option. But using the form's OrderBy property is a
handy way to do it. Making a dynamic sort order is slightly
trickier and makes a third alternative a viable approach to
both this and the filtering question - create the form
record source SQL statment on the fly. Setting the OrderBy
property and setting the RecordSource property are nearly
the same amount of work so it more a matter of what you
prefer. The key to these is that the form needs to set it's
own properties as it's opening or the form needs to be open
before you start all this stuff. (Using OpenForm
immediately followed by trying to set its properties can run
into a race condition where the form might not be ready
enough for an outside mechanism to set the properties.) The
easiest way to communicate this to the form is to use
OpenForm's OpenArgs argument to pass the SQL statement (or
OrderBy string) and the data form to retrieve it in its Open
event.

Here's a general outline of the control form's code:

strSQL = "SELECT fld1, fld2, ... " _
& "FROM sometable " _
& "WHERE "
If Not IsNull(txtfldA) Then
strWhere = strWhere & " AND fldA = " & Me.txtfldA
End If
If Not IsNull(txtfldB) Then
strWhere = strWhere & " AND fldB = " & Me.txtfldB
End If
. . .
strSort = " Order By " & Me.txtSortfield
DoCmd.OpenForm "formname", _
OpenArgs:= strSQL & Mid(strWhere, 6) _
& " Order By " & strSort

Then the form can set its own record source using:
Me.RecordSource = Me.OpenArgs
in its Open event procedure.

If you prefer to use the WhereCondition argument, then you
would use most of the code above with:

DoCmd.OpenForm "formname", _
WhereCondition:= Mid(strWhere, 6),
OpenArgs:= strSort

and the form's Open event would be:

Me.OrderBy = Me.OpenArgs
Me.OrderyByOn = True



--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: field calculated....
    ... > Do you have a specific sort order? ... THE SQL QUERY IS: ... > MS Access MVP ...
    (microsoft.public.access.queries)
  • Re: sorting columns in a client-side page
    ...  Each keyword represents a sort order ... BY clause for a SQL statement in the variable $order, ... the switch, or if it doesn't, you get the default order, which might ... Then run the query. ...
    (comp.lang.php)
  • Re: using the "Order By" on a form...
    ... So then I tried to issue a refresh afterwards... ... rows from this query. ... selected has the appropriate sort order. ... orderby property, what command do you need to run to actually get it to ...
    (microsoft.public.access.formscoding)
  • Re: Orderby property
    ... and updating that to include the valid orderby at runtime. ... you put it in the SQL ... >> I am attemting to set the OrderBy property on a query ... >> listbox it will not apply the OrderBy property. ...
    (microsoft.public.access.queries)
  • RE: Offering a sorting choice
    ... The best way to do this is open the query in design view, then switch to SQL ... It will prompt for a sort order and then sort on what the user entered. ...
    (microsoft.public.access.forms)