I usually do this type of reports vertically, as in:

Item    Size     In Stock
------------------------------------------
ItemA   S        1
        M        2  
        L        0
        XL       0

ItemA   S        0
        M        0  
        L        5
        XL       0

just because it's a lot easier to generate a straight forward query, I'm going to simplify the query so it reflects the join to the sizes table only:

select items.PartNo,;
items.Stock, ;
sizes.Size ;
from items ;
inner join sizes on items.SizeID = sizes.SizeID

this will work for any type of sizing.

On the other hand if you must display the report horizontally I would add an "order" field to my size table, so all sizing schemes have common values, this field would hold values from 1 to n, where n is the number of sizes in the sizing scheme, then I would use the same method as above, adding that field to the query:

select items.PartNo,;
items.Stock, ;
sizes.Size, ;
sizes.SizeOrder ;
from items ;
inner join sizes on items.SizeID = sizes.nSizeID

In my inventory report I would use the grouping footers for each Item to display the available stock, the expression for the field with the stock for each size would look something like this:

iif(SizeOrder = 1,Stock,0) && For the first size (small for example)
iif(SizeOrder = 2,Stock,0) && For the the second size (medium)
.
.

It's a little more complicated, but if you have very well defined sizing shemes it shouldn't be too difficult to implement.


>Hi,
>Could I ask further question?
>
>If I decide to have seperate tables for item and sizing, then how could I show the item qty in this format without hardcode the size in report since the sizing for each item may not be same?
>
>

>Item                      S  M  X  XL  XXL
>------------------------------------------
>ItemA                     1  2  0   0   10
>ItemB                     0  0  0   1    0
>