MS access - generate query visually

[jex57]

Hi all,
Has anyone, or does anyone know of a access db, whereby the users through the guid can select the tables that they want, and a query can be built accordingly. I am trying to create an interface whereby some of the tables are know (so for example give me all the customers that have outstanding bills as a template), by selecting this, they have created a data source, and then using a drop down they can select any of the tables that have not been included in the table. When they click on execute, it creates the query in design view.

Alternatively is their a method that - similar to the query builder the user can drag and drop the tables they want on to a form and generate from there?

Thank you

 

[Joe4]

The Query Builder IS the easy tool to use to do this. There are also some basic Query Wizard to be used to walk people through certain types of queries (like the Unmatched Query Wizard).
However, you can only go so far, and users will need to have a base level of basic knowledge to create their own things in Access. I don't know if you can "dumb it down" enough for someone who is totally non-technical. But really, you don't want those type of people messing much with your database anyway (potential to really screw things up).

One thing I have done in the past is to create a few basic queries that joins somde common tables together that they can use to start with. Then they don't need to worry about relationships, and can use those queries to build on, by adding criteria, etc.

 

[jex57]

The Query Builder IS the easy tool to use to do this. There are also some basic Query Wizard to be used to walk people through certain types of queries (like the Unmatched Query Wizard).
However, you can only go so far, and users will need to have a base level of basic knowledge to create their own things in Access. I don't know if you can "dumb it down" enough for someone who is totally non-technical. But really, you don't want those type of people messing much with your database anyway (potential to really screw things up).

One thing I have done in the past is to create a few basic queries that joins somde common tables together that they can use to start with. Then they don't need to worry about relationships, and can use those queries to build on, by adding criteria, etc.

Reason for try in to see if I can emulate it is because I need to create something but in a format that can be better deployed. Db is SQL, so looking to prototype something similar to the ms access query builder but custom build it...but prototyping it in access is much easier. I have built custom views which I want to allow the user to modify but then save as their own view, and at the same time being able to visually see what they are creating

 

[Joe4]

I think I did something like this once a long time ago, when I created a default query that they would open up to get them started. I think I put read-only properties on the original query, so they could not overwrite it, only save it with a different name, so my original one does not get overwritten. I then put a menu item out there that would open this default query in Query Builder view.

 

[Micron]

I started on a form that allowed you to specify the table from a combo, which would then enumerate each field other than autonumber id, I think. The idea was to select a field and code would determine its data type and present combos for the operations I figured one would need. Thus text would include LIKE but not BETWEEN. Those were just decision I made. It also had operators such as AND, OR. I got so far with it and dropped it after a few complications and haven't gotten back to it. It sounds easier than it turned out to be. I guess it was the assumption that I'd likely never use it anyway that added incentive to quit.

I mentioned it to give a flavour of what can be done and figure that if I can imagine it, someone else has probably already done it.