If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Footer In Access
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 |
#2
|
|||
|
|||
Conditional Footer In Access
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "FindMyDates.com" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|