I need help with a query that would seem to be simple, trying again (?)

[BrockWade]

I have two tables to compare, currently without a key field which I can add if necessary for any uniqueness of records one might find necessary to solve this problem. As you can see below I need to return 6 rows



I have one table called BankDebits that has these 9 rows (just a sampling of data with one test amount of 800.00):
Store Amount
36 800.00
36 800.00
126 800.00
175 800.00
175 800.00
252 800.00
704 800.00
1607 800.00
1621 800.00

I have another table called Bank that has these 4 rows (just a sampling of data with one test amount of 800.00):Store Amount
36 800.00
126 800.00
175 800.00
252 800.00

I only want this data returned with these four records that match:
Store Amount
36 800.00
126 800.00
175 800.00
252 800.00

SELECT BankDebits.Store, BankDebits.Amount
FROM Bank LEFT JOIN BankDebits ON Bank.Amount = BankDebits.Amount
GROUP BY BankDebits.Store, BankDebits.Amount
ORDER BY BankDebits.Amount DESC;

- successfully gives me this:

Store Amount
36 800.00
126 800.00
175 800.00
252 800.00

Problem is I need a query that will give me the rest of the unmatched:

Store Amount
36 800.00
175 800.00
704 800.00
1607 800.00
1621 800.00

But the below returns no records... thanks in advance for any help:

SELECT Bank.Store, Bank.Amount
FROM Bank
WHERE (((Exists (SELECT Bank.Store, Bank.Amount
FROM BankDebits LEFT JOIN Bank ON BankDebits.Amount = Bank.Amount))=False))
ORDER BY Bank.Amount DESC;

[orange]

Since we only know what you tell us, please step back and tell us in plain English WHAT you are trying to do.
Pretend you're talking to an 8 year old who knows nothing about you or database.
We need more context if we are to offer focused suggestions/help.

[BrockWade]

I have two tables to compare, "BankDebits" with 4 transactions and "Bank" which has 9 transactions.
Each table has information for a store and an amount.
I need to find out what is in "Bank" that is also in "Bank Debits" and return only that.
Then I need to return what is not returned in the above.
Because "Bank Debits" can have identical information from row to row,
the first query is to get one record and the second query is to get the other.

[orange]

So, you have financial transactions that relate to Stores. And under some condition(s) you return an amount of money.

1 store or several stores
1 bank or several banks
1 account or several accounts

Can you put he pieces together with a little more description from a business perspective?

[ssanfu]

I am confused....

In looking at your queries, are you trying to match on Store numbers between the two tables or on matching the amounts??

[BrockWade]

First I edited my original post, minor change: "As you can see below I need to return 4 rows" instead of "As you can see below I need to return 6 rows"

Several stores, 1 Bank, 1 Account.

During a month a store (actually there are almost 40 stores with combined sales of $4.5 million monthly) will deposit large bills (recorded by the bank in the table called "Bank") and the stores will log receipt of smaller bills (recorded by the stores as "Bankdebits"). Because of a natural business-to-bank communication lag-time "BankDebits" always has more transactions than "Bank". So with my original post's data I noted two $800 store deposits for store 36, even though the bank at that point in time only showed one $800 transaction. So I need to return in one query four records and in my second query the other five records. Again the original numbers:

I have one table called BankDebits that has these 9 rows (just a sampling of data with one test amount of 800.00):
Store Amount
36 800.00
36 800.00
126 800.00
175 800.00
175 800.00
252 800.00
704 800.00
1607 800.00
1621 800.00

I have another table called Bank that has these 4 rows (just a sampling of data with one test amount of 800.00):
Store Amount
36 800.00
126 800.00
175 800.00
252 800.00

[BrockWade]

Additional post above may help clarify...

[orange]

You need to step back and describe the issue without telling us HOW you have done whatever you have done.
Once we know WHAT you are trying to do (your business), we may be able to offer more focused suggestions/solutions.
You are too focused on your database --think in terms of business.
Pretend we're a bunch of 10 year olds who asked you -- What do you do?

Where do big bills and small bills fit into the business?

        transaction large bills
Store----------------------------->BankAccount<----------------Bank

[BrockWade]

The stores need to send $100 bills to the bank to get back smaller bills. I don't know what more to add to my description of the scenario... can you give me more specific questions that are unclear?

[orange]

Now it sounds like you have a glorified Money Changer application. Why do they need smaller bills?

Like a car wash or coin laundry-

change $20 bill into 10 x $2 (since a car wash costs $8 or $10 or $12
or
change $5 bill into quarters since machines take multiple 25 cent pieces/quarters.

I'm not certain you have a database application. Also, if you can't describe your requirement to a 10 year old, you don't have enough info to solve it.
I don't know what else to tell you other than describe the process generally and then identify all the data and relationships in the data required to support your business.

[June7]

I don't see how you can do a 'match/unmatch' between the two tables with the data shown. Both of the 36 $800 records in BankDebits will match to the single 36 $800 record in Bank. Is there a date or deposit number for each record common to both tables?

Could do an aggregate count of store/amount pairs for each table. The difference in the count will indicate a variance.

[BrockWade]

Please don't be condescending to someone who has done everything in my power to present a ligitimate business and database challenge in mostly my first post. My first query has served well and I was simply needing advice on the second query. Obfuscating by requiring distilling this down to a 10 year old level does nothing for me or anyone else in this forum, but obviously it does something for you.

[orange]

I'm sorry if you find my posts condescending I still have no idea what your business is. I'm trying to get you to describe to the reader in simple, plain English WHAT it is that you do. And I feel that after several attempts we're no closer. It's purely a guess on my part, but it appears you have a database structure issue - your tables and relationships may not support your business.

Obfuscating by requiring distilling this down to a 10 year old level does nothing for me or anyone else in this forum, but obviously it does something for you.

There is no obfuscation on my part. It was a request to you to tell us in simple terms WHAT you do. The rationale is that we can't offer advice, until we understand your situation. Once we know WHAT you're trying to achieve, then the readers, calling on their experience and expertise, can suggest./advise/recommend accordingly. But it all depends on our understanding your issue. That's why I asked, "What do you do?".

I would suggest to you that in your post #1, where you said

As you can see below I need to return 6 rows

there isn't 1 reader who could see that -- well maybe one.

Good luck with your project.

[ssanfu]

Please don't be condescending

I have to agree with Orange on this. I don't think he is being condescending.
After reading your posts several times I still haven't got a clue as to what you are trying to do.

It doesn't make sense to me to match amounts instead of stores.

3 people have responded and all are confused as to what you are trying to do.

if you can't describe your requirement to a 10 year old

This is really a good method to get a poster to get their thoughts together to be able to describe the process... not specific calculations or how has been tried.

"I changed the bearings and I need to fix the belt. What I have tried hasn't worked. there is still a problem. Can you help."
Does that tell you anything? Am I working on my car, a wrecker, a combine, an overhead crane, .....??????

But if I explained the problem so a 10 year old could understand, then anyone else should be able to understand and offer suggestions.
(BTW, it was a canning machine at a cannery that had a squeal. Eventually required disassembling half the machine to fix.)

I do find this an interesting problem.... I am hoping I can help. But I still do not have an understanding of what you are trying to accomplish.

So here is an example.

table Bank
Store Amount
36 200.00
126 300.00
175 500.00
252 600.00

table BankDebits
Store Amount
36 100.00
36 200.00
126 300.00
175 400.00
175 500.00
252 600.00
704 700.00
1607 800.00
1621 800.00

What are the results you expect for matches and non-matches?
Does the store number have any meaning in matching amounts?