How to filter a report using subtotals 1

[ThatNewGuy]

Hello,

I'm working with a database that is designed to track sales information of specific part numbers, when the units were sold, how much each specific order was invoiced for, etc etc.

I'm having problems filtering one report in this project. I'm sure there is an easy solution, but I simply don't know what it is. My report is setup as follows: I have a Part # header followed by the detailed section which includes a date column, quantity, amount invoiced, etc. Then I have a Part # footer where I have sub-totals such as =sum([invoiced]). This all works great, but my final report is showing every part # and it's sub-total. Something like 174 pages. Obviously information overload and not much of a focused report. Anyway, how can I narrow the criteria of this report down to only show the part numbers with sub-totals that are a specific amount such as >$3,000. I've tried your basic =sum([invoiced])>3000 and other basic fixes but they don't seem to filter out the information that I'm looking for. I also experimented with adding a subtotal column into my query and that didn't exactly fix it either.

Any direction or help would be appreciated.

Thanks

 

[PHV]

What is the actual SQL code of the query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThatNewGuy]

This is plain jane:

SELECT PORTAL.DATE, PORTAL.[Part No], PORTAL.Quantity, PORTAL.REV, PORTAL.FM, PORTAL.[Price/Unit], PORTAL.Quoted, PORTAL.Invoiced
FROM PORTAL
WHERE (((PORTAL.DATE) Between #9/1/2006# And #7/10/2007#));


The above SQL from one of my queries probably won't help much because I was working with my subtotals in my report.

 

[lespaul]

how can I narrow the criteria of this report down to only show the part numbers with sub-totals that are a specific amount such as >$3,000

If the report record source is a query that contains the sub-totals you can add a HAVING clause:

SELECT ....
FROM ....
WHERE .....
GROUP BY .....
HAVING SUM(TOTAL) > 3000

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

What about this ?
SELECT P.DATE, P.[Part No], P.Quantity, P.REV, P.FM, P.[Price/Unit], P.Quoted, P.Invoiced
FROM PORTAL AS P INNER JOIN (
SELECT [Part No] FROM PORTAL WHERE [DATE] Between #9/1/2006# And #7/10/2007#
GROUP BY [Part No] HAVING Sum(Invoiced) > 3000
) AS T ON P.[Part No] = T.[Part No]
WHERE P.DATE Between #9/1/2006# And #7/10/2007#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThatNewGuy]

PHV you rock! I just ran a sample test and it works perfectly. Thanks for the help!

 

[dhookom]

PH deserves a star for this SQL. I would have suggested two separate queries to arrive at the solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]