SQL Query Problem

[ria4life]

Can anyone Help....

I have this code running which works fine....except that it returns values like 33, 34, 63.

I would like it to find an exact match IE: "3"




SELECT [New Discplinary Cases].[DAN No], [New Discplinary Cases].LNAME, [New Discplinary Cases].FNAME, [New Discplinary Cases].[Pass No], [New Discplinary Cases].[Violation Number 1], [New Discplinary Cases].[Violation Number 2], [New Discplinary Cases].[Violation Number 3]
FROM [New Discplinary Cases]
WHERE (([New Discplinary Cases].[Violation Number 1]) & ([New Discplinary Cases].[Violation Number 2]) & ([New Discplinary Cases].[Violation Number 3])) LIKE "*" & 3 & "*"

 

[pbaldy]

You'd use

= 3

instead of

LIKE "*" & 3 & "*"

Your criteria looks odd, concatenating 3 fields together. The design isn't normalized, with 3 fields for violations. Those should probably be in a related table with a record for each violation.

 

[ria4life]

ok tried the =3 ....it only returns an exact match from the first column

 

[MarkK]

That looks like a normalization problem. If a Case can have many Violations, then the Violations are a separate table. Stacking your Violations INSIDE the Case record makes them, as you are discovering, almost impossible to retrieve.

 

[pbaldy]

Normalization problem! Why didn't I think of that?

 

[MarkK]

The classic, the all too common, many things in one row.

 

[ria4life]

Thanks on the Normalization Issue.....was able to get the desired results and it works as expected:


SELECT [New Discplinary Cases].[DAN No], [New Discplinary Cases].LNAME, [New Discplinary Cases].FNAME, [New Discplinary Cases].[Pass No], [New Discplinary Cases].[Violation Number 1], [New Discplinary Cases].[Violation Number 2], [New Discplinary Cases].[Violation Number 3]
FROM [New Discplinary Cases]
WHERE ([New Discplinary Cases].[Violation Number 1]) = 3 OR ([New Discplinary Cases].[Violation Number 2]) = 3 OR ([New Discplinary Cases].[Violation Number 3]) = 3