Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Option buttons, forms and queries (Access 2000)

    OK, I have a database with military personnel records, sorted by Name, SSN, Rank, Grade, MOS Code, UIC, Gender. I have no problem creating queries and reports, however most of my colleagues are not very query savvy (not at all). I am wondering if it is possible to create a form that my users can use to create ad hoc queries.

    I know I can add option buttons for fields within an option group for the fields in my table, but that will allow them to choose only one field to include (1,2,3,etc.). What I want to be able to do is create a form like the one shown to allow my users to choose which fields to include in their query. It would also be great to allow them a place to enter the criteria for the values for which they are searching within their selected fields.

    Does anyone know how to do this?
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Option buttons, forms and queries (Access 2000)

    Creating your own Query Builder is quite a task. Before tackling it. ask yourself what your colleagues really need. In many cases, they only need to make simple selections.

    The Filter by Form interface is less daunting than the query design grid. Would your users be able to use that? You could use the filter they set to filter a report.

    The Sample Reports Database from Microsoft contains some examples of using a form to specify report criteria. You can download it from ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center.

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    Hans, as I am the novice compared to you, my users are novice to me, perhaps even infantile. I have the startup set to hide the database window to prevent them from inadvertently screwing something up (also hid all the tables, queries, macros, etc.). I can design parameterized queries to allow them to view all records by selection parameters, but I can only design the queries based on criteria that I think they would need.

    I may simply create a button to export the users' data to Excel, so that they can apply filters and countif statements themselves, as this would be far less time and work intensive for me. Almost anyone can use Excel (if they want reports, they can create their own database files), and, besides if I do all the work for them in Access, they will never become great Access users like me (lol).

    But, yes, I do want to know how to design a query builder, as my own boss must be able to see what I can see after I leave this Godforsaken job. Is there a tutorial for this? I recently purchased the Osborne Complete Reference, but for all of its 1200 pages, it is still a bit overly simple for me. If you could make a recommendation, I would greatly appreciate it.

    Thanks, Hans.

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    Take a look at Roger's Access Library - Create Queries demo
    It might give you some pointers.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Option buttons, forms and queries (Access 2000)

    It seems to me your are taking on a great deal of work to create a query builder when you already have the right click capability of Access to apply filters galore. I would suggest you create some common filter capabilities that can be applied by simply clicking a button, and then put some help on your form that tells users how to right click and apply filters. We regularly use this approach with clients, because as you indicate, you can never guess what somebody someday will want to do. If you have disabled the right click menus, you can modify the standard ones to only allow certain options, such as filter by selection, filter excluding selection, creating a custom filter using wild cards or whatever, and remove filter. On the other hand if you really want to challenge yourself, a query builder would probably occupy the rest of your time in that "job!"
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    From the form you show, there are 6 options to choose. Why don't you just create the 6 queries and give them 6 buttons to choose click? A drop down list could give them any other optional criteria necessary.

    Peter

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    Here is another option for you from The Access Web http://www.mvps.org/access/. It is a lovely little from you just insert into any database as is. It is very basic in its operation but works with no tweaking. The only downside to it I have found is if you want to choose fields from more than one table to limit your criteria. The way around that would be to build a query that has all of the fields you will ever need and direct your users to it.

    Peter N
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    Wendell, while the approach with filters is feasible for the common user, I am not talking about the common user. Most of my users have never used Access. In my attempt to make it idiot proof, I can think only from the perspective of what you and I think of as idiotic. Many times I have created highly-customized, fool-proof systems, or so I thought. What I did not take into consideration was that a determined idiot can get really creative and really screw stuff up if left to their own devices. Also, my users are not familiar in the least with SQL or VBA, so if they were to start altering query criteria (or worse yet the queries themselves), especially not knowing the values for which to query, they could make a true mess of their systems in a bug hurry. My approach of creating a query builder is in keeping with the spirit of giving them the freedom they need without creating the need for each user to become even remotely familiar with the intracacies of database design.

  9. #9
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    Herworth, the tables from which the users will be querying data contains 23 fields, 9 of which have relational lookups for values. The form I show in my original post is simply an example of what I would like to be able to create for my users. In reality, the number of ways they may want to query data is mind boggling. I have already created several parameterized select and crosstab reports for them to use, but I use the reasonable-person approach to design. The truth is that many of my users fall outside the framework of the "reasonable-person" model. Like I said before, the easiest way fro me to accomplish this is to create a function to export the existing tables and queries to Excel, whereby they can apply their own filters, sorting, countif statements, etc. I do appreciate your response, though, and look forward to any other recommendations you may have.

    Thanks.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Option buttons, forms and queries (Access 2000)

    In fact, you *are* talking about common users. Those are the kind of users all professional developers have to cope with. I never allow users to see the database window or mess with queries, or open tables, nor does Wendell or any of the rest of us who do this daily. There have been numerous threads on ways to allow users to make criteria selections from forms. The form then creates the SQL and executes it. If you haven't already done so, browse through the threads in the this board and see what other ideas have already been suggested for this kind of problem.
    Charlotte

  11. #11
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option buttons, forms and queries (Access 2000)

    PeterN, Wendell, Hans, all others: I downloaded the form at http://www.mvps.org/access and it works great! Thanks to PeterN and all the rest of you. I have browsed the threads referenced and will continue to do so for answers to other questions that will inevitably come up. Thanks again!

Posting Permissions

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