Results 1 to 3 of 3

Search for a term in multiple fields from a single table

  1. #1
    pjtessi9 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    May 2019

    Unhappy Search for a term in multiple fields from a single table

    Need help..
    I have a table which has several fields like:
    Art_Discipline_1 (which contains numeric value from 1 to 24)
    Art_Discipline_2 (which contains numeric value from 1 to 24)
    Art_Discipline_3 (which contains numeric value from 1 to 24)
    There is one number per field that can be entered.
    I want to be able to make a query based on a specific value that will search for that value in Art_Discipline_1 OR Art_Discipline_2 OR Art_Discipline_3
    Click image for larger version. 

Name:	query.JPG 
Views:	10 
Size:	20.2 KB 
ID:	38421

    Let's say I want to search the value 13 in Art_Discipline_1 OR Art_Discipline_2 OR Art_Discipline_3....what should I type to get those results...
    I will enter the value in a list box and after pushing a button, it will execute the query based on that entered value.


  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    Your design is an issue. When we see fields named like fld1, fld2, fld3, it typically indicates non-normalized structure.
    You should review Normalization.
    Welcome to the forum.
    Good luck with your project.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    Expanding on what orange said, the reason it is a non-normalized structure is because it is designed like a spreadsheet instead of a dB. In fact, this type of design even has a term associated with it: "Committing Spreadsheet".
    Spreadsheet designs are typically short and wide.
    A RDB table is typically tall and narrow.

    The reason a non-normalized structure is "bad" is this: What happens if someone decides there needs to be "Art_Discipline_4"?
    You would have to modify the design of your table "RAI" (add a field).
    Then you need to modify the query designs.
    Then you have to modify the form designs.
    Then you have to modify the report designs.
    And don't forget to modify the VBA code!

    Basically a complete re-write of the dB just to add one more option.

    In a normalized structure, all you would need to do would be to add the value of the new option.

    Let's say your table design is:

    RAI_PK (autonumber)
    Dossier_RAI (text)
    Art_Discipline (integer)
    Art_Value (integer)

    There are already 3 Art_Disciplines. So to add a new Art_Discipline option, all you would need to do is enter, say "4", in the Art_Discipline field.
    DONE! No changes would need to be made on the dB. You could add 100 Art_Disciplines with no need to make one change to the dB.

    in my first few database designs, I "Committed Spreadsheet" in every one of them! I had a friend who was a dB programmer for a bank look at a dB I created and was told (straight to my face ) "It is a Bad design".
    After doing LOTs and LOTs of reading about Normalization, my designs started to get better and easier to use and maintain.

    And Welcome to the forum.......
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Query one search term under multiple fields
    By teachEmToBeGeeks in forum Queries
    Replies: 2
    Last Post: 03-15-2017, 02:09 PM
  2. Replies: 1
    Last Post: 02-27-2017, 06:09 AM
  3. Replies: 4
    Last Post: 07-27-2016, 09:44 AM
  4. Replies: 15
    Last Post: 11-26-2015, 10:27 AM
  5. Replies: 1
    Last Post: 08-30-2011, 06:35 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
Tech Forums: Microsoft Office Forums