How To Split Text And Then Select Them Individually ?

[niloufar]

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:CODE


teacher; nurse; engineer; doctor


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

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

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:

ID	Name	Positions	Gender	Address
1	jane	teacher	F	123 dhkdlk
3	nina	engineer	M	778HDJJ

result for last row will be:

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

[recyan]

Till someone comes along, just check if below gives some guidelines :

SELECT 
    Table1.ID, 
    Table1.FName, 
    Table1.Positions, 
    Table1.Gender, 
    Table1.Address, 
    Table2.APT
FROM 
    Table1 
    LEFT JOIN 
    Table2 
    ON 
    Table2.Position like "*" & Table1.POSITIONs & "*";

Thanks

[niloufar]

HI,

Thank you very much and I tried it, but it didn't return anything

Till someone comes along, just check if below gives some guidelines :

SELECT 
    Table1.ID, 
    Table1.FName, 
    Table1.Positions, 
    Table1.Gender, 
    Table1.Address, 
    Table2.APT
FROM 
    Table1 
    LEFT JOIN 
    Table2 
    ON 
    Table2.Position like "*" & Table1.POSITIONs & "*";

Thanks

[recyan]

Based on the data given by you, it should have worked. Am puzzled.
Try

..........

ON 
    Table2.Position like "*" & TRIM(Table1.POSITIONs) & "*";

Thanks

[niloufar]

Thank you very much, I guess that was me not you.... Thank you agian

Based on the data given by you, it should have worked. Am puzzled.
Try

..........

ON 
    Table2.Position like "*" & TRIM(Table1.POSITIONs) & "*";

Thanks

[recyan]

Glad you got it working.
If TRIM() worked, just see if you can use it before submitting / storing the data to / in the table & then check if your query works without the TRIM().

Thanks

[niloufar]

yes I did and worked well, Thank you for bein awesome

Glad you got it working.
If TRIM() worked, just see if you can use it before submitting / storing the data to / in the table & then check if your query works without the TRIM().

Thanks

[recyan]

Best of Luck with your project & Thanks.