(Access) Inclusive range query results from non-inclusive data sets

[krinid]

Is there a simple way to produce the following output?

QUERY RESULT
Type Value
---- -----
A 1
B 5
C
D -3
E
F 10

(ie: includes rows which don't exist in the table it's getting the data from)

DATA SET (table)
Type Value
---- -----
A 1
B 5
D -3
F 10



I'm currently using a fairly roundabout way to achieve this and would like to simply it if possible.

 

[tviman]

Create a temporary table of A,B,C,D,E,F and join it with the existing table. That's about the easiest way to get it done. The problem is that you can't quesry a table and get results that aren't contained in the table.

There's always a better way. The fun is trying to find it!

 

[krinid]

That's the 'fairly roundabout' way I'm currently using. My actual system is more complex than above, and contains a few tables and lots of fields which need to be displayed as mentioned above (inclusively, even if the values aren't in the table). So what I've done is created a 'master index' containing all the possible combinations. Problem is, there are about 650,000 possible combinations for a given 1 month period of data, which takes a lot of time to process, especially when processing data for 2 months or more.

Is there no way to simplify this? Any ideas?