Results 1 to 8 of 8
  1. #1
    sqrrl is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    4

    Question How can I apply multiple filters to a query/report using a form?

    Suppose I have a following table:



    BRAND MODEL COLOR
    Audi 100 Beige
    Audi 200 Red
    Renault 300 Black
    I want to have a form with 2 dropdown lists:
    • one for Brand (with Audi and Renault),
    • one for Model (with 100, 200 and 300).

    Also, the form would have this very table as a report or something.

    I want to be able to select Audi, and then the second dropdown list would only show 100 and 200, and the report would only show the first two rows.
    Then I select 200 in the second dropdown list, and the report only shows the second row.

    It doesn't have to be exactly like this, it doesn't even have to be a form, but i want a step-by-step narrowing search with results displayed on each step.
    Is it possible in MS Access?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Make three tables - tblMake, tblModel, tblColor

    Make a fourth table - tblRepairs

    Each table should have an Autonumber field and this Autonumber field should be assigned as the primary key. Each table's Primary Key should have names like MakePK, ModelPK, ColorPK, RepairsPK. The first three tables should have an additional field. This field will describe the Make, the Model, the Color

    In tblRepairs place additional fields to store the Primary key values of the other tables. Something like - MakeFK, ModelFK, ColorFK

    Every time you crash one of your cars, you can create another record in tblRepairs. Simply enter the PK value in the FK fields of tblRepairs

  3. #3
    sqrrl is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    4
    I think that's an answer to another question...

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by sqrrl View Post
    ....I want to have a form with 2 dropdown lists.....
    The wizard can guide you through the process of adding a combo box to your form. All you need is a form and a table.

  5. #5
    sqrrl is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    4
    I can add combo boxes. I can't figure out the rest...

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Using the hypothetical scenario presented in post #1, I can not figure out the rest either. You will need tables. You need tables with relationships. You will also need a table to store some sort of activity. For instance, you buy a car, you take a car in for repairs, you sell a car, etc.

    This is why I suggested you build some tables. Right now, your question does not seem defined. Perhaps after you build some tables you can build a query. Then with the query the comboboxes will make more sense.

  7. #7
    sqrrl is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    4
    There is one not-so-hypothetical example of a table, and there's no need for any relationships, really. What about repairs? I don't do or plan any repairs and I have no idea why you keep bringing this up. Did you read my question at all?


    I can do this very thing in, say, python + sqlite3 + qt, by re-executing a query on a table with a bunch of “where” clauses, but in Access I can't show a query so I want a report, and I don't know how to automatically update the source of query, so I supposed I'd have to do filters. I hope you can read this.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by sqrrl View Post
    .....re-executing a query on a table with a bunch of “where” clauses, but in Access I can't show a query so I want a report, and I don't know how to automatically update the source of query, so I supposed I'd have to do filters. I hope you can read this.
    You can adjust what the combobox will query by adjusting the SQL in the combobox's RowSource property.

    You can employ DoCmd.OpenReport and include a where clause in the statement. Your where clause can use the values of controls on your form or the form's entire Recordset or whatever criteria you wish.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple filters on a continuous form
    By Ray67 in forum Forms
    Replies: 3
    Last Post: 01-26-2013, 10:57 PM
  2. Report based on multiple filters
    By Johnny12 in forum Reports
    Replies: 1
    Last Post: 12-20-2012, 02:57 PM
  3. Apply Multiple filters
    By spitfire122 in forum Access
    Replies: 2
    Last Post: 07-01-2011, 10:02 AM
  4. Replies: 0
    Last Post: 03-14-2011, 08:38 AM
  5. Using a form to apply a condition to a Query
    By JeepWidow in forum Forms
    Replies: 0
    Last Post: 12-22-2008, 10:29 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums