User defined queries...

[dazza61]

Hi guys,

I've been asked to come up with something whereby management can create their own queries...thereby being able to design their own reports with their own chosen data, filtered, sorted to their own needs, etc....blah blah...

My first thought would be to design a customised form so you could choose which tables, fields, etc to display then string all the data up into an SQL statement thus providing the query data for a datasheet view....but then I thought about multiple table joins (inner and outer) and all the available possibilities and this put me off...

Then I though about perhaps opening a standalone "starter" query (can't be truly blank as Access won't save a query without any fields in the grid) in design view, so priveliged staff could drag their own tables, fields, et al, save this query, then run it after saving...

My problem lies in the fact that my security for our database lies in one of those "disable bypass key", shut off all startup options, database window, etc affairs so that if staff try and right click any query in design view they get zilch (obviously)...

Can anyone point me in the right direction as to what the best approach would be for this kind of problem?

Many thanks in advance

Darren

 

[georgedwilkinson]

Microsoft has spent more money writing a user friendly query engine than I could ever hope to improve on in my lifetime so I'm OK with doing this.

1. Create a separate backend database for queries.
2. Set up a refresh schedule and process to keep the "query" database up to date.
3. Teach your users how to use the QBE and give them a front-end linked to the "query" backend.

Now, the success of this really depends on the sophistication of your users and their ability to "grok" your design. We are doing this at a law firm I'm working with with pretty good success. Obviously, there are "power" users who can write pretty complex queries and there are some who frankly just don't get it. But the neat thing is that the super users help out the ones who need extra help and I only get called in for really complex stuff (subqueries and such).

 

[dazza61]

Sounds like a novel idea George and many thanks for your response.

Just to clarify so I get this right...

Using your approach, for your point 1) I'd use e.g. a Shell copy command to copy the "main" backend making a "snapshot in time" exact copy...
2) This takes care of point 1) in always having "refreshed" data which is up to date as the queries are run/created...
3) Then use a separate "front end" to link to the "copied" backend??

This seems sound plan, because if users "mess up" by using e.g. a "delete query" ;-) they are working on a copied backend and NOT the original...so if they compain they have done something really bad, I could always say "Oh my, you have killed our data, it is not easily replaceable, however for a raise in salary, I can rectify the problem...

**giggles**

Would I be right in the above assumptions??

Darren

 

[georgedwilkinson]

Hmmm. There are a lot of ways to accomplish this. You could look into replication, you could create a "job" that copies the whole database, or you could create a job that just copies the refreshed data to the reporting database.

Generally, you have the right idea. The topic is advanced (more advanced than I can answer off the top of my head), but I'm pretty sure it's something you can figure out. The caution I would have for copying the file is that there may be some locking issues.

Also, you might want to make the "copy" read-only so the user has NO expectation that any change they make there will be in the database next time they use it.

 

[dfenton]

Hmmm. There are a lot of ways to accomplish this. You could look into replication...

Care to say more about the replication suggestion? I just can't see any possible way replication could play any useful role here. If you were trying to prevent users from mucking up the data, replication won't help, because if you use it properly, you'd create a replica for the user to muck around in and unless you coded one-way synchs, the changes would get back to the real data file. That is, if the user deleted records in the replica, a synch would cause them to be deleted in the main database.

So, there is really no role for replication here, as you want an entirely one-way operation, i.e., copying the data and never pushing the changes back.

For what it's worth, I think I'd give the users only read-only access for an ad hoc querying module. But I don't think I'd build anything like this in the first place (see my other post in this thread).

 

[dfenton]

They always tell you they need complete freedom to do anything at all, but experience tells me that there are really only a finite number of tasks that they need to do in regard to data retrieval and report filtering.

A robust query-by-form interface will probably take care of most such cases, and you can control what they have access to. Combine it with some tools for executing updates of data, and if you do it through a QBF interface, you can do backups so that if a user screws up, the results can be rolled back (indeed, I'd probably put the results in a temp table, have the user sign off on them and only then apply them to the real data, while keeping the temp table data as a backup).

I think you should go back to your users and find out what their real needs are. Chances are you can address 90% of them with pre-built tools that will insure the safety of their data. For the other 10%, they have to ask if they really need to do it in the first place.

 

[dazza61]

Many thanks for your comments David...

I did try replication (for synchronising laptops to main office, not this issue) - but I didn't like it - it was okay for adding child records, but too complicated for full synchronisation. I think I'd rather code my own way of getting stuff updated....

David, I think your last point of going back to them is a good idea - my role isn't just IT - my main role is sales, sales, sales - but perhaps if I show them what would be involved in developing a reports system, etc they might just allocate me more IT time and take the whole concept of IT more seriously...

I now have plenty to go on; thanks once again to both of you.

Darren