Grouping in Reports

[pilotkk22]

I was wondering if anyone knows if it is possible to write a report in
access that breaks on a counter, and resets the counter after it does.
I need to display a list of inventory, but it needs to be displayed in
groups of 20 items.



For example



Hammer 8

Nails 10

Wood 25



In report should be displayed as

Hammer 8

Nails 10

Wood 2

---------------Break------no page break, just start again

Wood 20

---------------Break------no page break, just start again

Wood 3



Or if this is not even possible with access that would be a great
help as well.

 

[Duane Hookom]

You could do this with combinations of cartesian, select, and totals
queries.
Assuming:
tblInventory (your table)
Item
Qty
tblNums (records with values 0 - 100)
Num

Create a query (qcarInventory)
SELECT tblInventory.Item, tblNums.Num
FROM tblInventory, tblNums
WHERE (((tblNums.Num) Between 1 And [Qty]))
ORDER BY tblInventory.Item, tblNums.Num;

Create another query with a subquery (qselRankInventory):
SELECT qcarInventory.Item, qcarInventory.Num,
((Select Count(*)
From qcarInventory q
Where q.Item & Format(q.Num,"000") <=
qcarInventory.Item & Format(qcarInventory.Num,"000"))-1)\20 AS Rank
FROM qcarInventory
ORDER BY qcarInventory.Item, qcarInventory.Num;

Create another query (qtotInventory):
SELECT Item, Rank, Count(Num) AS CountOfNum
FROM qselRankInventory
GROUP BY Item, Rank;

Build your report from qtotInventory.

 

[Duane Hookom]

I don't know if this is still an open issue but just try my suggestion. If
there is another method (simpler) then use it.

--
Duane Hookom
MS Access MVP

Originally posted by Duane Hookom

You could do this with combinations of cartesian, select, and totals


tblInventory (your table)


tblNums (records with values 0 - 100)


Create a query (qcarInventory)

SELECT tblInventory.Item, tblNums.Num

FROM tblInventory, tblNums

WHERE (((tblNums.Num) Between 1 And [Qty]))

ORDER BY tblInventory.Item, tblNums.Num;

Create another query with a subquery (qselRankInventory):

SELECT qcarInventory.Item, qcarInventory.Num,

((Select Count(*)

From qcarInventory q

Where q.Item & Format(q.Num,"000") <=

qcarInventory.Item & Format(qcarInventory.Num,"000"))-1)\20 AS
Rank

FROM qcarInventory

ORDER BY qcarInventory.Item, qcarInventory.Num;

Create another query (qtotInventory):

SELECT Item, Rank, Count(Num) AS CountOfNum

FROM qselRankInventory

GROUP BY Item, Rank;

Build your report from qtotInventory.


Duane Hookom

MS Access MVP

news:[email protected]"]bforums.com[/url]...

I was wondering if anyone knows if it is possible to write a report in
access that breaks on a counter, and resets the counter after it does.

I need to display a list of inventory, but it needs to be displayed in
groups of 20 items.
For example
Hammer 8
Nails 10
Wood 25
In report should be displayed as
Hammer 8
Nails 10
Wood 2
---------------Break------no page break, just start again
Wood 20
---------------Break------no page break, just start again
Wood 3
Or if this is not even possible with access that would be a great

help as well.

http://dbforums.com/http://dbforums.com

Dave I am not follwoing you, why dod I need the extra table of numbers?
Ill will try to explain a little better down below.



Say I want to make a report for a baking list, that needs to group
the bakeing items into groups of 20, cause thats all that can be
baked at one time.



So I have a table of items and there quantity for the day

Brownies - 5

Cakes - 8

Cupcakes- 28

Cookies - 19



I want the report to show

START REPORT

----1st BAKE LIST

5 - Brownies

8 - Cakes

7 - Cupcake

----2ND BAKE LIST

20 -Cupcakes

----3rd BAKE LIST

1 - Cupcakes

19 - Cookies

END REPORT



Please let me know if this is any better. Thanks?

Kevin