Conditional Footer In Access

[FindMyDates.com]

I have a table of about 500,000 records for which I need to find exceptions
in. The exception is a negative quantity in a footer3 of a report I have
designed. I have changed Footers 1 & 2 to not be visible (unless I need to
see the details of something - I can re-activate them). Footer3 accumulates
net quantities correctly. NOW I would like to only print this footer when
the quantity is negative. The entire line of the footer, that is. I have
done an =IIF() for the quantity field, and that works for the field - but can
I just do that for the entire line or something?

Also, when I try and export the report's results to Excel it only gives me
the field names from the tables and no summarized results - in fact no
records at all???

Thanks in advance!
Owner
FindMyDates.com

 

[Allen Browne]

The export to Excel may not include the subreports. In fact, in Access 2007,
you can't export the report to Excel at all.

What you need to do is to create a query that performs the calcuation to
identify the records needed for the report. The WHERE clause of the query
can then exclude those where the calcuation result is not negative.

This will probably involve creating queries that use other queries as input
"tables" (stacked queries) and/or subqueries (a query that uses a SELECT
query statement in one of its clauses.) These queries will probably involve
some grouping to get the totals (Totals queries.)

This example assumes a table of products, and a table of transactions, and
returns the products that have a negative quantity for this year:

SELECT ProductID, ProductName
FROM tblProduct
WHERE (SELECT Sum(tblTransaction.Quantity) AS SumOfQuantity
FROM tblTransaction
WHERE tblTransaction.ProductID = tblProduct.ProductID
AND tblTransaction.TransactionDate >= #1/1/2007#) < 0;

For more examples of subqueries, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...