Query with WHERE clause on a UNION query results in a table scan without using the indexes

An MS Access query with a WHERE clause on a UNION query results in a table scan without using the indexes.

Example: There is a table like this:

CREATE TABLE tblFigures
(
    EntryDate DATETIME,
    AmountType NUMBER,
    Amount DOUBLE
);

CREATE INDEX idxAmountType ON tblFigures (AmountType)

If you run the following query and view the query plan in JetShowPlan's showplan.out file, you will see that the index is being used:

SELECT * FROM tblFigures
WHERE AmountType = 1;

If you now run the following UNION query, the query plan shows that a table scan is being performed instead of using the index. I suspect this is because the result set of a UNION query exists only virtually in memory and not in the database. Therefore, there are no indexes on the result set. (Maybe I'm wrong!?)

qryFiguresUnion looks like this:

SELECT * FROM tblFigures
UNION ALL
SELECT * FROM tblFigures

Run qryFiguresUnion with the WHERE clause:

SELECT * FROM qryFiguresUnion
WHERE AmountType = 1;

This poses a major performance problem for my application. For example, there are three amount types: 1, 2, and 3. In the subsequent calculation, the user has the option of performing the calculations based on one of the types or based on the sum of all three types. Therefore, I initially tried writing a UNION query based on tblFigures and a SELECT of the sums of the three types for each EntryDate, and using the UNION query as the record source for the subsequent calculations. This works, but is very, very slow due to the table scanning.

Another idea would be to calculate the sums and insert them into tblFigures, for example, with the amount type 4, and then use tblFigures as the data source for further calculations. I think this would be fast, since the JET engine uses the index. However, I'm not very satisfied with this solution because the data -> sum of the amount types is redundant.

Does anyone have a better idea?

Thanks in advance!