Custom report tool for users in Access

Your logic is a bit offf.

1. when reporting you shuld be able to select the Report itself an perhaps the parameters.
Selecting the "Table" is not really done because a user might mistakenly select the "Customer" table as the basis for a "Students" report.
1a. A report has hard coded fields, ...you cannot "easily" select (hide/show) certain fields.

2. Selecting multiple tables would be worse because of the relationships involved.

In the above scenarios users should either be taught how to create reports, or you should make a simpler interface (where gain, the user might select a report and select a few parameters)

But all of this would require you to explain in more detail exactly what you have, and exactly what you need.

3. You can use code like this to export an Access report to Excel:
    DoCmd.OutputTo acOutputReport, "YourReport", acFormatXLS, "C:\YourFolder\YourReport.xls"
But be aware that the Excel data will be static (Not linked to the Access data)
Also know that formulas are not exported,
Finally, Exported reports will not look "exactly" like the Access report.

JeffCoachman

A bit deeper....

In your first two scenarios, you are basically asking for an interface to create Reports.
This is what the report wizard does.
If Ad-hoc reports are needed, ...then you should train them to use the Report wizard.

In a typical "Report Menu", the basics reports are already created, the user will simply be selecting the specific report they want.
(ex: Customers, Orders, Students and Classes, Patient Visits, ...etc)
Once the (existing) report is selected, your interface can ask for things like: Cities, dates, Price ranges, ...etc

Make sense?

Hope his helps

JeffCoachman

Finally, regarding your screenshot...
I am not quite sure I understand how that interface would be used in conjunction with creating reports, ...?
,...But please reply to my above posts in sequence first.

JeffCoachman

If a user is sufficiently technically savvy that he can be trusted to choose a table or group of tables as the basis for a report, then he can learn how to use the report wizard.  It is a wizard after all and does much of the work for you once a "query" is built.  It even builds queries for you if you choose items from related tables.  When my users need to create their own reports, I give them a database that includes linked views that they can then build on.  This allows me to flatten lookups so the user isn't troubled by them and also to create common joins.  The users DO NOT share this database.  Each user has his own sandbox.   If they design a report that everyone loves, I import it into the main app so everyone can use it.

I also create a simple export form where they can choose a destination, a file name, and a query, and the app will export to excel for further refinement.  I also create a table and an interface where they can log and document their queries so they don't loose them.  Of course, some users are better and more organized than others but I give them an infrastructure that gives them a fighting chance of creating useful, repeatable results.

Regarding the "canned" reports in my app, I have a reports table that includes definitions and criteria.  The criteria specifies whether a column is required, optional, or not supported.  In any given app, there are usually a dozen or so major attributes that the users want to use as selection criteria so although my selection sub-app is customized for each app, the concept is the same and I import the working parts from an old app into a new one and customize the criteria part.  The report table also includes a query name if the basic data can be exported to Excel.  Most list type reports can have their base data exported but not the complex ones.

PS - using "improve" and "web app" in the same sentence is an oxymoron.  I have never seen a web interface that is superior to what you can create with a client server tool.

No matter where your going to do this (Access or web), the problem your going to find is that most users lack the knowledge to go after the data.   The second you realize that, you move away from giving users total freedom to "canned" reports and some programming.  

The best you can do is offer some predefined base tables/queries to pull data from, allow them to select fields, then do and / or's for criteria on those fields.  Once that's done, allow them to save that definition.

Then allow them to output to printer, file (as PDF), or send as an e-mail.

While that's not totally ideal, it does usually cover most of the data requests.  But once you get into grouping, sum'ing, etc, it gets a lot more complicated and your going to have to have someone develop it with all that entails.

Been down this road countless times.

Jim.

I do understand the complexity of a reporting screen and that most users will not understand.

Do you have an example of what you're talking about above? Have you done this successfully?

When I say 'improve' to a web app, the main goal is to make it web based and accessible from anywhere by our company and a select number of our vendors.

A lot here is still unclear.

Is your screenshot a view of your "web app"?
If so, ...how is this to be used to create reports in Access?

"I do understand the complexity of a reporting screen and that most users will not understand."
Then I am confused as to what you are asking for??
I thought you were asking for a Reporting menu system?

"Do you have an example of what you're talking about above? Have you done this successfully?"
Three Experts have replayed to your post.
So it is not clear if you read my first posts, or who you are asking this of...?

Please reply to the experts as they posted chronologically, so we know who you are directing your posts to.

Thanks

JeffCoachman

Jeff. We don't have a web app. We're in evaluation mode. The screen shot is in our current db. The screen was developed years ago. Each 'tab' or 'table' you select dumps data to a separate excel spreadsheet.

ALL
We are looking for a way for our users to pull reports into excel. They want it to be flexible so they can pick and choose what they want to see. All I'm looking for are cases where this has been successfully implemented (if at all).

<<When I say 'improve' to a web app, the main goal is to make it web based and accessible from anywhere by our company and a select number of our vendors. >>

 That cannot be done with Access easily.  Only way to run Access "over the web" via browser is to use terminal services and/or Citrix.   There is also the www.eqldata.com service.

 There are "web apps" in Access 2013, which via SharePoint, allows you to put something in a web browser.  These might work for you, but you'd be rather limited with them.  Reporting/e-mailing you'd be hard pressed to do.  What you have is the ability to do basic CRUD (Create, Read, Update, and Delete) operations on records.  Everything is done via macro's (no VBA coding language).

 Still might work for what you want, but I would suggest looking at a web based language (ASP.net, PHP, etc) if your looking to do something nice for remote end users via browser.

 You can get a feel for Access web apps by taking a look at some of the screen shots in this:

What's new for Access 2013 developers
https://msdn.microsoft.com/en-us/library/office/jj250134.aspx

Jim.

Jim. We're thinking of moving away from Access all together. A custom MVC app.

That's a good direction.  Access really doesn't have much web capability at this point.  Still good on the desktop for a lot of apps, but as a pure web app, no.

 You can use it for remote users as a front end app with a SQL Server back end somewhere or Azure (SQL in the cloud), but that would be with cases where your users are "known" and you have control of their PC's (and the ability to install software).

However in your case, since your looking to have ad-hoc anonymous users, Access can't really do the job your looking to have it do.   Access web apps are coming along, but their not there yet.

Jim.

Ad-hoc, anonymous users don't belong in a line of business application - period!  so I'm not sure where you're going with that.  Trying to accommodate known users working from off-site is one thing, strangers is a completely different type of application.  Access is absolutely the wrong tool for anonymous users but it can work for a known population working on-site and off-site.  You need to focus on why you need remote users and what aspects of the app need to be accessible to them.  That will tell you whether you actually need a web app and whether or not you need to convert your access app.  Then you can think about expanding the reporting tool.  Going to a web based tool, you won't have anything like the Access report and query wizards, you will be building completely from scratch.  There is also no migration tool from Access to the web.  Access web apps should probably not even have "Access" in their name since they are incompatible with any existing Access apps and there is no way to get from here to there short of a rewrite.  However, if the line of business app is OK the way it is but you want to expand reporting access, then if you convert the data to Azure, you can build a hybrid app that gives you some reporting capability while leaving the rest of the app unchanged (except for the data store).

If you do move to one of the .NET platforms, you'll find a wealth of reporting options, and many of those have builtin user-modifiable reports. I use DevExpress, and their reporting element is very nice, and you can provide users with the ability to design their own reports and such.

As others have said, this might be a tough sell with some users, and you'll very likely end up creating quite a few Views that will combine data in a manner that is more understandable to the user. Most of them don't have any clue what a database "relationship" is (and they don't care to know, either!).

<<Ad-hoc, anonymous users don't belong in a line of business application - period!>>

 The point was, it's users you have no direct control over.   But even so, yes you might have those types of users.  Stock availability, information, or a product catalog is something you might offer the public at large.  

 This certainly would be a more controlled situation (vendors get a logon and password), but the intent is not to have them install any software.

 There are many companies that operate web sites like this, so I don't understand what your trying to say either.

 Suffice to say, Access is not the tool for the job.  

Jim.

Also, many e-commerce sites provide this type of filtering capability. Have you considered using one of those? I'm working with a client who is using Magento, and it seems to be very complete in that area.

Scott. DevExpress looks very interesting. Thank you for the feedback!

No one really provided a solution / platform / idea. Just a lot of comments as to what we are trying to accomplish.