Using macro to filter table without the table being in view mode.

[AshikHusein]

I am very new to Access and now am facing a problem with Macros. I have created a report from a query. The query is based on another query and this other query is based on the main table. I want to run the report based on filtered data from the main table. However, I dont want the the main table to open when I run the filter operation through the macro. When I try to open the report directly from the macro and put in the condition that [tbl_xxx]![Centre] = "Toronto" I get an error message. So the questions are

1) If I want to filter data in the main table how can I do it without opening the table or setting it so that the main table should not show although it is open, while using a macro.

2) If I open the report directly from the macro, how could I filter the data in the main report.

The query from which the report is drawn does not include the field on which the data is filtered.

Would appreciate help on this. Thanks.

 

[FoFa]

Well, you could join the mian table to the final query on a key, than use the column in your selection, or I would change the other queries to include that column and filter at the end. You do not have to include that column in your report.

 

[AshikHusein]

With the database that I am working, there are no key fields. There are certain fields which may be coded as either a "Yes" or left blank. What I am trying to do is add all the "Yes"s for the different fields by making queries and compile it into a report. There are some reports which require me to add the "Yes"s for only some entries of another field (example only for "Toronto") but when I run the query on the filtered table, it gives me the results for everything (Toronto and others). Thanks and your further input will be appreciated.

 

[FoFa]

Sounds like you need to bring those columns forward through the queries if you can't join to the main table.

 

[AshikHusein]

I Have done that done that. The question basically is that if I filter the table and run the query pulled from the filtered table will the Query only work for the filtered records. From what I can see it seems that the query disregards any filters on the table. Is there any way for the query to work on the filtered records of the table only? Thanks.

 

[FoFa]

MainTbl ---> Query1 ---> Query/Report
I assume you are basing the query of the report on the query that is filtering the main table (Or I assume wrong on what you say).

MainTbl ---> Query1
MainTbl ---> Query/Report

Would not work as it would bypass the filter query.

You can also use the query to pull the criteria from a form

MainTbl ---> Form with filter
MainTbl ---> Query uses Form for criteria ---> Query/Report

So how are you doing it?

 

[AshikHusein]

I am using the following:

Main table (filtering it with a macro)
From filtered Main table------>Query1
From Query1------->Query2
From query2------->Final Report

So you are saying the above doesnt work?

 

[FoFa]

Have you tried putting the filter on the report instead?

 

[AshikHusein]

I cannot do that because the last query from which the report is pulled does not have the centre field. The reports are also a bit complex because they involve calculations of percentages for rows headings and column headings for two sub groups within each. For some reports I had to cross tab query.

However I did try adding in the Centre fields to the reports by adding the "Centre" field to the underlying queries. The results were frightening!!. The final reports started subdividing the groups and thee was a big mess!! I have about 8 reports with sub reports in each of them.

What I am now doing is that I am using delete queries. So for different presses of form buttons it is going to create a table and delete records for "Centres" not required. The reports can then be pulled up with relative ease. I was experimenting with this the whole day yesterday.

If you do find a way in which queries and reports use only filtered datafrom the underlying table I would like to know. It would also make things more simple in Access. Thanks for your insight into the matter.