Results 1 to 10 of 10
  1. #1
    hbacchiocchi is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Location
    Boston
    Posts
    4

    Creating Search Database

    Hi Everyone,

    I'm trying to create a database for my job that will store info on scholarships in our area. Each record (scholarship) will have information on in many different fields. Some fields are text and some are lookup/combo boxes. I want our clients to be able to search for records based on some fields (combo boxes), but not others and then see their results (ideally in a split form). I have tried a few different ways, can't seem to get it to work. Either it comes up with no results or it comes up with hundreds of results (copies of one record).

    Any help/feedback/suggestions would be great!

    Thanks!

  2. #2
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Click image for larger version. 

Name:	ShowMeWhatYouGot.jpeg 
Views:	48 
Size:	24.9 KB 
ID:	23869

    Show us, what you tried, what results are you expecting, what errors you can see, etc. We can't do much with generalities like that.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.


    You might want to take the time to work through the tutorials at
    http://www.rogersaccesslibrary.com/forum/forum46.html


    I would also suggest designing your dB using pencil and paper or a whiteboard before typing on the computer.


    And see the search form here http://www.allenbrowne.com/ser-62.html

  4. #4
    hbacchiocchi is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Location
    Boston
    Posts
    4
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum.


    You might want to take the time to work through the tutorials at
    http://www.rogersaccesslibrary.com/forum/forum46.html


    I would also suggest designing your dB using pencil and paper or a whiteboard before typing on the computer.


    And see the search form here http://www.allenbrowne.com/ser-62.html

    Thanks! The tutorials are very helpful. I do have one question though on how would I normalize my database? Each of my records is a scholarship. One of my fields is "City of Residence". Some scholarships are applicable to many different cities. In the past I had used a lookup list but now I'm thinking that might have been part of the problem. How would normalize that so that there is only one value per field?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    As cyanidem said "Show us what you've got".

    how would I normalize my database?
    Would have to see your dB to know what you table structure is. Care to post your dB?

  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
    16,716
    Further to comments by Steve and cyanidem, do you have a description of the task(s) your database is intended to support. Start with a clear description, similar to the tutorials at RogersAccessLibrary (Steve gave the link).
    Build a data model; then test it with some sample data. Adjust the model as necessary to support your needs. Once the model is considered correct, use it as a blueprint for your tables and relationships.

  7. #7
    hbacchiocchi is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Location
    Boston
    Posts
    4

    DB Structure

    This is the previous table structure I was using:
    Click image for larger version. 

Name:	Scholarship Table.jpg 
Views:	32 
Size:	108.5 KB 
ID:	23969
    I never got any specific error message but when I searched (a form with unbound fields that ran a select query) I either got no results or 100s of results of one record.

    The fields are:
    Scholarship Foundation Text
    Scholarship Name Text
    Award Value Currency
    Renewable Yes/No
    Requires Combo
    Open Date Date
    Close Date Date
    Gender Combo
    Applicant Type Combo
    GED Yes/No
    School Level Combo
    Student Status Combo
    GPA Number
    Interest/Major Text
    School In Combo
    Reside In Combo
    Ethnicity Combo
    Citizenship Required Yes/No
    Other Requirements Text
    Other Info Text
    Website Hyperlink


    I went through the naturalization tutorial and split this into two tables: Scholarship Funders and Scholarships because some Foundations give out more than one scholarship. Therefore, removing duplicate values. The thing I am stuck on now is removing multiple values. Every field listed as "Combo" would have more than one value. For example, "Reside In" is a combobox with a lookup to the "Residences Table" which is a list of all the cities in Massachusetts. Many scholarships only apply to students who are residents of certain cities, therefore that field would have multiple values.

    Thank you everyone for all of your help, I really appreciate it! I've built simple databases before so I didn't think would be quite the project it has turned into.

  8. #8
    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
    16,716
    Did you work through a tutorial at the link that Steve provided?

    Spend 45 to 60 minutes working through this tutorial. Then apply what you have learned to your situation.

    Post back with specific issues. You have to get your database designed to support your requirements.

    Also, the Allen Browne search link is an excellent reference, once you have done the tutorial.

    Good luck with this project.

  9. #9
    hbacchiocchi is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Location
    Boston
    Posts
    4
    Access Tutorial Work.zip
    So I worked through the Hernandez Tutorial. I'm thinking I can make each one of the fields that I was using as a combo it's own separate table and each option it's own yes/no field. I'm now stuck as to what would be my primary key for each table?

    Thanks!

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My thoughts.....
    I'm now stuck as to what would be my primary key for each table?
    I use an Autonumber type field for my PK field in all of my tables. (But I do not display the autonumber on forms/reports)
    I add a "_PK" suffix to the PK field and "_FK" to all foreign key fields. I never use a text field as a PK field.

    More reading:
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

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

Similar Threads

  1. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  2. Creating a Search Form
    By midnight_puppy_2303 in forum Forms
    Replies: 5
    Last Post: 08-04-2014, 09:29 AM
  3. Need help in creating Search Form
    By bnar in forum Forms
    Replies: 2
    Last Post: 06-05-2012, 12:09 AM
  4. creating a search form
    By foxtet in forum Forms
    Replies: 4
    Last Post: 08-06-2011, 06:08 AM
  5. Creating a Search Field
    By SKUPenn in forum Programming
    Replies: 1
    Last Post: 01-28-2010, 08:39 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