Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2014
    Posts
    6

    Unanswered: How to Join Two Tables on Multivalued Field?

    I have a field with several strings separated by semicolon. depends on the user selection these values (length) may change. Filed Name is called Position so they have 4 choices:
    teacher; nurse; engineer; doctor

    my code:
    Code:
    SELECT Table1.Name, Table1.Position, Table1.SEX, Table1.Address, Table1.Location
    FROM Table1 INNER JOIN Table2 ON (Table1.Position = Table2.Position) AND (Table1.Location = Table2.Location)
    GROUP BY Table1.Name, Table1.Position, Table1.SEX, Table1.Address, Table1.Location;

    In my db, I get this file and have to match the result from the Position field with my final report. So if any or all match with my position in my final report then display or filter my result base on these values.

    So at first I need to parse or split the data in my field, then select all but select them all individually. meaning one field has multi value..

    For example:

    CODE
    Select * from table x
    Where [position] = "teacher" ANDor " nurse" ANDor "engineer" ANDor "doctor";

    table1

    Code:
    ID	Name	       Positions	Gender	Address
    1	jane	        teacher	F	        123 dhkdlk
    2	sam	         doctor	M	        73 hfjk
    3	nina	         engineer	M	       778HDJJ
    4	BOB	         DOCTOR	M	       344 JDHDK
    5	SARAH	NURSE	F	       748 HDEHJ
    second table is a from a form on sharepoint and the final table I import to my Access is like this ->
    Code:
    APT	    BEDROOM	POSITION	                        DUE DATE
    23B	    2 BDROOM	TEACHER;ENGINEER	         6/3/2014
    21AA	    3 BDROOM	DOCTOR; NURSE; TEACHER	 09/2014
    my query only reads the latest row for now ; when the last row is read from table 2 then the query will return all the information from table 1 filtered based on positions in table two.
    result for first row will be:

    Code:
    ID	Name	    Positions	      Gender	      Address
    1	jane	    teacher	         F	    123 dhkdlk
    3	nina	    engineer	         M	     778HDJJ
    result for last row will be:
    Code:
    ID	Name	      Positions	Gender	Address
    1	jane	      teacher	   F	        123 dhkdlk
    2	sam	      doctor	          M	        73 hfjk
    4	BOB	      DOCTOR         M	         344 JDHDK
    5	SARAH	NURSE	   F	        748 HDEHJ
    Last edited by mydarian; 07-14-14 at 01:14.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Where position like "*doctor*" or position like "*nurse*".....

    The like word tells the sql engine to find a match for the word/letters that follow
    the * is a 'wild card' meaning ignore letters before (in the case of user like "*darian") or after (in the case of user like "my*")

    This however is a kludge. Its a workaround for bad table design, and you should fix that....
    gave a tabke for people
    a table for rows
    a table for peoples roles (a so called intersection table that identifes what roles a person has). This is a way if modelling a many to many relationship. Each row is a singke pairing of person AND role. A person can have more than one role, and there can be more persons with same role
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    6
    Thank you, but after solving this problem I have to be able read each row individually and then create a new report for each row.
    Since I have not much experience but only 2 months out of school; I think it is getting more complicated so I should stick with something easy like what you said using like* instead of normalizing the other table...

    what do you think ?

    Quote Originally Posted by healdem View Post
    Where position like "*doctor*" or position like "*nurse*".....

    The like word tells the sql engine to find a match for the word/letters that follow
    the * is a 'wild card' meaning ignore letters before (in the case of user like "*darian") or after (in the case of user like "my*")

    This however is a kludge. Its a workaround for bad table design, and you should fix that....
    gave a tabke for people
    a table for rows
    a table for peoples roles (a so called intersection table that identifes what roles a person has). This is a way if modelling a many to many relationship. Each row is a singke pairing of person AND role. A person can have more than one role, and there can be more persons with same role

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No thats a nasty, dirty workaround that merely papers over the symptons and doesnt fix the actual problem. If you dont fix the problem it will almost certainly become a major ussue in the future. Not least because you are using text to store toles as opposed ti the relational way if of storing data.

    why is this a problem?
    Your data is vulnerable to typos, spelling mistakes and duplication.

    There are good reasons to normase data, especially when using a relational database. Data integrity is the main reason. But also flexibility say you needed to change the title of nurse to nurse peactioner. Your way yoyd have to update every row containing nurse, the normalused relational way change the description for the nurse cide ONCE.
    As the db grows your way will be less efficient, take longer to search through.
    its clear that the designer of this system has little idea of relational db design. Fix that now before it becones a major project management task. It will hurt you badly ib the not too distant future
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by mydarian View Post

    ...I think it is getting more complicated so I should stick with something easy...instead of normalizing the other table...
    It would be easier, still, simply dumping the database and using pencil and paper...but not as productive! Healdem's advice is spot on; it all starts with the data and getting it right is the #1 priority!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jun 2014
    Posts
    6

    Thank you

    Thank you, Can you tell me how this table suppose to be?? to eliminate multivalue field?
    here is my code and my table2.postion is multivalued

    Code:
    SELECT Table1.Name, Table1.Position, Table1.SEX, Table1.Address, Table1.Location
    FROM Table1 INNER JOIN Table2 ON (Table1.Position = Table2.Position) AND (Table1.Location = Table2.Location)
    GROUP BY Table1.Name, Table1.Position, Table1.SEX, Table1.Address, Table1.Location;
    Last edited by mydarian; 07-14-14 at 01:01.

Posting Permissions

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