User defined queries in access runtime

[Luke Bellamy]

Hi - I've had alot of people ask me can I write my own reports/queries
of the data using access runtime. Not sure if you can but I really would NOT
like to give show to the query writer in Access.

Wondering if anyone knows of a good util or app that you can generate the
info you want from underlying tables?
Any alternatives appreciated. Anyone prefer a more commercial approach
like Crystal Reports?

I'm sure I've seen a good little one around by the likes of tony toews and
stephen lebans.

 

[Arvin Meyer]

Hi - I've had alot of people ask me can I write my own reports/queries
of the data using access runtime. Not sure if you can but I really would NOT
like to give show to the query writer in Access.

Wondering if anyone knows of a good util or app that you can generate the
info you want from underlying tables?
Any alternatives appreciated. Anyone prefer a more commercial approach
like Crystal Reports?

I'm sure I've seen a good little one around by the likes of tony toews and
stephen lebans.

 

[Arvin Meyer]

Excuse the first (empty) post. Coffee hadn't kicked in yet <g>

I am unaware of anything by either Tony or Stephen to do this. If you have
an ODBC driver, there are numerous query writers. Here's a free one, that
works inside of Access, but I think you're looking for more than that:

http://www.mvps.org/access/modules/mdl0056.htm

Before I'd buy Crystal, I'd think seriously about offering full versions of
Access, which are cheaper (as part of Office), and easier to use.

While limited, you can use MS Query from Excel to import Access data and
build a report. With a little imagination and some temp tables in Access,
you can do quite a lot.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Nikos Yannacopoulos]

Luke,

For what it's worth, here's what I did: I have a sales reporting app
that "reads" detailed invoicing data (stored in the back end), and
wanted to give users the possibility to select/filter/group data in any
way they wanted. I have about 16 "input" fields (to analyze/filter/group
on) and 8 "output" fields (sales/returns/free goods volume/value etc.)
in a master query, that includes virtually every table in the database.
I give the user the possibility to select their filter(s), output fields
and grouping/sorting options through an unbound form, and then some VBA
code "reads" the user's selections and constructs the SQL expression for
a make table query (run through the code), which puts the results in a
temp table in the user's front end, where from they can view, print and
output to Excel for further analysis if required.
The advantage of this approach is users don't need to know how to make a
query, and they are not allowed to get into the design (the FE is an
..mde). Although my users have the full version of Access, I believe this
would run just as fine with the runtime version. Would something like
this work for you?

HTH,
Nikos

 

[Albert D. Kallal]

Actually, even a better approach is figure out what the parameters are, and
then build a form.

I mean, really, for a salesman report, you going to have a salesrep, date
range, and perhaps a few others like region, or city.

However, there is usually NOT very many parameters needed. I been deploying
and writing commercial software for 20 years now. I have NEVER EVER had ONE
OF my clients request that they need a query builder. In fact, if you meet
the needs of the client, then you find they don't even request new reports
anymore.

The solution I use (regardless if I am using the runtime or not) is simply
to build a nice report prompt screen. These screens not only make things
MUCH more easy for the end user, but in fact also make the application far
more easy to use. Here is some screen shots of this concept in action:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

 

[Luke Bellamy]

Thanks guys - a few things for thought there.

Users are begging for a generic reporting tool to write custom queries but
my problem and their problem is it is a complex DB model behind the scenes.
A normal user would not understand how to join tables, and the data contains
many relations to interest rates, tax systems, etc.

Like Arvin said, I may just be able to say to them if you have the full
version
of Access you can import/link to your mdb data files and use the query
wizard.
I just found Excel a bit fidly.

Thanks again,
Luke