J
Jerry
I have a greenhouse that wholesales nursery stock to retailers. The
inventory in each crop category is arranged by pot size and variety, each
variety having a color. Orders come in for a quantity of pots of a
particular size and color.
I have a crosstab query that summarizes the varieties in inventory by
pot size and color,
TRANSFORM Sum([Inventory Query].Pots) AS SumOfPots
SELECT [Inventory Query].Size
FROM [Inventory Query]
GROUP BY [Inventory Query].Size
PIVOT [Inventory Query].Color;
([Inventory Query]'s purpose is to retrieve the color of each variety which
is stored in the varieties table)
which produces this;
Size Marble Novelty Pink Red White
3"
600 1204 200
4" 300 4000 700 3400 500
6" 400 2950 500 6200 850
7" 100 400 150 1500 250
8" 33 249 99 715 100
10" 25
25 100 50
Boxes
10
12 5
Mini UFO
5
20 10
Trees
33 33 33
UFO
10
20 10
Wreaths
10
20 10
and one which does the same for the order details table,
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (Colors INNER JOIN [Order Details] ON
Colors.ColorID = [Order Details].ColorID) ON PotSizes.PotSizeID = [Order
Details].PotSizeID
GROUP BY PotSizes.Size
PIVOT Colors.Color;
producing this,
Size Marble Novelty Pink Red White
3"
14
4"
51
6"
50
7" 7
30
8"
10
Boxes
10
Trees
3
which is fine. What I'd like to do is create another with the same output
as the inventory crosstab but with the values at each position reduced by
the amount in the corresponding location in the orders crosstab query, thus
showing the number of pots remaining available for sale in each size and
color, to be displayed on the order entry form.
I just did these with the query builder, but don't know SQL well enough
to combine this information. Can anyone suggest alterations to the
inventory query to do the deed (or perhaps some other way)?
Thanks
inventory in each crop category is arranged by pot size and variety, each
variety having a color. Orders come in for a quantity of pots of a
particular size and color.
I have a crosstab query that summarizes the varieties in inventory by
pot size and color,
TRANSFORM Sum([Inventory Query].Pots) AS SumOfPots
SELECT [Inventory Query].Size
FROM [Inventory Query]
GROUP BY [Inventory Query].Size
PIVOT [Inventory Query].Color;
([Inventory Query]'s purpose is to retrieve the color of each variety which
is stored in the varieties table)
which produces this;
Size Marble Novelty Pink Red White
3"
600 1204 200
4" 300 4000 700 3400 500
6" 400 2950 500 6200 850
7" 100 400 150 1500 250
8" 33 249 99 715 100
10" 25
25 100 50
Boxes
10
12 5
Mini UFO
5
20 10
Trees
33 33 33
UFO
10
20 10
Wreaths
10
20 10
and one which does the same for the order details table,
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT PotSizes.Size
FROM PotSizes INNER JOIN (Colors INNER JOIN [Order Details] ON
Colors.ColorID = [Order Details].ColorID) ON PotSizes.PotSizeID = [Order
Details].PotSizeID
GROUP BY PotSizes.Size
PIVOT Colors.Color;
producing this,
Size Marble Novelty Pink Red White
3"
14
4"
51
6"
50
7" 7
30
8"
10
Boxes
10
Trees
3
which is fine. What I'd like to do is create another with the same output
as the inventory crosstab but with the values at each position reduced by
the amount in the corresponding location in the orders crosstab query, thus
showing the number of pots remaining available for sale in each size and
color, to be displayed on the order entry form.
I just did these with the query builder, but don't know SQL well enough
to combine this information. Can anyone suggest alterations to the
inventory query to do the deed (or perhaps some other way)?
Thanks