Using Mutiple entries in a Field

[RobH]

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"


tbl-StateID
StateID State Country
1 ACT AU
2 NSW AU
3 VIC AU
4 QLD AU
5 SA AU
Note - this table will get into 2 or 3 digit StateID numbering...

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

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.

[Scott Price]

What does the "1,2,3,5" mean in the Available field of your Products table?

It actually looks to me like this table is most certainly NOT normalized! A normalized table would only store one piece of data in one place. You have 4 pieces of data in one place...

Let us know a little more about what those numbers might mean, and we'll try to point out a little better solution.

Regards,
Scott

[JConsulting]

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"


tbl-StateID
StateID State Country
1 ACT AU
2 NSW AU
3 VIC AU
4 QLD AU
5 SA AU
Note - this table will get into 2 or 3 digit StateID numbering...

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

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.

you can use the InSTR function for that

select * from yourtable where Instr([yourfield],"3");

or

select * from yourtable where [yourfield] Like "*3*";

stuff like that

[RobH]

What does the "1,2,3,5" mean in the Available field of your Products table?

It actually looks to me like this table is most certainly NOT normalized! A normalized table would only store one piece of data in one place. You have 4 pieces of data in one place...

Let us know a little more about what those numbers might mean, and we'll try to point out a little better solution.

Regards,
Scott

The 1,2,3,5 represents the StateID number from the tblStateID so in this case..

the product with a 1,2,3,5 would be availble In the ACT,NSW,VIC & SA only

[RobH]

you can use the InSTR function for that

select * from yourtable where Instr([yourfield],"3");

or

select * from yourtable where [yourfield] Like "*3*";

stuff like that

The problem here is that when I get to a StateID of lets say 13 I dont want it to be listed as part of a search result looking for 1 or 3 because these will more than probably be a different country even.

So far here is the full listing of the tbl-StateID

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

[Scott Price]

You have a many to many relationship between tblProducts and tblStates. That means that more than one product can be related to more than one state, and vice versa.

The best way to handle this is to create a third table with the ID field for each tblProduct and tblStates. Your queries are far easier to write, and your duplication of data is far less doing this.

Regards,
Scott

[RobH]

You have a many to many relationship between tblProducts and tblStates. That means that more than one product can be related to more than one state, and vice versa.

The best way to handle this is to create a third table with the ID field for each tblProduct and tblStates. Your queries are far easier to write, and your duplication of data is far less doing this.

Regards,
Scott

The problem is with 19 states and so many products i have a table with some 16500 records. is that still going to be the best way forward?

[Scott Price]

The problem is with 19 states and so many products i have a table with some 16500 records. is that still going to be the best way forward?

That depends on how you view data integrity rules!

To my mind, yes it still is the best way. The reason being that in your products table, how are you going to update the field that contains the stateid numbers if/when a product ceases to be available in one state? Or when you wish to add a state to expand the availability of a product? Or, when a state gets swallowed up by another? Any change that gets made to that field that contains multiple values will require you to write some kind of convoluted vba code to search the database, find the wrong values and change just them, leaving the other values unchanged! (Or worse, go through by hand and try to change them manually... perish forbid!)

As I've said before, database programs are built with data integrity rules in mind. They will allow you to bend and break the rules, but you do so at your own peril...

The linking table in a many to many relationship results in the least duplication of data, is easiest to update, and is easiest to query. If you like doing things the hard way, go ahead! But don't expect many here on this site to be too keen to help you out when you run into problems :-)

Regards,
Scott

[MMcCarthy]

The problem is with 19 states and so many products i have a table with some 16500 records. is that still going to be the best way forward?

Hi RobH

It might be worth your while reading this tutorial on Database normalisation.

Database Normalisation and Table Structures