Query working fine... trying to use it as a Subquery

[BrockWade]

I have a query that is working correctly, returning the correct number of records and I checked the total dollar amount and all looks perfect.. that query is pulling data from two tables and giving me a total of $254,998 out of $306,267:

SELECT DISTINCT tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount
FROM tmpBankConcatenate LEFT JOIN tmpBankDebitConcatenate ON tmpBankConcatenate.Amount = tmpBankDebitConcatenate.Amount
GROUP BY tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount
ORDER BY tmpBankDebitConcatenate.Amount DESC;



I was hoping this query using that as a subquery would yield the difference being $51,269, but I've done something wrong and the new query is returning $306,267:

SELECT tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount
FROM tmpBankDebitConcatenate
WHERE (((Exists (SELECT qryAutoMatchChangeOrders.Store, qryAutoMatchChangeOrders.Amount
FROM qryAutoMatchChangeOrders
WHERE ((tmpBankDebitConcatenate.Store <> qryAutoMatchChangeOrders.Store) AND
(tmpBankDebitConcatenate.Amount <> qryAutoMatchChangeOrders.Amount))))=False))
ORDER BY tmpBankDebitConcatenate.Amount DESC;

Thanks for any clues!

[ItsMe]

How did you create that SQL and where in your db did you place it? I suggest you use the query builder and save your subquery as a query object.

With that, I would build a second query and bring your other query into the design to create the join.

[BrockWade]

I saved the Subquery as a Query Object and it is working fine bringing back only those records needed in the subquery (the $254,998 out of $306,267):

SELECT DISTINCT tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount
FROM tmpBankConcatenate LEFT JOIN tmpBankDebitConcatenate ON tmpBankConcatenate.Amount = tmpBankDebitConcatenate.Amount
GROUP BY tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount
ORDER BY tmpBankDebitConcatenate.Amount DESC;

I can write my base query which gives me the entire $306,267:

SELECT tmpBankDebitConcatenate.Store, tmpBankDebitConcatenate.Amount
FROM tmpBankDebitConcatenate
ORDER BY tmpBankDebitConcatenate.Amount DESC;

However

... I went into design view to arrange the objects to produce the final needed query that will yield a query that gives me the difference, but I am at a loss at that point as to where in that simple query to fill in the "Criteria" that excludes the results of the first query.

[ItsMe]

If you are looking to exclude results, try the Unmatched Query Wizard and consider the two query objects you already created as the Wizard steps you through the process. Take a look at the SQL when you are finished. The key is to use Is Null on the Joined field.

[BrockWade]

I think the biggest challenge is that I'm not sure which field to use as a null because neither will have a null.... the base query which gives me the $306,267 is comprised of this simplified sample set of data (I'm matching stores and thier amounts to the same in one table "tmpBankConcatenate" for example:

Store / Amount
1 / 20
1 / 20
1 / 20

In this case my base query is successfully matching two of the rows producing a total of $40....
my Unmatched query needs to tell me that I have one row that got unmatched and yields a $20 as an unmatched

[ItsMe]

It is not that there is a field that is null in a given record. It is that the join does not find a match.

You are calculating $254,998. Whatever field is causing you to only calculate the records that generate this sum is probably the field. Try the wizard again without thinking Null. The wizard will create the criteria for you.

Ultimately, I could be wrong and this is not what you need. Give it another try and if you need to upload the DB. I will try and take a look.