Need help with query strategy

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
 
A

Ari Hamara

When I try make query:
"INSERT INTO TapahtumanKappaleet VALUES(11, (SELECT
kappale_id FROM Kappale WHERE Otsikko = 'Integraalit'))
"

I get: Reserved error (-3025); There is no message for this
error.

That Select query works fine example in:
SELECT * FROM TapahtumanKappaleet WHERE kappale_id =
(SELECT kappale_id FROM Kappale WHERE Otsikko =
'Integraalit')

And insert query works file if I replace Select query with
its result, in this case 16. There is two cells in
"Taulukko" -table. Everything should work fine, but I get
that weird error. Anyone knows what is the problem?

Ari Hamara
 
G

Gary Walter

Ari Hamara said:
When I try make query:
"INSERT INTO TapahtumanKappaleet VALUES(11, (SELECT
kappale_id FROM Kappale WHERE Otsikko = 'Integraalit'))
"

I get: Reserved error (-3025); There is no message for this
error.

That Select query works fine example in:
SELECT * FROM TapahtumanKappaleet WHERE kappale_id =
(SELECT kappale_id FROM Kappale WHERE Otsikko =
'Integraalit')

And insert query works file if I replace Select query with
its result, in this case 16. There is two cells in
"Taulukko" -table. Everything should work fine, but I get
that weird error. Anyone knows what is the problem?
Hi Ari,

I don't believe using query in VALUES is supported.

If the 2 fieldnames in your table were "f1" and "f2",
I believe the following would work:

INSERT INTO TapahtumanKappaleet (f1, f2)
SELECT
11 As f1,
kappale_id
FROM Kappale
WHERE Otsikko = 'Integraalit';

Good luck,

Gary Walter
 
G

Gary Walter

Hi Jerry,

Without seeing your data and what
you really want do (for example, I would
think you would instead want to just update
Inventory to reflect Orders sent), this might be
how I would create your query.

First, I might use Nz function in TRANSFORM
clause of your crosstabs to change Null to 0

TRANSFORM Nz(Sum([Inventory Query].Pots),0) AS SumOfPots

TRANSFORM Nz(Sum([Order Details].Quantity),0) AS SumOfQuantity

Then I believe you should be able to bring
both xtabs into a query, join on Size, then
perform subtractions in calculated fields.


Click on "Create Query in Design View"

In the Show Table dialog box,
click on one of your xtabs,
click Add,
click on other xtab,
click on Add again,
and then click Close.

You should now show both xtabs
in the query designer.

Right-mouse click on the "Inventory" xtab
and choose Properties.
In the Alias row, type in
Inv
then close the Properties dialog box.

Right-mouse click on the OrderDetail xtab
and choose Properties.
In the Alias row, type in
OD
then close the Properties dialog box.

I believe you want to join them
on the Size fields.

Click and hold down on left table's
Size field
and "drag and drop"
over on right table's Size field.

You should now have a (join) line
connecting the 2 xtabs going
from Inv.Size to OD.Size fields.

Drag and Drop "Size" field from
Inv table down in to field row
of first column of grid.

All that's left is to enter your
calculated fields.

In an empty field row, type in

NewMarbleInv: Inv.Marble - OD.Marble

In another empty field row, type in

NewNoveltyInv: Inv.Novelty - OD.Novelty

Just do the same to create the other
calculated fields.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
-------------------
"Jerry" wrote:
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)?
 
J

Jerry

Hi Gary,

Thank you! That's just what I needed for the calculations. The only
problem is that the intersections where there are no values in the OD
crosstab are blank. Do you know how to make the remainder show up in every
cell that has a value in the Inv crosstab (whether there is anything in OD
to be deducted from it or not)?

The reason I'd like to do it in a query rather than updating the inventory
table is that the inventory table is actually oriented by variety rather
than color. Colors are stored in a Colors table, referenced by a Varieties
table, which is referenced by a VarietyID stored in the inventory table.
The inventory table has an entry for each variety and size that came from a
particular supplier, and may have entries for the same variety/size from
more than one supplier. The point of all this is that in order to update
the inventory quantities when an order is taken would require that we look
through several pages of inventory to find an entry with a remaining
quantity available to fill the request. The buyers don't care about
variety, only size and color.

All I really need here is to make sure we don't overbook certain
sizes/colors. I'd like to use this directly on the customer orders form so
the person taking orders can always see at a glance how many units of each
size/color are available, which is why I need to show quantities in all
cells.

Thanks again,

Jerry

Gary Walter said:
Hi Jerry,

Without seeing your data and what
you really want do (for example, I would
think you would instead want to just update
Inventory to reflect Orders sent), this might be
how I would create your query.

First, I might use Nz function in TRANSFORM
clause of your crosstabs to change Null to 0

TRANSFORM Nz(Sum([Inventory Query].Pots),0) AS SumOfPots

TRANSFORM Nz(Sum([Order Details].Quantity),0) AS SumOfQuantity

Then I believe you should be able to bring
both xtabs into a query, join on Size, then
perform subtractions in calculated fields.


Click on "Create Query in Design View"

In the Show Table dialog box,
click on one of your xtabs,
click Add,
click on other xtab,
click on Add again,
and then click Close.

You should now show both xtabs
in the query designer.

Right-mouse click on the "Inventory" xtab
and choose Properties.
In the Alias row, type in
Inv
then close the Properties dialog box.

Right-mouse click on the OrderDetail xtab
and choose Properties.
In the Alias row, type in
OD
then close the Properties dialog box.

I believe you want to join them
on the Size fields.

Click and hold down on left table's
Size field
and "drag and drop"
over on right table's Size field.

You should now have a (join) line
connecting the 2 xtabs going
from Inv.Size to OD.Size fields.

Drag and Drop "Size" field from
Inv table down in to field row
of first column of grid.

All that's left is to enter your
calculated fields.

In an empty field row, type in

NewMarbleInv: Inv.Marble - OD.Marble

In another empty field row, type in

NewNoveltyInv: Inv.Novelty - OD.Novelty

Just do the same to create the other
calculated fields.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
-------------------
"Jerry" wrote:
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)?
 
J

Jerry

Just Nz'ed the calculated fields with defaults to the Inv xtab.
Thanks very much for your help, this'll work just fine.

Jerry

Jerry said:
Hi Gary,

Thank you! That's just what I needed for the calculations. The only
problem is that the intersections where there are no values in the OD
crosstab are blank. Do you know how to make the remainder show up in every
cell that has a value in the Inv crosstab (whether there is anything in OD
to be deducted from it or not)?

The reason I'd like to do it in a query rather than updating the inventory
table is that the inventory table is actually oriented by variety rather
than color. Colors are stored in a Colors table, referenced by a Varieties
table, which is referenced by a VarietyID stored in the inventory table.
The inventory table has an entry for each variety and size that came from a
particular supplier, and may have entries for the same variety/size from
more than one supplier. The point of all this is that in order to update
the inventory quantities when an order is taken would require that we look
through several pages of inventory to find an entry with a remaining
quantity available to fill the request. The buyers don't care about
variety, only size and color.

All I really need here is to make sure we don't overbook certain
sizes/colors. I'd like to use this directly on the customer orders form so
the person taking orders can always see at a glance how many units of each
size/color are available, which is why I need to show quantities in all
cells.

Thanks again,

Jerry

Gary Walter said:
Hi Jerry,

Without seeing your data and what
you really want do (for example, I would
think you would instead want to just update
Inventory to reflect Orders sent), this might be
how I would create your query.

First, I might use Nz function in TRANSFORM
clause of your crosstabs to change Null to 0

TRANSFORM Nz(Sum([Inventory Query].Pots),0) AS SumOfPots

TRANSFORM Nz(Sum([Order Details].Quantity),0) AS SumOfQuantity

Then I believe you should be able to bring
both xtabs into a query, join on Size, then
perform subtractions in calculated fields.


Click on "Create Query in Design View"

In the Show Table dialog box,
click on one of your xtabs,
click Add,
click on other xtab,
click on Add again,
and then click Close.

You should now show both xtabs
in the query designer.

Right-mouse click on the "Inventory" xtab
and choose Properties.
In the Alias row, type in
Inv
then close the Properties dialog box.

Right-mouse click on the OrderDetail xtab
and choose Properties.
In the Alias row, type in
OD
then close the Properties dialog box.

I believe you want to join them
on the Size fields.

Click and hold down on left table's
Size field
and "drag and drop"
over on right table's Size field.

You should now have a (join) line
connecting the 2 xtabs going
from Inv.Size to OD.Size fields.

Drag and Drop "Size" field from
Inv table down in to field row
of first column of grid.

All that's left is to enter your
calculated fields.

In an empty field row, type in

NewMarbleInv: Inv.Marble - OD.Marble

In another empty field row, type in

NewNoveltyInv: Inv.Novelty - OD.Novelty

Just do the same to create the other
calculated fields.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
-------------------
"Jerry" wrote:
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)?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top