Help needed with query returning rows?

[rashar via AccessMonster.com]

Hello,

I have a table with a column called Quantity. If there is a Quantity >= 30, I
need to place 30 on one line, then the remainder on the next row(s).

For example,

A row holds the quantity 40. I need a query that will take the 40, and place
30 on one row then 10 on the next.

Example 2.

A row holds quantity of 80. So when the query runs the following rows would
be, 30, 30, 20.

Table1
Item Quantity
A 80

Table1
Item Quantity
A 30
A 30
A 20
Is this possible to accomplish?

Thanks in advance.

 

[KARL DEWEY]

You did not say how high a number you needed to handle. These two queries
will work for quanities of 150 or less --
Rows_of_30_1 --
SELECT Table1.Item, IIf([Quanity]>30,30,[Quanity]) AS QTY1,
IIf([Quanity]>60,30,[Quanity]-30) AS QTY2, IIf([Quanity]>90,30,[Quanity]-60)
AS QTY3, IIf([Quanity]>120,30,[Quanity]-90) AS QTY4,
IIf([Quanity]>150,30,[Quanity]-120) AS QTY5
FROM Table1;

SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY1 AS Quanity, 1 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY1)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY2, 2 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY2)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY3, 3 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY3)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY4, 4 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY4)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY5, 5 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY5)>0))
ORDER BY Item, [Row];

 

[John Spencer]

One way would be to use queries like the following,


SELECT ITEM, IIF(Quantity<=30,Quantity,30) as FirstGroup
FROM SOMETABLE
UNION ALL
SELECT ITEM, IIF(Quantity>30 and Quantity <=60,Quantity-30,30)
FROM SomeTable
WHERE Quantity > 30
UNION ALL
SELECT ITEM, IIF(Quantity>60 and Quantity <=90,Quantity-60,30)
FROM SomeTable
WHERE Quantity > 60
UNION ALL
SELECT ITEM, IIF(Quantity>90 and Quantity <=120,Quantity-90,30)
FROM SomeTable
WHERE Quantity > 90
....

Another way MIGHT be to use another table with Numbers from 0 to N
(where N is the maximum number of groups of 30) and a query that might
look something like this UNTESTED query assuming the max number you are
interested in is a quantity of 3000.

SELECT Item, IIF(Quantity\30 = CCur(Quantity/30),30,Quantity Mod 30)
FROM SomeTable, GroupNumbers
WHERE GroupNumbers.Counter <= CCur(Quantity/30)
AND GroupNumbers.Counter <=10


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[rashar via AccessMonster.com]

Thanks for the solutiosn guys...

I did not specify how high a number because any amount of number could be
entered as a quanity. Would that be a problem to these solutions?

You did not say how high a number you needed to handle. These two queries
will work for quanities of 150 or less --
Rows_of_30_1 --
SELECT Table1.Item, IIf([Quanity]>30,30,[Quanity]) AS QTY1,
IIf([Quanity]>60,30,[Quanity]-30) AS QTY2, IIf([Quanity]>90,30,[Quanity]-60)
AS QTY3, IIf([Quanity]>120,30,[Quanity]-90) AS QTY4,
IIf([Quanity]>150,30,[Quanity]-120) AS QTY5
FROM Table1;

SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY1 AS Quanity, 1 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY1)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY2, 2 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY2)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY3, 3 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY3)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY4, 4 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY4)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY5, 5 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY5)>0))
ORDER BY Item, [Row];

Hello,

[quoted text clipped - 23 lines]

Thanks in advance.

 

[John Spencer MVP]

Yes, the UNION query solution will be a problem if the number gets really
high. Eventually you will run into the limit on the number of fields that can
be involved in a query or the complexity of the query.

The second solution that I proposed (if it works at all) will not that limit.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for the solutiosn guys...

I did not specify how high a number because any amount of number could be
entered as a quanity. Would that be a problem to these solutions?

You did not say how high a number you needed to handle. These two queries
will work for quanities of 150 or less --
Rows_of_30_1 --
SELECT Table1.Item, IIf([Quanity]>30,30,[Quanity]) AS QTY1,
IIf([Quanity]>60,30,[Quanity]-30) AS QTY2, IIf([Quanity]>90,30,[Quanity]-60)
AS QTY3, IIf([Quanity]>120,30,[Quanity]-90) AS QTY4,
IIf([Quanity]>150,30,[Quanity]-120) AS QTY5
FROM Table1;
SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY1 AS Quanity, 1 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY1)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY2, 2 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY2)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY3, 3 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY3)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY4, 4 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY4)>0))
UNION ALL SELECT Rows_of_30_1.Item, Rows_of_30_1.QTY5, 5 AS Row
FROM Rows_of_30_1
WHERE (((Rows_of_30_1.QTY5)>0))
ORDER BY Item, [Row];

Hello,

[quoted text clipped - 23 lines]

Thanks in advance.

 

[rashar via AccessMonster.com]

Thanks for the reply... Almost there but can't quite get the right info to be
displayed.

Here is the query that I am working with....

SELECT DISTINCTROW tblProducts1.Cust_Number, tblProducts1.Quantity_Last, IIf(
[tblProducts1].[Quantity]\30=CCur([tblProducts1].[Quantity]/30),[tblProducts1]
[Quantity],[tblProducts1].[Quantity] Mod 30) AS Quantity, tblProducts1.
Part_Number, IIf([forms]![frmInitialStart]![txtLanguage]="English",[Product_E]
,[Product_F]) AS Description, Products_Pricing.Promo, Products_Pricing.Retail,
Products_Pricing.Category_1, Products_Pricing.Category_2, Products_Pricing.
Category_3, tblProducts1.Total, tblProducts1.ID, Products_Pricing.Dealer_Net
FROM Products_Pricing INNER JOIN tblProducts1 ON Products_Pricing.Part_Number
= tblProducts1.Part_Number
WHERE (((tblProducts1.Cust_Number)=[forms]![frm_main]![txtcust_number]) AND (
(tblProducts1.Quantity)>0)) OR (((tblProducts1.Cust_Number)=[forms]![frm_main]
![txtcust_number]) AND ((tblProducts1.Quantity_Last)>0))ORDER BY tblProducts1.
ID;

If I have a Part# of ABC and its Quantity is 38, with the above query the
results will display,
ABC 8.

How can I take the above query so that it will read,
ABC 30
ABC 8

Also, if there are no values then the display will read #Error. I tried to
put the false condition after Mod 30, but when I go to execute the query I
receive a message stating that there is a Syntax error..comma in expression.

Thanks in advance.

One way would be to use queries like the following,

SELECT ITEM, IIF(Quantity<=30,Quantity,30) as FirstGroup
FROM SOMETABLE
UNION ALL
SELECT ITEM, IIF(Quantity>30 and Quantity <=60,Quantity-30,30)
FROM SomeTable
WHERE Quantity > 30
UNION ALL
SELECT ITEM, IIF(Quantity>60 and Quantity <=90,Quantity-60,30)
FROM SomeTable
WHERE Quantity > 60
UNION ALL
SELECT ITEM, IIF(Quantity>90 and Quantity <=120,Quantity-90,30)
FROM SomeTable
WHERE Quantity > 90
...

Another way MIGHT be to use another table with Numbers from 0 to N
(where N is the maximum number of groups of 30) and a query that might
look something like this UNTESTED query assuming the max number you are
interested in is a quantity of 3000.

SELECT Item, IIF(Quantity\30 = CCur(Quantity/30),30,Quantity Mod 30)
FROM SomeTable, GroupNumbers
WHERE GroupNumbers.Counter <= CCur(Quantity/30)
AND GroupNumbers.Counter <=10

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,

[quoted text clipped - 23 lines]

Thanks in advance.

 

[John Spencer]

First of all I don't see the additional table in that query. You need
to have a table (tCounter) that consists of just one column (iCount) and
with records that have a value from 1 to 100 (or whatever your maximum
number of groups might be - maximum of quantity divided by 30 plus 1.

You ADD the table to your query, but do NOT join it to any of the other
tables. If you have records with NO quantity then you will have to use
a query to filter out those records or force quantity to zero in the
calculations using the NZ function.

Also, the expression i originally proposed seems to be incorrect. It
should probably be the following. You might have to force the number
type to integer to take care of floating point errors.


IIF (Quantity-(30*(iCount-1))>30,30,Quantity-(30*(iCount-1))

So the following MIGHT work for you.

SELECT tblProducts1.Cust_Number
, tblProducts1.Quantity_Last
, IIF (Quantity-(30*(iCount-1))>30,30,Quantity-(30*(iCount-1)) AS Qty
, tblProducts1.Part_Number
, IIf([forms]![frmInitialStart]![txtLanguage]="English"
, [Product_E],[Product_F]) AS Description
, Products_Pricing.Promo
, Products_Pricing.Retail
, Products_Pricing.Category_1
, Products_Pricing.Category_2
, Products_Pricing.Category_3
, tblProducts1.Total
, tblProducts1.ID
, Products_Pricing.Dealer_Net

FROM tCounter, (Products_Pricing INNER JOIN tblProducts1 ON
Products_Pricing.Part_Number = tblProducts1.Part_Number)
WHERE tCounter.iCount-1 <= tblProducts1.Quantity/30
AND tblProducts1.Cust_Number=[forms]![frm_main]![txtcust_number]
AND (Quantity>0 OR Quantity_Last>0)

ORDER BY tblProducts1.ID;



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for the reply... Almost there but can't quite get the right info to be
displayed.

Here is the query that I am working with....

SELECT DISTINCTROW tblProducts1.Cust_Number, tblProducts1.Quantity_Last, IIf(
[tblProducts1].[Quantity]\30=CCur([tblProducts1].[Quantity]/30),[tblProducts1]
.[Quantity],[tblProducts1].[Quantity] Mod 30) AS Quantity, tblProducts1.
Part_Number, IIf([forms]![frmInitialStart]![txtLanguage]="English",[Product_E]
,[Product_F]) AS Description, Products_Pricing.Promo, Products_Pricing.Retail,
Products_Pricing.Category_1, Products_Pricing.Category_2, Products_Pricing.
Category_3, tblProducts1.Total, tblProducts1.ID, Products_Pricing.Dealer_Net
FROM Products_Pricing INNER JOIN tblProducts1 ON Products_Pricing.Part_Number
= tblProducts1.Part_Number
WHERE (((tblProducts1.Cust_Number)=[forms]![frm_main]![txtcust_number]) AND (
(tblProducts1.Quantity)>0)) OR (((tblProducts1.Cust_Number)=[forms]![frm_main]
![txtcust_number]) AND ((tblProducts1.Quantity_Last)>0))ORDER BY tblProducts1.
ID;

If I have a Part# of ABC and its Quantity is 38, with the above query the
results will display,
ABC 8.

How can I take the above query so that it will read,
ABC 30
ABC 8

Also, if there are no values then the display will read #Error. I tried to
put the false condition after Mod 30, but when I go to execute the query I
receive a message stating that there is a Syntax error..comma in expression.

Thanks in advance.

One way would be to use queries like the following,

SELECT ITEM, IIF(Quantity<=30,Quantity,30) as FirstGroup
FROM SOMETABLE
UNION ALL
SELECT ITEM, IIF(Quantity>30 and Quantity <=60,Quantity-30,30)
FROM SomeTable
WHERE Quantity > 30
UNION ALL
SELECT ITEM, IIF(Quantity>60 and Quantity <=90,Quantity-60,30)
FROM SomeTable
WHERE Quantity > 60
UNION ALL
SELECT ITEM, IIF(Quantity>90 and Quantity <=120,Quantity-90,30)
FROM SomeTable
WHERE Quantity > 90
...

Another way MIGHT be to use another table with Numbers from 0 to N
(where N is the maximum number of groups of 30) and a query that might
look something like this UNTESTED query assuming the max number you are
interested in is a quantity of 3000.

SELECT Item, IIF(Quantity\30 = CCur(Quantity/30),30,Quantity Mod 30)
FROM SomeTable, GroupNumbers
WHERE GroupNumbers.Counter <= CCur(Quantity/30)
AND GroupNumbers.Counter <=10

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,

[quoted text clipped - 23 lines]

Thanks in advance.

 

[rashar via AccessMonster.com]

Perfect!!! Thanks for your time and assistance with my solution John.

It works excatly how I need it to.

First of all I don't see the additional table in that query. You need
to have a table (tCounter) that consists of just one column (iCount) and
with records that have a value from 1 to 100 (or whatever your maximum
number of groups might be - maximum of quantity divided by 30 plus 1.

You ADD the table to your query, but do NOT join it to any of the other
tables. If you have records with NO quantity then you will have to use
a query to filter out those records or force quantity to zero in the
calculations using the NZ function.

Also, the expression i originally proposed seems to be incorrect. It
should probably be the following. You might have to force the number
type to integer to take care of floating point errors.

IIF (Quantity-(30*(iCount-1))>30,30,Quantity-(30*(iCount-1))

So the following MIGHT work for you.

SELECT tblProducts1.Cust_Number
, tblProducts1.Quantity_Last
, IIF (Quantity-(30*(iCount-1))>30,30,Quantity-(30*(iCount-1)) AS Qty
, tblProducts1.Part_Number
, IIf([forms]![frmInitialStart]![txtLanguage]="English"
, [Product_E],[Product_F]) AS Description
, Products_Pricing.Promo
, Products_Pricing.Retail
, Products_Pricing.Category_1
, Products_Pricing.Category_2
, Products_Pricing.Category_3
, tblProducts1.Total
, tblProducts1.ID
, Products_Pricing.Dealer_Net

FROM tCounter, (Products_Pricing INNER JOIN tblProducts1 ON
Products_Pricing.Part_Number = tblProducts1.Part_Number)
WHERE tCounter.iCount-1 <= tblProducts1.Quantity/30
AND tblProducts1.Cust_Number=[forms]![frm_main]![txtcust_number]
AND (Quantity>0 OR Quantity_Last>0)

ORDER BY tblProducts1.ID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for the reply... Almost there but can't quite get the right info to be
displayed.

[quoted text clipped - 67 lines]

[quoted text clipped - 23 lines]
Thanks in advance.