return one value or another

[CoffeeGuru]

I am looking up a product based on a unique code in tblProducts

I need to return one of two values

If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields

for example

tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue

query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'

 

[CazB]

A possible solution, albeit a not very elegant one, would be to use:

ProductColour: iif([product type]="multi","multi",max([red],[yellow],[blue]))

I'm sure someone else will come up with a better solution tho

 

[CoffeeGuru]

Hi CazB

Thanks for your suggestion, however i get an error that says "The expression you entered has a function with the wrong number of arguments."

That is before I add in all (200+) possible colours ha ha ha!
just thought I'd try it with the example 3

NB the observent amoung you will have noticed my naming error
[Product Type] should read [PACKAGE TYPE] :banghead:

 

[CazB]

lol ok.... I can see it would get complex with that many colours!!
Guess we'll have to leave it to the gurus

 

[CazB]

Just a question tho: do you really need to store all the colours as separate fields? What's your reasoning for doing that? Because whatever your reasoning is, ANY table with that many fields is probably going to become unmanageable...
It may be better to store the 'required' colours for a particular product in a separate table, linking on the product code?

 

[CoffeeGuru]

Yeah, I agree, the colours probably arent actually 200+ but it makes the point that I didnt want to count them all. However to answer your question, this file is a report from another database which spits it out like that, so in essence there are a lot of empty fields.

In hindsite It would have been better to do some manipulation in Excel before, but I was advised not to as it would mean monthly additional work where Access would do it all for me, Only it may not in this case.
Trouble is the advisor does not work with me or the company, so the problem is now mine.

BTW I also looked a DMax as it appears that Max only works with numbers, but I got no joy their either.

Did you give it a go, I am trying to work out what the error is saying as it looks good to me as an excel formula If(x=x,<then>,<else>)

 

[CazB]

looks like you can't use MAX that way... sorry! Ignore me

or try: iif([package type]="multi","multi",[red]&[yellow]&[blue])

 

[jdraw]

I think your major issue is table structure. But more info would be helpful.

What exactly does multi/solo represent?

A Yellow widget is sold only as a "solo" product? No, because Yellow is also pat of the "multi" list
You may wish to research Normalization.

 

[CoffeeGuru]

OK using your influence I have got this to work
Colour: IIf([PACKAGE TYPE]="multi","multi",Trim$([Red] & [Yellow] & [Blue]))

 

[CoffeeGuru]

jdraw
You are correct, but the dilema is I need to know how many yellow widgets were sold including those within a mutipack.

 

[CazB]

Correct me if I'm wrong, Martin.... but in answer to jdraw: my understanding was that 'solo' means it's one colour only (but you only know which one by looking through all the available colours!) and Multi means it is made up on more than one colour... again you would only know which ones by looking at all the fields, but for the purposes of what Martin is trying to achieve here it's enough to say 'Multi'

 

[jdraw]

I'd like to see the definition of
-solo
-multi
In addition please put an overview in a few lines of what the business is.
We are a manufacturer of..., we buy and sell... we have Customers who.... we package...

The reader can only guess what your business context is unless you tell us more. It is unlikely that a Product/PackageType exists in isolation.

 

[David R]

To add to the chorus, if you store the colors the correct way, any package with one associated color would be "solo" and more than one associated color would be "multi" - you just saved yourself another stored field!

 

[CoffeeGuru]

1. Correct me if I'm wrong, Martin.... but in answer to jdraw: my understanding was that 'solo' means it's one colour only (but you only know which one by looking through all the available colours!) and Multi means it is made up on more than one colour... again you would only know which ones by looking at all the fields, but for the purposes of what Martin is trying to achieve here it's enough to say 'Multi'

Good Grief!

Hi CazB

Correct

1. 'solo' means it's one colour only (but you only know which one by looking through all the available colours!)
2. Multi means it is made up on more than one colour... again you would only know which ones by looking at all the fields

For my exercise There is a single field that states solo/multi but a host of fields that have a colour for each.
I just want to know if there is a value in the field. If there is I want to know which colour that is. so if field Package Type = "multi" then return "Multipack" thats all I need to know, however if field Package Type = "solo" then I just want to know which of all the possible colours it is but looking at the range and returning the only non blank field.

DavidR - The table already has that level of detail
jdraw - solo = only one colour
multi = >1 colour
overview in a few lines of what the business is
we sell inkjet cartridges for printers in single packs and multipacks. the single pack will be Black, Cyan, Magenta, Yellow and a host of others for business printing like those on the wall posters you see in highstreets.
mutipacks contain multiple solo inks not some random mix of all the colours to produce puke green or martian blood, if we need it we will produce it as a new solo cartridge.


I hope this answers the majority of questions, it was not my attempt to start a civil war.

CasB - Thanks for your insite, it works for me for now.