Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Unanswered: Searching Multiple Fields in access 2003

    Hi,

    I've designed and built a database to hold part information on a Tester we built.

    The Tester is made up of stacks -> elements - > instruments - > boards - >components.

    Problem I have just run into is some components are used in more then one board.

    I currently have fields in the component table which are Board ID, Additional Board ID 1, Additional Board ID 2 etcetra.

    I am writing to ask; I Want to be able to display component information at board level. I can do this with the first 'Board ID', but not the 'additional board id' fields.

    Is there a way to do this?
    OR is there a way to hold multiple values in one field?

    any help will be greatly appreciated,

    Justin

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Justin!

    1) Never put multiple value in one field.
    2) I suggest, learn something about DATABASE NORMALIZATION,
    3) Learn something about PRIMARY KEY and FOREIGN KEY.

  3. #3
    Join Date
    Jan 2007
    Posts
    2

    Fair Point

    OK So i shouldn't give it a multiple value.

    How would i go about searching for a single value in more then one field then?

    And if a match was found then display that record?

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Examine;
    1) DATABASE NORMALIZATION
    2) PRIMARY KEY, FOREIGN KEY
    3) RELATIONSHIPS

    Every components record must include a link (foreign key) on boards table.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by JustinP@serco.com
    OK So i shouldn't give it a multiple value.

    How would i go about searching for a single value in more then one field then?

    And if a match was found then display that record?
    you do something like the following

    Component (PK CompID, ... )

    Board (PK BoardID)

    ComponentOnBoard (CK CompID, CK BoardID)

    Key
    Table (Field 1, Field 2, ...)
    PK = Primary Key, CK = Combined Key

    The only time you should ever use the component(compID, BoardID 1,BoardID 2, BoardID 3) method is if every component excists on 3 boards never more and never less (and even then you normally wouldn't do it)
    Last edited by m.timoney; 01-18-07 at 08:59.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    a quick lesson in ER.

    draw a box for each table then draw a line between each box that needs to know about another box then label the lines as 1 or * at each end 1 if the table links to only 1 row in the other table, * if it's more than 1. anywhere you have 1-1 ie 1 row points at 1 row then you should probably merge the tables (not always true), EVERYWHERE you have *-* you should add a table in between making it look like the following

    [table1] 1-* [newTable] *-1 [table2]
    Last edited by m.timoney; 01-18-07 at 09:18.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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