MS access - generate query visually
Results 1 to 5 of 5

Thread: MS access - generate query visually
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS access - generate query visually

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: MS access - generate query visually

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Oct 2015
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS access - generate query visually

    Quote Originally Posted by Joe4 View Post
    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

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: MS access - generate query visually

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,667
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS access - generate query visually

    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •