count() or =Abs(Sum(([Item] function

[AnhCVL via AccessMonster.com]

Hi all,

I initially having problem with counting the total records on my report and
was able to get it fix with research and help form the expert here. I am
currently use sql:

SELECT count(*) FROM RecordTable
WHERE Quantity Sold > 0

and I ran accross the method =Abs(Sum(([Item]="Ax") that posted by some
expert here and I wonder if I cuold extend the method to ad more arguements
to it as described below. let say I have a report with the following columm:

Item | Date Sold | Sold by | Price | Quantity Sold

let say I have an Item name "Ax", myself a saleman name "Mark", and I sold
total of "10" axes this week, is it possible to included all that on the text
box on the report footer using acess function? I've tried something like

=Abs(Sum(([Item]="Ax"), ([Sold by] = "Mark"), ([Quantity Sold]))

and does it work. any suggestion?


Thanks all.
Mark

 

[Duane Hookom]

I'm not sure how you define "this week"...
I would try:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") * [Quantity Sold])

However, I don't think it is ever a good idea to hard-code values into
expressions since your items and sold by will change. You should never be
required to change expressions when you change employees or inventory.

 

[AnhCVL via AccessMonster.com]

Hi Duane

I've created queries to run weekly report, the report will be manually update
so it was not an issue. I tried your suggestion and it show as "#error", I
came across Dcount() function and have tried:

=DCount("Status","Inventory Table","[Status] = 'Instock'")

This worked so far but I wanted to extend the arguments into something more
specific like:

=DCount("[Status] = 'Instock'","Inventory Table","[TName] = 'hammer'")

I tried this but it counted all the count from Tname colume that associate
with hammer regardless of the Status ( Instock/Sold). Any sugestion to make
this work will be deeply appreciated.

Mark

I'm not sure how you define "this week"...
I would try:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") * [Quantity Sold])

However, I don't think it is ever a good idea to hard-code values into
expressions since your items and sold by will change. You should never be
required to change expressions when you change employees or inventory.

Hi all,

[quoted text clipped - 21 lines]

Thanks all.
Mark

 

[Duane Hookom]

Where did you try my suggestion? Was it in a Report or Group Footer or
Header? If you tried it in a Page section, it will error. Also, the name of
the text box must not be the name of a field in the report's record source.

I would never use DCount() like this in a report.

--
Duane Hookom
Microsoft Access MVP

Hi Duane

I've created queries to run weekly report, the report will be manually update
so it was not an issue. I tried your suggestion and it show as "#error", I
came across Dcount() function and have tried:

=DCount("Status","Inventory Table","[Status] = 'Instock'")

This worked so far but I wanted to extend the arguments into something more
specific like:

=DCount("[Status] = 'Instock'","Inventory Table","[TName] = 'hammer'")

I tried this but it counted all the count from Tname colume that associate
with hammer regardless of the Status ( Instock/Sold). Any sugestion to make
this work will be deeply appreciated.

Mark

I'm not sure how you define "this week"...
I would try:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") * [Quantity Sold])

However, I don't think it is ever a good idea to hard-code values into
expressions since your items and sold by will change. You should never be
required to change expressions when you change employees or inventory.

Hi all,

[quoted text clipped - 21 lines]

Thanks all.
Mark

 

[AnhCVL via AccessMonster.com]

Hi Duane,

I placed it on the report footer.
For this line:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") * [Quantity Sold])

what is the * do? multiply the quantity sold? or join the [quantity sold] to
the criteria? I want to limit the display by Item>Sold by>Quantity. I don't
need to calculate the total quantity, I only need to count the total rows on
the report/table because each time the item scanned out, it create a new
record line on the inventory table and the quantity is 1, therefore, each row
has a given value of 1. Any suggestion?

Thanks
Mark

Where did you try my suggestion? Was it in a Report or Group Footer or
Header? If you tried it in a Page section, it will error. Also, the name of
the text box must not be the name of a field in the report's record source.

I would never use DCount() like this in a report.

Hi Duane

[quoted text clipped - 29 lines]

 

[Duane Hookom]

If all you want to do is count the records that meet the criterian then use:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") )
--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I placed it on the report footer.
For this line:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") * [Quantity Sold])

what is the * do? multiply the quantity sold? or join the [quantity sold] to
the criteria? I want to limit the display by Item>Sold by>Quantity. I don't
need to calculate the total quantity, I only need to count the total rows on
the report/table because each time the item scanned out, it create a new
record line on the inventory table and the quantity is 1, therefore, each row
has a given value of 1. Any suggestion?

Thanks
Mark

Where did you try my suggestion? Was it in a Report or Group Footer or
Header? If you tried it in a Page section, it will error. Also, the name of
the text box must not be the name of a field in the report's record source.

I would never use DCount() like this in a report.

Hi Duane

[quoted text clipped - 29 lines]

Thanks all.
Mark

 

[AnhCVL via AccessMonster.com]

Hi Duane

I did what you said, doesn't seem to work. when I run the report, it prompt
for criteria parameter "Sold by", I think I am going to give up on this, I'll
try to find another way to do it. Thanks for all the helps.

Mark

If all you want to do is count the records that meet the criterian then use:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") )

Hi Duane,

[quoted text clipped - 23 lines]

 

[Duane Hookom]

The [Sold by] comes directly from your first post with this expression:
=Abs(Sum(([Item]="Ax"), ([Sold by] = "Mark"), ([Quantity Sold]))
and Sold By also in the column list.

Apparently you don't have a field in your report record source named [Sold
By].

If you need a solution, you need to provide quality specifications and
information about your application.

--
Duane Hookom
Microsoft Access MVP

Hi Duane

I did what you said, doesn't seem to work. when I run the report, it prompt
for criteria parameter "Sold by", I think I am going to give up on this, I'll
try to find another way to do it. Thanks for all the helps.

Mark

If all you want to do is count the records that meet the criterian then use:
=Sum( Abs([Item]="Ax" AND [Sold by] = "Mark") )

Hi Duane,

[quoted text clipped - 23 lines]

Thanks all.
Mark