Filter Question

[Antavas]

I have a report we review on a monthly basis. However, we do not review the
entire report only a certain portion. Is there a way to have Access
automatically cut down the report to the required percentage? So for instance:

50% of AB items
25% of BC items
etc........

 

[Allen Browne]

Can't think of an easy way to use a variable percentage like that.

You yave a list of categories, so you could add a percentage column to that
table to specify the desired percentage for each category. You could then
write some code to loop through the categories, executing an Append query
statement for each one, appending the primary key value of the desired
percentage to a temporary table. Then use that temporary table (inner joined
to your real table) as the source for the report.

This is just a mock up. You will have to create your own SQL statements:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Set db = dbEngine(0)(0)
strSql = "SELECT CategoryID, CategoryPercent FROM tblCategory WHERE
CategoryPercent Is Not Null;"
Set rs = db.OpenRecordset(strSql)
Do While Not rs.EOF
strSql = "INSERT INTO YourTempTable ( ID ) SELECT TOP " &
rs!CategoryPercent & " PERCENT tblSales.SalesID FROM tblSales INNER JOIN
tblItem ON tblSales.ItemID = tblItem.ItemID WHERE tblItem.CategoryID = " &
rs!CategoryID & ";"
db.Execute strSql, dbFailOnError
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db=Nothing