Using a Field to contain a LIST

[Spidey3721]

Is there a field type that I can use that will contain a list; where I could
add and remove items easily. Here is what I am trying to do:

I am trying to track customer selections of different product options. I
made an earlier post titled Complicated Design Solution (Sorry - not sure
how to link to it...), but I think I made the question too long - I will try
to simplify...

I have a Locations table, a Products table, a Customers table, and a
Customer selections table. Each location has different products from the
products table that are available. I then have a form that I use to record
many different product selections via various combo boxes. I want the combo
boxes to only contain the products that are available at the (current)
locationID...

This brings me to my problem - how do enable the user to set which products
are available at each location. I envision there being another form, amybe a
"Project Setup" Form
that will somehow store information in one of these, or a new table about
each products availability for each job...


OPTION #1
include a Yes/No field in the Products table for each Project. How do I
create a new field when the user adds a new project ? Should I avoid this
approach because it may result in too many fields for one table

OPTION # 2
Create a third table for the product, which includes a record for each
product that is available for each job. This sounds like a lot of
duplication, especially considering that I wish to apply this to many
different Products table, and that each products table will have 10-50
products available.

OPTION # 3
Use a text, or memo field at the end of each record in the Products table
that includes a list of ProjectID's that have it as an option. This might
become a bear when trying to add a new project and when trying to use this
field in a WHERE statement on my product selection comboboxes (I guess I
could say WHERE [listmemo] like *(projectID)*, but it could be done in VBA I
imagine - but wondering if there is a more traditional tactic for handling
this...

Maybe I'm missing a fundamental element with my whole approach...Looking for
opinions...

 

[Sandra Daigle]

Option2 is on the right track - A good design will require the least amount
of maintenance and will provide maximum flexibility so before you start
designing forms, design the tables. Just to clarify one thing, is Job the
same as location? You've referred to jobs further down in your question but
I don't see it in your tables so I'm going to go forward assuming that they
are the same. Let me know if I'm wrong and we'll go from there.

You already have the 4 basic tables, now you need to build a new table to
show the Many to Many relationship between Products and Locations. A
Location has many products and a Product can be in many locations. The table
that represents this relationship is ProductLocations (or LocationProducts)
and it can have as few as 2 fields:

ProductLocations
---------------------
ProductID
LocationID

This is easily shown as a subform on either the Product Form or the
Locations Form (or Both).

Customer Selections is probably a similar table that relates Customers and
Products. Again, I would think you would have a Customer form, then the
CustomerSelections subform. You haven't indicated where Location (or Job) is
initially entered. Perhaps this is value that is determined when the
database is initially opened? If so, you probably have the LocationID value
available on an open form and it is pretty easy to filter the rowsource of a
combo box based on a value on an open form. In this case, the RowSource
query for the Products Combo would be a query including ProductLocations and
Products, joined on Productid and filtered on LocationID. The SQL would look
something like this:
'
Select
Products.productid,
Products.ProductDescr,
From Products
Inner Join ProductLocations on Products.PRoductId=ProductLocations.Productid
Where ProductLocations.Locationid=Forms!frmMyForm!LocationID

Hopefully this is enough to get your thoughts moving. You might also want to
look at the SelectRecords example on http://www.daiglenet.com/msaccess.htm
for some ideas on how to work with Many to Many relationships.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Is there a field type that I can use that will contain a list; where I
could add and remove items easily. Here is what I am trying to do:

I am trying to track customer selections of different product options. I
made an earlier post titled Complicated Design Solution (Sorry - not sure
how to link to it...), but I think I made the question too long - I will
try to simplify...

I have a Locations table, a Products table, a Customers table, and a
Customer selections table. Each location has different products from the
products table that are available. I then have a form that I use to record
many different product selections via various combo boxes. I want the
combo boxes to only contain the products that are available at the
(current) locationID...

This brings me to my problem - how do enable the user to set which
products are available at each location. I envision there being another
form, amybe a "Project Setup" Form
that will somehow store information in one of these, or a new table about
each products availability for each job...


OPTION #1
include a Yes/No field in the Products table for each Project. How do I
create a new field when the user adds a new project ? Should I avoid this
approach because it may result in too many fields for one table

OPTION # 2
Create a third table for the product, which includes a record for each
product that is available for each job. This sounds like a lot of
duplication, especially considering that I wish to apply this to many
different Products table, and that each products table will have 10-50
products available.

OPTION # 3
Use a text, or memo field at the end of each record in the Products table
that includes a list of ProjectID's that have it as an option. This might
become a bear when trying to add a new project and when trying to use this
field in a WHERE statement on my product selection comboboxes (I guess I
could say WHERE [listmemo] like *(projectID)*, but it could be done in
VBA I imagine - but wondering if there is a more traditional tactic for
handling this...

Maybe I'm missing a fundamental element with my whole approach...Looking
for opinions...