Inner Join question.

[Robeen]

Hi,



I have two queries.
Query1: shows all Debits for Companies by Date.
Query2: shows all Credits for Companies by Date.

I have created another query to show all the data for Debits [Query1] - with the following requirements:
If a Query1 Debit amount for a Company for a Date - has a matching Query2 Credit amount for the same Company for the same Date . . .
I don't want that Debit to show in the query.

This is what I did:
In Query Design, I pulled in both queries.
I joined them on CompanyName, EntryDate & TransactionAmount.
Dropped in all the fields from Query1 [Debits].
Went to SQL View.
And changed this:

SELECT Query1.[CompanyName], Query1.[EntryDate], Query1.[RoutingNumber], Query1.[TranCode], Query1.[TraceNum], Query1.[TransactionAmount]
FROM Query1 
INNER JOIN Query2 
ON (Query1.TranAmt = Query2.Total) 
AND (Query1.[EntryDate] = Query2.[EntryDate]) 
AND (Query1.[CompanyName] = Query2.[CompanyName]);

to this:

SELECT Query1.[CompanyName], Query1.[EntryDate], Query1.[RoutingNumber], Query1.[TranCode], Query1.[TraceNum], Query1.[TransactionAmount]
FROM Query1 
INNER JOIN Query2 
ON (Query1.TranAmt <> Query2.Total) 
AND (Query1.[EntryDate] = Query2.[EntryDate]) 
AND (Query1.[CompanyName] = Query2.[CompanyName]);

It 'APPEARS' to be giving me what I need - on a small sample of the data that I eyeballed.
Am I am doing something intrinsically wrong by putting that "<>" into an 'INNER JOIN' clause.
It's striking me as a bit of an oxymoron to have a '<>' in an Inner Join clause.
?

Is there a better/more correct way to do it?

I'd appreciate any help.

Thanks!!

Robeen

[June7]

Yes, that is wrong. A join clause by it's nature is linking two fields with equal values. What you need to do is apply filter criteria. Under TranAmt field on Criteria row, use: <>Query2.Total

[Robeen]

Thanks, June7. Appreciate your help.

[June7]

Oh, and probably should remove the part that joins on those two fields.

[Robeen]

Sorry June7 . . . it is not working totally as it should and that might be because of something I ommited to mention [though I don't know what yet].

I have taken a slightly different approach and now:
I have one Query showing all the Debits AND Credits and
I have another query showing all the Offset Amounts [which can be positive OR negative values].

Actually, these two queries [in my test environment] are simply 'Select * . . .' queries based on two tables that have the data.
I wanted to be able to study the data in each of the two tables using different sorts - so I started out with these two queries.

The goal is:
Show all rows from the 'Debits and Credits' Query/Table for which there is NOT a matching [Company, Date and Amount] row in the 'Offsets' Query/Table.


The problem is this:
If a Company has 13 Debits&Credits on a particular date - and also 13 matching Offset amounts [negative or positive] on the same day -
Access SHOWS 169 rows [13 x 13] - basically each possible combination - for all the matching rows.
It also displays [as I need it to] the rows that do not have matches in the Offsets Query.

Here's the SQL that is in my query:

SELECT QryDebitsCredits.Effective_Entry_Date, QryDebitsCredits.Company_Name, QryDebitsCredits.Acct, QryDebitsCredits.Transit_Routing, QryDebitsCredits.Trace, QryDebitsCredits.Transaction_Amount
FROM QryDebitsCredits 
INNER JOIN QryOffsets 
ON (QryDebitsCredits.Effective_Entry_Date = QryOffsets.Effective_Entry_Date) AND (QryDebitsCredits.Company_Name = QryOffsets.Comany_Name)
WHERE (((QryDebitsCredits.Transaction_Amount)<>[QryOffsets].[Transaction_Amount]))
ORDER BY QryDebitsCredits.Effective_Entry_Date, QryDebitsCredits.Company_Name, QryDebitsCredits.Transaction_Amount;

I've attached two screenshots - 1 with views of the source data and the other with a view of the data from the above SQL.
The data in the screenshots is from a small subset of the actual data.

I'd appreciate any help I can get.

Thanks!

P.S.
The Debits & Credits Query returns 50 rows of data
The Offsets - 13.
The Transactions_Minus_Offsets Query - 640 rows.
All I want is for the 13 matching rows [in this case they are all negative numbers] not to show in my Transactions_Minus_Offsets Query. That should give me 37 rows of data instead of 640.

[Robeen]

Hi Guys!

I think I may have this solved by using a 'NOT EXISTS' subquery.

Here's my SQL:

SELECT a.Effective_Entry_Date, a.Company_Name, a.Tran_Amt
FROM QryDebits_Credits AS a
WHERE NOT EXISTS
(SELECT 1
FROM QryOffsets b
WHERE a.Effective_Entry_Date = b.Effective_Entry_Date
AND a.Company_Name = b.Company_Name
AND a.Tran_Amt = b.Transaction_Amount);

The thing is - this takes longer to run than my usual queries [perhaps because I've never done one like this with this many records] and I don't know if that is because the total number of rows in the actual Debits_Credits table is around 33k and the actual number of rows in the Offsets table is 1123.
If I reduce the number of records to around 20k and 1k [approx] . . . it takes around three minutes to run.
When I say 'Run' . . . I mean for the total number of records to show up in the record number box at the bottom of the query window after it has completely finished running. Before that, it will not let me do a Ctrl + End to get to the bottom of the recordset.
It does come up with a 'Ready' message after the first 10 seconds or so . . .

Any ideas about whether this type of query is inherently laborious?

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.