MS access - generate query visually

jex57

Board Regular
Joined
Oct 29, 2015
Messages
62
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,646
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 29, 2015
Messages
62
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,646
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,847
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,223
Messages
5,485,476
Members
407,502
Latest member
Bloople

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top