sub query wild card 2

[HomeGrowth]

I have a table1 contains a unique text field (6 char) called ‘LotNumber’ that allow user to create a record for each lot.

I have a table2 contains a text field (225 char) called ‘LotAffected’ that allow user to enter all the lot numbers that are affected by this record.

Table 1 Table 2
LotNumber Rec LotAffected
1 A 2,3,7
2 B 1,4,5
3 C 6
4 D 1,2,3,4,5
5 E 2
6
7

I try to build a query return all records that has a match in LotAffected

Query1(what I want)
LotNumber Rec LotAffected
1 B 1,4,5
1 D 1,2,3,4,5
2 A 2,3,7
2 D 1,2,3,4,5
2 E 2
3 A 2,3,7
3 D 1,2,3,4,5
4 B 1,4,5
4 D 1,2,3,4,5
5 B 1,4,5
5 D 1,2,3,4,5
6 C 6
7 A 2,3,7

I try using a sub-qry, but it only return records with exact match, how can I put a wild card for it to return all record? Can this be done in SQL or Query, or need to do it in VBA to loop through the records. Below is SQL statement from a query.

SELECT [Table2].*
FROM [Table2]
WHERE ((([Table2].[LotAffected]) In (SELECT [Table1].LotNumber FROM [Table1])));

Only 2 records returned where they have an exact match.
LotNumber Rec LotAffected
2 E 2
6 C 6

Thanks for any helps/suggestions

 

[PCLewis]

Well you have to understand that a text value of "2,3,7" has nothing to do with a text value of "2" in terms of the "IN" SQL keyword.

You really should redesign your LotAffected sgtructure otherwise ANY solution you are provided here will only get you past this one hurdle. Down the track you will have others.

If you really want to leave your tables structure as it is, then you could get to you goal if you ensured that the start and end of that field contained a comma as well. The reason is that you could then use a select statement like this:

SELECT Table1.Lot, Table2.Rec, Table2.LotsAffected
FROM Table1, Table2
WHERE (((InStr(1,[LotsAffected],"," & [Lot] & ","))>0))
ORDER BY Table1.Lot, Table2.Rec;



Lot	Rec	LotsAffected
1	B	,1,4,5,
1	D	,1,2,3,4,5,
2	A	,2,3,7,
2	D	,1,2,3,4,5,
2	E	,2,
3	A	,2,3,7,
3	D	,1,2,3,4,5,
4	B	,1,4,5,
4	D	,1,2,3,4,5,
5	B	,1,4,5,
5	D	,1,2,3,4,5,
6	C	,6,
7	A	,2,3,7,

The reason you need to enclose the field in commas is to make the INSTR work. If you did not always have comma's around every number in the list then you could never be sure of your matches. (i.e. 2 is in the string 21, so would match, but ,2, is not in ,21, so will not match)

 

[Remou]

Hi
Could this work?
[tt]SELECT Table1.lotnumber, Table2.lotaffected
FROM Table1, Table2
WHERE (((Table2.lotaffected) Like "*" & [table1].[lotnumber] & "*"));[/tt]

 

[DotNetGnat]

i would suggest redesigning the database!!!

-DNG

 

[Remou]

Drat, sorry PCLewis, forgot to refresh.

 

[r937]

if you want to do it this way --

WHERE InStr(LotsAffected
       ,','&LotNumber&',') > 0

then as mentioned, you would not match the first or last item in the comma-separated list, so what you have to do is also put commas around the LotsAffected value


you can either actually store them in the column, or else change the query to this --

WHERE InStr(','&LotsAffected&','
           ,','&LotNumber&',') > 0

you can do the same with LIKE instead of INSTR but you would also have to handle commas the same way, for the same reason -- '*2*' would match '21' but '*,2,*' would not match ',21,'


nevertheless, the best advice is to redesign

one reason is that the comma-separated list will always guarantee you a table scan, rather than utilizing any indexes on the data

r937.com | rudy.ca

 

[HomeGrowth]

PCLewis and Remou

Thanks for the suggesting ideas. I worked around PCLewis's SQL statement by adding a calculated field to add the commas in front and end of each field, so it works; However, Remou's SQL statement works even better without any modification. Each one of you have earned a star from. Thank you very much!

In addition, I agreed that redesigning the database is the best route to do, but when you don't have the ownership of the database, you couldn't do it. I just manipulate the data to create a report. However, Thank you everyone. You are always the best!