Set a value to a field based on what another field equals

[Guest]

Hi, I hope this makes sense, but it's driving me crazy.

I have a table that stores products. For every product, there are 4 types.
For example, Lamp1 has choices Lamp1A, Lamp1B, Lamp1C, and Lamp1D. Every
product has it's own field because more than one can be marked.

I created a form that lets a user filter down records based on many options
(company name, country, etc...) but I want to add the ability to filter based
on product type. The user will only care about whether or not there is a
Lamp1, so the code should be something like this:
"If Lamp1A = -1 OR Lamp1B = -1 OR Lamp1C = -1 OR Lamp1D = -1 Then Lamp1 =
Yes"

My problem is that I want Lamp1 to be a field in one table, whereas the
actual products are in another. I feel like I need to run some sort of
query.

Thanks!

 

[Michel Walsh]

I think you should try to redesign your table like:

Product, Type, Quantity
Lamp1 A 10
Lamp1 B 4
Lamp2 B 5
Lamp2 C 1
Lamp2 D 9



instead of

Product A B C D
Lamp1 10 4
Lamp2 5 1 9



so you can easily, now, see if there is a product like Lamp1:



0 = DCOUNT("Quantity", "table", " Product = 'Lamp1' " )


(return true if there is no product Lamp1, any type)

or be specific about its type:


0 = DCOUNT("Quantity", "table", " Product = 'Lamp1' AND type = 'A'
" )




Hoping it may help,
Vanderghast, Access MVP