Report Listing Criteria

[dmkennard2]

Hi,
I am trying to build a report from a query.
In it i have Customer, Product Code and a Quantity. I would only like to show products that have a Quantity value Greater than 100 and Lower than -100 by customer.
This i can do in the query OK.

My problem is that i need to have a grand total of all the products, by customer in the table and not just those values selected in the query above.

Is this possible?
Thanks in advance

Dazz

 

[Knicks]

Why don't you use a Total query or you can use a regular select query and then perform the grouping by customer in the report.

On the criteria line you would want

>100 or <-100

 

[dmkennard2]

If i use the criteria then i only get the records in that criteria. I need the total for all records but on the report only show those that are >100 or <-100.

So, i need the report to show total QTY for all records by customer but only show the records for the criteria above.

Report:

Item 1 = 1000
Item 3 = -100

Total for above = 900
Subtotal = 990 (Item 2 = 90 and is not shown)
Difference = 90

Dazz

 

[Knicks]

You could stick with the query I mentioned on your report.

Then you could make another query that lists everything and totals it and reference it on your report using =Dlookup("[totalofquantity]","qryEverything") for an unbound text field.

qryeverything would be the name of the new query you are referencing, when doing the total on a grouping query run it and you'll see the name Access gives it, or you can force the actual name by putting "totalofquantity:" prior to the field your using as a total

 

[Knicks]

You could also do something like this, lets say you show everything from the query and you just want to handle this at the report level.

Would it work for you if simply some special formatting was applied to the lines that meet a criteria.

You can use the On Format Event, probably for the detail (possibly for the group footer depending upon if its for a row or for a total of rows)

If [quantity] >= 100 And [quantity] < -100 Then


Me.Detail.BackColor = 13434828

Else

Me.Detail.BackColor = 255
End If

 

[dmkennard2]

I must be doing something wrong.
I have created a seperate query and placed the code in an unbound text box, but i now only get a count of the the records shown on the report.

The second idea would not work as i could have 100 records to total but only require to show 3 that meet the criteria.

Thanks for this, its much appreciated.

Dazz

 

[Knicks]

Did you use Dlookup to reference a Totals query?.....Or you can use DSum to reference a select query.

The syntax should be something like

=Dlookup("[fieldtotalled]","qryAllrecords")
or

=Dsum("[quantity]","qryAllrecords")

And yes that would be placed in a text box and it would reference a query you made.