report count function and start recordset with highest number

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have a report where at the footer I have: =Count([WIMS_SUB_VEND])

Is there away to have the report start with the highest number of
[WIMS_SUB_VEND] first like

item 1 has 108 items
item 2 has 104 items
item 3 has 68 items
etc

Ryan
 
Hi Ryan

Add a calculated field to your report query like this:

ItemCount: DCount( "WIMS_SUB_VEND", "YourTable",
"[FieldYouAreGroupingBy]=" & [FieldYouAreGroupingBy] )

Then add the field ItemCount to your report's sorting/grouping list *before*
the field that has the group header/footer and with a sort order
"Descending".
 
When you say "[FieldYouAreGroupingBy] what does this mean?

Yourtable is the current table in which it pulls, so it'll be
Qrywimssum (assuming this is the table in the query where the new
column would pull of of right?)


ItemCount: DCount( "WIMS_SUB_VEND", Qrywimssum ,
"[FieldYouAreGroupingBy]=" & [FieldYouAreGroupingBy] )

here is current SQL if that'll help.

SELECT Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM,
Qrywimssum.WIMS_SUB_VEND, Qrywimssum.SUB_VEND_DESC,
Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
FROM Qrywimssum
GROUP BY Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM,
Qrywimssum.WIMS_SUB_VEND, Qrywimssum.SUB_VEND_DESC,
Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
HAVING (((Qrywimssum.VEND_NUM)=[Vendor Number]) AND
((Qrywimssum.WIMS_SUB_VEND)>"1"))
ORDER BY Qrywimssum.WIMS_SUB_VEND DESC;





Hi Ryan

Add a calculated field to your report query like this:

ItemCount: DCount( "WIMS_SUB_VEND", "YourTable",
"[FieldYouAreGroupingBy]=" & [FieldYouAreGroupingBy] )

Then add the field ItemCount to your report's sorting/grouping list *before*
the field that has the group header/footer and with a sort order
"Descending".
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I have a report where at the footer I have: =Count([WIMS_SUB_VEND])
Click to expand...
Is there away to have the report start with the highest number of
[WIMS_SUB_VEND] first like
Click to expand...
item 1 has 108 items
item 2 has 104 items
item 3 has 68 items
etc
Click to expand...
Ryan
Click to expand...
Click to expand...
 
Hi Ryan

You said (or at least implied) that you have your report grouped on a
certain field, and that this group has a group footer in which you display
the number of items in the group.

I understand you want the group with the largest number of items to appear
first, and so on to the smallest number.

To do this, you need to add a field to your query - it could be a subquery
or a DCount function, which contains the number of items in the group. Then
you can sort (descending) on that field in your report's sorting and
grouping list.

For example, your query recordset could look like this:

[GroupField] [ItemCount] [Other data fields...]
Group1 2 blah blah 1
Group1 2 blah blah 2
Group2 1 blah blah 3
Group3 4 blah blah 4
Group3 4 blah blah 5
Group3 4 blah blah 6
Group3 4 blah blah 7
Group4 2 blah blah 8
Group4 2 blah blah 9

Your report's sorting and grouping list would look like this:

ItemCount Descending (no header or footer)
GroupField Ascending (header and footer)

And your report would look like this:

Group 3
blah blah 4
blah blah 5
blah blah 6
blah blah 7
Number of items: 4

Group 1
blah blah 1
blah blah 2
Number of items: 2

Group 4
blah blah 8
blah blah 9
Number of items: 2

Group 2
blah blah 3
Number of items: 1

If that's not what you want, then I'm sorry but I've missed the boat
somewhere. Can you please explain a bit more.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand



When you say "[FieldYouAreGroupingBy] what does this mean?

Yourtable is the current table in which it pulls, so it'll be
Qrywimssum (assuming this is the table in the query where the new
column would pull of of right?)


ItemCount: DCount( "WIMS_SUB_VEND", Qrywimssum ,
"[FieldYouAreGroupingBy]=" & [FieldYouAreGroupingBy] )

here is current SQL if that'll help.

SELECT Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM,
Qrywimssum.WIMS_SUB_VEND, Qrywimssum.SUB_VEND_DESC,
Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
FROM Qrywimssum
GROUP BY Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM,
Qrywimssum.WIMS_SUB_VEND, Qrywimssum.SUB_VEND_DESC,
Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
HAVING (((Qrywimssum.VEND_NUM)=[Vendor Number]) AND
((Qrywimssum.WIMS_SUB_VEND)>"1"))
ORDER BY Qrywimssum.WIMS_SUB_VEND DESC;





Hi Ryan

Add a calculated field to your report query like this:

ItemCount: DCount( "WIMS_SUB_VEND", "YourTable",
"[FieldYouAreGroupingBy]=" & [FieldYouAreGroupingBy] )

Then add the field ItemCount to your report's sorting/grouping list
*before*
the field that has the group header/footer and with a sort order
"Descending".
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


I have a report where at the footer I have: =Count([WIMS_SUB_VEND])
Click to expand...
Is there away to have the report start with the highest number of
[WIMS_SUB_VEND] first like
Click to expand...
item 1 has 108 items
item 2 has 104 items
item 3 has 68 items
etc
Click to expand...
Ryan
Click to expand...
Click to expand...
Click to expand...
 
Hi Gram
Thanks for your post, but how can I use Dcount funtion to make the qery result like this:

[GroupField] [ItemCount] [Other data ields...]
Group1 2 blah blah 1
Group1 2 blah blah 2
Group2 1 blah blah 3
Group3 4 blah blah 4
Group3 4 blah blah 5
Group3 4 blah blah 6
Group3 4 blah blah 7
Group4 2 blah blah 8
Group4 2 blah blah 9
 
Hi Kenny

ItemCount: DCount( '*', 'YourTable', '[GroupField]=' & [GroupField] )

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

in message news:[email protected]...
 
Back
Top