Problem with concenating

[Ason1976]

Hi,

I have 10 fields in a table(Order Details). These fields are sizes of Clothing(YS, YM, M, L,etc.)

In a query, how can I concenate it so that the results appear as
(FIELD1#)-YS, (FIELD2#)-YM, (FIELD3#)-YL
10-YS, 8-YM, 6-YL
and have it so that it only displays the values greater than 0?

thanks in advance

ason1976

hope im not rambling

 

[Steve101]

SELECT YourYSFld & "-YS" AS YS,
YourYLFld & "-YL" AS YL,
....
YourLastFld & "-YLF" AS YLF
FROM tblYourTable

Will do the concatenation. I'm assuming you're familiar with the SQL; in the Query Grid, the Field cell for the first field would be entered like:

YS: YourYSFld & "-YS"

And so on.

When you say: Displays values only greater then zero, does that mean that you dont want to see 0-YS; it should just display as blank in that position.

To do this, you could use the following code:

IIF(YourYSFld=0,"",YourYSFld & "-YS&quot AS YS

for each of the fields.

On the subject of overall database design, its generally not a good idea to include all products in the OrderDetails table structure. Instead of having one record with multiple order products , its better to have many records each with a single order product. Read up on the subject of data normalisation.

Hope this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Ason1976]

HI Steve101,

Thanks for the info. I will have a go at it later tonite.

My database is structured so that only information pertaining to a single product is in my table. Unfortunately I need to see the different sizes on order. I considered setting up a seperate table but it justs gets complicated and I need simple(1 form/Subform) for the (l)users where I work.

ason1976

hope im not rambling

 

[Ason1976]

Hey Steve,

Without adding all the extra concenated fields, here is the monstrosity I cooked up with your help, to Display the Quantities:

  DisplayQuantity: IIf([PromoQuantity]=0,"",[PromoQuantity] & "-units, ") & IIf([YS]=1,"",[YS] & "-YS, ") & IIf([YM]=0,"",[YM] & "-YM, ") & IIf([Yl]=0,"",[Yl] & "-Yl, ") & IIf([Yxl]=0,"",[Yxl] & "-YXL, ") 
& IIf([s]=0,"",[s] & "-S, ") & IIf([m]=0,"",[m] & "-M, ") & IIf([l]=0,"",[l] & "-L, ") & IIf([xl]=0,"",[xl] & "-XL, ") & IIf([XXL]=0,"",[XXL] & "-XXL, ") & IIf([XXXL]=0,"",[XXXL] & "-XXXL, ") & IIf([XXXXLplus]=0,"",[XXXXLplus] & "-XXXXLplus, ")

Thank you very much

ason1976

hope im not rambling