J
Jerry
Hi,
I'm not sure this the right group for this but I have a form for recording
orders at a greenhouse that includes a subform showing current outstanding
orders in the form of a crosstab query arranged by size and color. There is
a second subform beside it that shows inventory available, also arranged by
size and color - a duplicate of the first subform's grid. This second
subform is filled by a select query as follows:
SELECT Inv.Size, nz(Inv.Red-OD.Red,inv.red) AS Red,
nz(Inv.White-OD.White,Inv.White) AS White, nz(Inv.Pink-OD.Pink,inv.pink) AS
Pink, nz([Inv].[Marble]-[OD].[Marble],[inv].[marble]) AS Marble,
nz(Inv.Novelty-OD.Novelty,inv.novelty) AS Novelty
FROM [Size x Color Inventory_Crosstab] AS Inv INNER JOIN [Size x Color
Orders_Crosstab] AS OD ON Inv.Size = OD.Size;
the [Size x Color Inventory_Crosstab] (aliased as Inv);
TRANSFORM Sum(Inventory.Pots) AS SumOfPots
SELECT PotSizes.Size
FROM (Colors INNER JOIN Varieties ON Colors.ColorID = Varieties.ColorID)
INNER JOIN (PotSizes INNER JOIN (Crops INNER JOIN Inventory ON Crops.CropID
= Inventory.Crop) ON PotSizes.PotSizeID = Inventory.[Pot Size]) ON
Varieties.VarietyID = Inventory.Variety
WHERE (((Crops.[Crop Name])="Poinsettias"))
GROUP BY PotSizes.Size, Crops.[Crop Name]
PIVOT Colors.Color;
totals and tabulates all varieties in the inventory by size and color, and
the [Size x Color Orders_Crosstab] (aliased as OD);
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT cart.Size
FROM cart LEFT JOIN [Order Details] ON (cart.PotSizeID = [Order
Details].PotSizeID) AND (cart.ColorID = [Order Details].ColorID)
GROUP BY cart.Size
PIVOT cart.Color In ("Marble","Novelty","Pink","Red","White");
totals and tabulates orders by size and color and is what fills the first
subform mentioned above. This works nicely to give an overall view of all
orders against inventory side by side, and being situated on a main form
that also contains customer info and a subform showing all orders for the
current customer in the main form along with another subform bound to the
currently selected order in the orders subform to show all items in the
current order, I can enter and change orders and immediately see both the
current order details and the overall orders and inventory.
If I enter orders exceeding available inventory, I get negative numbers in
the availability window, which is fine.
The problem that I have is when I enter an order for a size x color
combination that is not represented in the inventory, it doesn't show up as
a negative in the available inventory window - just a blank box.
How would you change these queries to make it display a negative value if an
order is entered for an empty inventory item? Also is it possible to change
the color of the cell in a crosstab if the value in it is negative?
p.s. The reason for this strategy is that our inventory is not arranged in
the same way as our orders. The actual inventory is made up of many
varieties, each with an associated color, and in numerous sizes, but we
track these according to the supplier we got them from and may have entries
for the same variety from more than one vendor. Orders, on the other hand
are concerned only with size and color of the plant. So we thought it would
be easier to just keep a running total of orders for each size and color
balanced against inventory totals for each size and color than to try to
decrement the inventory numbers in many different variety entries.
I'm not sure this the right group for this but I have a form for recording
orders at a greenhouse that includes a subform showing current outstanding
orders in the form of a crosstab query arranged by size and color. There is
a second subform beside it that shows inventory available, also arranged by
size and color - a duplicate of the first subform's grid. This second
subform is filled by a select query as follows:
SELECT Inv.Size, nz(Inv.Red-OD.Red,inv.red) AS Red,
nz(Inv.White-OD.White,Inv.White) AS White, nz(Inv.Pink-OD.Pink,inv.pink) AS
Pink, nz([Inv].[Marble]-[OD].[Marble],[inv].[marble]) AS Marble,
nz(Inv.Novelty-OD.Novelty,inv.novelty) AS Novelty
FROM [Size x Color Inventory_Crosstab] AS Inv INNER JOIN [Size x Color
Orders_Crosstab] AS OD ON Inv.Size = OD.Size;
the [Size x Color Inventory_Crosstab] (aliased as Inv);
TRANSFORM Sum(Inventory.Pots) AS SumOfPots
SELECT PotSizes.Size
FROM (Colors INNER JOIN Varieties ON Colors.ColorID = Varieties.ColorID)
INNER JOIN (PotSizes INNER JOIN (Crops INNER JOIN Inventory ON Crops.CropID
= Inventory.Crop) ON PotSizes.PotSizeID = Inventory.[Pot Size]) ON
Varieties.VarietyID = Inventory.Variety
WHERE (((Crops.[Crop Name])="Poinsettias"))
GROUP BY PotSizes.Size, Crops.[Crop Name]
PIVOT Colors.Color;
totals and tabulates all varieties in the inventory by size and color, and
the [Size x Color Orders_Crosstab] (aliased as OD);
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT cart.Size
FROM cart LEFT JOIN [Order Details] ON (cart.PotSizeID = [Order
Details].PotSizeID) AND (cart.ColorID = [Order Details].ColorID)
GROUP BY cart.Size
PIVOT cart.Color In ("Marble","Novelty","Pink","Red","White");
totals and tabulates orders by size and color and is what fills the first
subform mentioned above. This works nicely to give an overall view of all
orders against inventory side by side, and being situated on a main form
that also contains customer info and a subform showing all orders for the
current customer in the main form along with another subform bound to the
currently selected order in the orders subform to show all items in the
current order, I can enter and change orders and immediately see both the
current order details and the overall orders and inventory.
If I enter orders exceeding available inventory, I get negative numbers in
the availability window, which is fine.
The problem that I have is when I enter an order for a size x color
combination that is not represented in the inventory, it doesn't show up as
a negative in the available inventory window - just a blank box.
How would you change these queries to make it display a negative value if an
order is entered for an empty inventory item? Also is it possible to change
the color of the cell in a crosstab if the value in it is negative?
p.s. The reason for this strategy is that our inventory is not arranged in
the same way as our orders. The actual inventory is made up of many
varieties, each with an associated color, and in numerous sizes, but we
track these according to the supplier we got them from and may have entries
for the same variety from more than one vendor. Orders, on the other hand
are concerned only with size and color of the plant. So we thought it would
be easier to just keep a running total of orders for each size and color
balanced against inventory totals for each size and color than to try to
decrement the inventory numbers in many different variety entries.