MS access - generate query visually

jex57

Board Regular
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

MrExcel MVP, Junior Admin
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
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top