I have a client with an Access-based inventory system with very complicated pricing. The pricing is based on a query that performs a lot of calculations based on several tables. The pricing query is set up to work on all the records in inventory (about 50,000 items).
This query is used in pretty much every inventory report.
The problem I have is that even if a report has only a handful of items on it, the underlying pricing query seems to be calculating pricing for every item in the database meaning that every report is now becoming immensely slow.
As a test, I ran a report of all items for a vendor and I chose a vendor that only has 5 items. It was extremely slow. Then I deleted every item in the database except those 5 and the report was lightning fast.
In this example, the report is based on a parent query that pulls vendor and item data (including the pricing query) and the parent query has a record source that specifies just the one vendor. However, it appears that the pricing query is running on every item in the database and then subsequently being filtered based on the parent query.
How do I get Access to only run the child query (i.e. the pricing query) for the items that are on the report? I should add that the pricing query is a child query in dozens of other reports in the database so I'm hoping for a solution that isn't going to require a lot of coding dozens of times over.
Thanks in advance.