Subtracting results from two different queries.

[Nevy]

Hi, here's the situation:

I have a query A, that gives me the a set of numbers.
I have a query B, that gives me another set of numbers.

Numbers in B are always equal or larger than those in A. (B >= A)

I want to create a new query C, that does the subtraction. (C = B-A)

Here's an example to explain what I want.
Let's say I have A=4,7 and B=6,9

Now, when I create the query, I get the following result:
A | B | C=B-A
4 | 6 | 2
4 | 9 | 5
7 | 6 | -1
7 | 9 | 2

What I want is this:
A | B | C=B-A
4 | 6 | 2
7 | 9 | 2

How do I do that?

Thanks.

 

[The_Doc_Man]

You have what looks like a cartesian product (a.k.a. permutation JOIN).

This occurs because of the way Access handles queries. No, on second thought, it is the way ANSI standard SQL queries are DEFINED. ANY ANSI-SQL-compliant product would have given you this result.

What has happened is that you have two data streams. (Query A and Query B). You have asked Access, using pretty much standard SQL rules, to perform a subtraction. But you didn't bind the records in any specific way. That is, if there IS a relationship between the two streams, you didn't state it.

THEREFORE, the definition of SQL requires it to compute every possible value for every potentially valid combination of records in the two streams.

If these were TABLES and you had somehow done a JOIN where you constrained the records that matched up to each other, you would have gotten fewer records. All you would have gotten would have been the permutations that matched your JOIN restrictions. (Like ... FROM [A] LEFT INNER JOIN ... ON [A]![Field1] = ![Field2] ... or something similar.)

In the absence of record-selection guidance, ANSI SQL (including Access) will give you permutations of all valid combinations of the tables you are combining. Which is why you include a WHERE clause or the ON clause. You need to constrain the combinations that will be returned.

OK, that's the theory... how do you do it? There is where I cannot go. I don't see enough visual cues in what you wrote to tell me how the queries are derived. And, in specific, how the two sets of values are related. But that is what you need to look for. In my [Field1], [Field2] example above, these two fields would be related to each other in some way so that you would know that a value in the [A] query somehow corresponds to a value in the query. And including those values would allow you to write the C query that you wanted.

 

[Nevy]

Thank you.
As you mentionned, all I needed was to bind two fields together.
Now it works.