Unanswered: filtered report vs. specific data source
I've been charged with cleaning up & normalizing a sprawling Access database in preparation for upsizing to SQL Server. There will always be an Access front-end in charge of running the same report, once for each of 100+ employees. The current db performs this report by running the overall report, filtered by each employeeID.
I'm not sure how JET processes this filter - is it really crunching all the numbers and then just displaying the results for the filter criteria, or is it optimizing this process somehow? Would it be faster if the data source query only queried for that specific employee and handed the (much) smaller data set to the report? I guess the main question is: does JET optimize a filter so the dataset is minimized from the outset?
I believe in your situation the Jet Engine will return everything from the query and then the report will filter the results. It would be similar to opening a query to show all of the records and then filtering the results that were returned in the datasheet view. I can't imagine that for 100+ employees that a report like the one you are describing would be very slow. If it is there are things you can do to speed it up in Access. You can write the ID of the employee you want to a table. And then include that table in the query.