Negative values in combined crosstabs

[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.

 

[Guest]

Hi Jerry -

First the easier one, to change the color of a control on
a form if the field is negative, use conditional
formatting. (Select the field, choose Formatting,
conditional formatting and set the options as needed).

Now, I haven't been able to quickly see which of the
queries you list below is the one that is having the
problem. So I'll give you a couple possiblities and if
this doesn't help, please post again with just the query
that is not working correctly.

One problem could be that you have an INNER JOIN between
your orders and your inventory. An Inner Join only
selects records that are in both tables. So, if there is
no record in the inventory table that matches the record
in the orders table, the entire row wont show. Is that
the problem you're having? If so you need a LEFT JOIN.

Another possiblity is that you need an nz() somewhere.
Let's say the value in inventory is null, and the order
qty is 3 you've got null-3 and you want the answer to be -
3, right? But you're getting null because to access
unknown (null) -3 is still unknown. What you need to do
is wrap the inventory amount in an nz as in:
nz(FieldOrCalc, 0)-3 In english: If the value of
fieldorcalc is null, use zero instead.

Anyway if that doesn't do it, if you post just the query
that is causing the problem, I can probably help.

-----Original Message-----
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.


.

 

[Jerry]

Hi,
Thanks for the reply.
Responses interspersed with your reply.

Hi Jerry -

First the easier one, to change the color of a control on
a form if the field is negative, use conditional
formatting. (Select the field, choose Formatting,
conditional formatting and set the options as needed).

This control is a subform control with it's source object set to the first
query listed and conditional formatting is not available. Wondered if there
was some other way to do it in the query itself.

Now, I haven't been able to quickly see which of the
queries you list below is the one that is having the
problem. So I'll give you a couple possiblities and if
this doesn't help, please post again with just the query
that is not working correctly.

Not real sure but I think it's the first one.

One problem could be that you have an INNER JOIN between
your orders and your inventory. An Inner Join only
selects records that are in both tables. So, if there is
no record in the inventory table that matches the record
in the orders table, the entire row wont show. Is that
the problem you're having? If so you need a LEFT JOIN.

I had this problem earlier with the third query and did this on it after
making a cartesian query to join it to, which allowed all the rows to show.

Another possiblity is that you need an nz() somewhere.
Let's say the value in inventory is null, and the order
qty is 3 you've got null-3 and you want the answer to be -
3, right? But you're getting null because to access
unknown (null) -3 is still unknown. What you need to do
is wrap the inventory amount in an nz as in:
nz(FieldOrCalc, 0)-3 In english: If the value of
fieldorcalc is null, use zero instead.

Yes, I used this in the first query listed for just that reason, but I see
that I made an error in the way I used it. Tried it again with both terms
nz'ed to 0 seperately and that did it.
Had it set to alternate to what it was originally. DUH!
Now, if I could just turn it red....

Thank you

Anyway if that doesn't do it, if you post just the query
that is causing the problem, I can probably help.

-----Original Message-----
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 [Inventory Available] 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.


.