Results 1 to 9 of 9
  1. #1
    alicevuap is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    8

    Create an advanced search form that filters through multiple fields of a database

    Hello!

    I am having some difficulties in creating a search form from a very complex database (250 fields). My database contains details about contracts with various schools, under different majors, some of them including up to 10 majors, plus many many other details. What i am looking for is to create a search form with multiple fields (i.e. institution name, country, city etc), some of them filtering through more than one field from the table (i.e. each record has a maximum of 10 majors and i want a search field that would filter through all of them). Also, as a result i would like a table that does not include all the data from the original table, but just a selection of them.

    Any ideas? Hope i was clear enough...



    Thank you in advance!!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,278
    Id do it similar to the way Excel does their filter.
    a list box with the fields available, dbl-click and its added to a table , user fills in the search item
    once the table is filled with [field], [value],
    scan the table and build the WHERE clause for the sql to pull.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    alice,

    You may get some ideas from this search article by Allen Browne.

    Good luck.


    After posting change:
    I just saw pBaldy's post. I reread your post and noticed your "the table". If your 250 fields are in 1 table, then
    I agree that your data structure is not Normalized. Also, I see Contracts, Majors, Institutions which would suggest 4 or 5 tables minimum.

    You might get some design insight from the free videos by Dr. Daniel Soper on Database. I recommend that you watch videos 1,2 and 4 of his 8 part series that begins here.
    Last edited by orange; 01-29-2016 at 01:41 PM. Reason: updated after the original post

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,284
    If you're saying you have fields like Major1, Major2, etc, your database is not normalized:

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    alicevuap is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    8
    Thank you! I m not an expert in Access, actually this is my first database and i have no background in programming .. I will check out the articles and videos and see what i can do.

    Once the table is filled, can i still make changes and organize it better without loosing the data?

    Again, thank you!!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    Possibly, but getting your tables and relationships designed to support your business is critical to successful database.
    Build a data model (see the videos) and test it against your requirements, adjust until it does what you need. Then, add your data...

    Here's a related post to help get you oriented with a data model in advance of physically building a database.

    Also, here is a link from someone who watched the Soper videos that were recommended.

    Good luck and don't be too quick to jump into the nitty gritty of Access before understanding some of the concepts and techniques of database.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,284
    If you decide to normalize the tables, I would expect you to be able to run some append queries to populate them from the original table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    alicevuap is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    8
    Great! Thank you for your advice!

  9. #9
    alicevuap is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    8
    I finally managed to create a new database, this time following your guidance. I now have the following tables:
    1. tblInstitutions (complete list of institutions participating in the program, with detailed info: code, name, address, postal code, city, country) - the only one containing data
    2. tblBA (the one containing info on the specific contract: agreement period, checkbox for signed/not signed to keep track of the process, scanned contract attachment)
    3. tblContact (contact details for each BA)
    4. tblMajors (majors offered at my institution, and their code)
    5. tblSubjectArea (subject area - the name of the major as it appears in the BA [ie the major is architecture-the name of the field in my institution-and the subject area is architecture and planning], and further details on the contract under the specific subject - number of students or stuff)
    6. tblLanguages (each subject area could be taught in more than one language + details such as the level required)
    + the junction tables

    I have created the following relationships:
    Click image for larger version. 

Name:	Untitled-1.jpg 
Views:	23 
Size:	236.0 KB 
ID:	24212

    As i was saying, the only table containing data is tblInstitutions, therefore i need a data entry form as per BA, friendly for other users, which contains the fields from all the tables, but i cannot figure it out how to deal with the many to many relationships and how to link them to the main table. Do i need to include the junction tables? If i want to be able to insert more contact details or more major for one BA, do i create tabs? How would i relate all the data so that at the end i could generate reports ie filter by a major and show each institution and all the details in the existing BA's under the specific major?

    Do you have any suggestions? Or could you guide me towards tutorials that might help (i googled it but i didn't find anything, maybe i'm not using the right keywords)?

    Thanks a lot!

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

Similar Threads

  1. Advanced Search Field in a form
    By raffi in forum Queries
    Replies: 2
    Last Post: 04-20-2015, 08:54 AM
  2. Replies: 2
    Last Post: 01-20-2014, 02:04 PM
  3. Replies: 5
    Last Post: 08-05-2013, 09:47 AM
  4. Multiple Tables, Multiple Filters + Search
    By michael.legge@rbc.com in forum Access
    Replies: 1
    Last Post: 05-09-2013, 03:15 PM
  5. Replies: 4
    Last Post: 05-29-2012, 01:32 PM

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 - Senior Forums