Custom Queries for multiple users

  • Thread starter Thread starter John
  • Start date Start date
J

John

We've had an Access2000 Application designed for us which works rather well.
However the problem I am having is that my users are able to create queries
for whatever and whenever they want. Queries are being created for a one
time use and then never used again, but the query is not deleted by the
creator. So you can imagine how many useless queries there are.
I've read somewhere that I can/should hide the database window from my users
and give them some kind of functionality whereby they can create custom
Queries via a Form to view the required data. But that query isn't really
created in the database window, it's just a temporary query?

Can someone point me in the right direction to begin to achieve this.
If I hide the DB window how do my users view any info they might need.

Thanks for any help.
 
Have you concidered a form that in VBA creates a query at
form_load, and then deletes the query at form_exit?
If you create a "general" query (that your users later can
"customize" for their use) by using the query's SQL
in VBA, there will not be created any more useless
queries. You can then also hide the database window
without "stripping" your users for possibilities...

-This works just fine with the db I administer for users
in my company.

kind regards;
Torgeir
 
I'm not sure I understand.
I'm sure your aren't sugesting that my users know SQL
Could you Expand on this a little please.
 
We've had an Access2000 Application designed for us which works rather well.
However the problem I am having is that my users are able to create queries
for whatever and whenever they want. Queries are being created for a one
time use and then never used again, but the query is not deleted by the
creator. So you can imagine how many useless queries there are.
I've read somewhere that I can/should hide the database window from my users
and give them some kind of functionality whereby they can create custom
Queries via a Form to view the required data. But that query isn't really
created in the database window, it's just a temporary query?
Click to expand...

To some extent it depends on what kind of queries the users are
creating. If they're creating custom queries with different tables,
different joins, different fields and so on, then education (or
providing each user with their own frontend database) is the best bet.

More likely, they're creating queries with "Jones" or "Brown" or
"Smith" or "Hernandez" on the criteria line. If so, a Parameter query
with

=[Forms]![frmCriteria]![LastName]

will let them use one and the same query for any number of names.
Parameter queries can be very flexible; if you're not using them, they
may be the solution you need!
 
Hi John,
Hey... I've had this same problem presented to me just
today.

I have a parameter query set up now...
That my users get a pop up form... asking for a
CaseWorkers ID.

They enter their Worker ID... and then the query spits out
all cases pertaining to that Worker ID

Well... my users want to be able to query more.. than just
by CaseWorker ID

They'd like to be able to query by
CaseWorker ID, City, State, CaseNumber

or whatever.... is this possible ?

Aaron
(e-mail address removed)
-----Original Message-----
We've had an Access2000 Application designed for us which works rather well.
However the problem I am having is that my users are able to create queries
for whatever and whenever they want. Queries are being created for a one
time use and then never used again, but the query is not deleted by the
creator. So you can imagine how many useless queries there are.
I've read somewhere that I can/should hide the database window from my users
and give them some kind of functionality whereby they can create custom
Queries via a Form to view the required data. But that query isn't really
created in the database window, it's just a temporary
Click to expand...
query?

To some extent it depends on what kind of queries the users are
creating. If they're creating custom queries with different tables,
different joins, different fields and so on, then education (or
providing each user with their own frontend database) is the best bet.

More likely, they're creating queries with "Jones" or "Brown" or
"Smith" or "Hernandez" on the criteria line. If so, a Parameter query
with

=[Forms]![frmCriteria]![LastName]

will let them use one and the same query for any number of names.
Parameter queries can be very flexible; if you're not using them, they
may be the solution you need!


.
Click to expand...
 
Hi John,
Hey... I've had this same problem presented to me just
today.

I have a parameter query set up now...
That my users get a pop up form... asking for a
CaseWorkers ID.

They enter their Worker ID... and then the query spits out
all cases pertaining to that Worker ID

Well... my users want to be able to query more.. than just
by CaseWorker ID

They'd like to be able to query by
CaseWorker ID, City, State, CaseNumber

or whatever.... is this possible ?
Click to expand...

Sure. Look at Query By Form. A query can have scores of criteria.
 
Consider implementing the query by form applet from
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'.

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms and two tables from the DH_QBF.mdb, creates some master queries, and
deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main Form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, or a graph. Most formats allow the
user to automatically open the target application. The Word merge process
will open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP


Aaron said:
Hi John,
Hey... I've had this same problem presented to me just
today.

I have a parameter query set up now...
That my users get a pop up form... asking for a
CaseWorkers ID.

They enter their Worker ID... and then the query spits out
all cases pertaining to that Worker ID

Well... my users want to be able to query more.. than just
by CaseWorker ID

They'd like to be able to query by
CaseWorker ID, City, State, CaseNumber

or whatever.... is this possible ?

Aaron
(e-mail address removed)
-----Original Message-----
We've had an Access2000 Application designed for us which works rather well.
However the problem I am having is that my users are able to create queries
for whatever and whenever they want. Queries are being created for a one
time use and then never used again, but the query is not deleted by the
creator. So you can imagine how many useless queries there are.
I've read somewhere that I can/should hide the database window from my users
and give them some kind of functionality whereby they can create custom
Queries via a Form to view the required data. But that query isn't really
created in the database window, it's just a temporary
Click to expand...
query?

To some extent it depends on what kind of queries the users are
creating. If they're creating custom queries with different tables,
different joins, different fields and so on, then education (or
providing each user with their own frontend database) is the best bet.

More likely, they're creating queries with "Jones" or "Brown" or
"Smith" or "Hernandez" on the criteria line. If so, a Parameter query
with

=[Forms]![frmCriteria]![LastName]

will let them use one and the same query for any number of names.
Parameter queries can be very flexible; if you're not using them, they
may be the solution you need!


.
Click to expand...
Click to expand...
 
Back
Top