searching form multiple Values in a Text Field

[Guest]

Hi..

I have a table of Products and StateID. The products with a field for which
states they are availble using the codes from the StateID table

tbl-Products
ProductID Description Available
1 "Blue Coffee Table" "1,2,3,5"
2 "Red Coffee Table" "2,3,4,19,21,18"


tbl-StateID
StateID State Country
1 AU ACT
2 AU NSW
3 AU VIC
4 AU QLD
5 AU SA
6 AU TAS
7 AU NT
8 AU WA
9 NZ AUCKLAND
10 NZ BAY OF PLENTY
11 NZ CANTERBURY/WEST
12 NZ GISBORNE
13 NZ HAWKES BAY
14 NZ NELSON/MARLBOROGH
15 NZ NORTHLAND
16 NZ OTAGO/SOUTHLAND
17 NZ TARANAKI/WANGANUI
18 NZ WAIKATA
19 NZ WELLINGTON/MANAWA

I already have the Customers StateID figured out from the customers Country
& State. Eg Mr John Smith Vic AU = 3

How can I have a from that has a Product Selction that only shows Products
where the "3" is included in the tblProducts.Available - Note: 13 should not
be displayed..

I know the how to have a field show the list where the entire field matches
the requried Like Country = AU or State = VIC but using this normalised style
and multiple entries in the one field im a little lost.

 

[John W. Vinson]

Hi..

I have a table of Products and StateID. The products with a field for which
states they are availble using the codes from the StateID table

tbl-Products
ProductID Description Available
1 "Blue Coffee Table" "1,2,3,5"
2 "Red Coffee Table" "2,3,4,19,21,18"

Then your table design IS WRONG.

Fields should be atomic. Storing a one-to-many relationship in a comma
separated string in your Available field *is the root of your problem* and is
incorrect design.

tbl-StateID
StateID State Country
1 AU ACT
2 AU NSW
3 AU VIC
4 AU QLD
5 AU SA
6 AU TAS
7 AU NT
8 AU WA
9 NZ AUCKLAND
10 NZ BAY OF PLENTY
11 NZ CANTERBURY/WEST
12 NZ GISBORNE
13 NZ HAWKES BAY
14 NZ NELSON/MARLBOROGH
15 NZ NORTHLAND
16 NZ OTAGO/SOUTHLAND
17 NZ TARANAKI/WANGANUI
18 NZ WAIKATA
19 NZ WELLINGTON/MANAWA

I already have the Customers StateID figured out from the customers Country
& State. Eg Mr John Smith Vic AU = 3

How can I have a from that has a Product Selction that only shows Products
where the "3" is included in the tblProducts.Available - Note: 13 should not
be displayed..

I know the how to have a field show the list where the entire field matches
the requried Like Country = AU or State = VIC but using this normalised style
and multiple entries in the one field im a little lost.

The proper design would have a third table, Availability, with two fields -
ProductID and StateID. It will take some VBA code to parse the text string
into numbers (using the Split function perhaps). A Query is going to be VERY
snarky. I'd really suggest dumping your product table out to Excel; use the
Text To Columns feature to split the field into its components, and reimport
into a properly normalized table.

John W. Vinson [MVP]