Duplicates showing up in query

[BrockWade]

I'm struggling with what would seem quite easy... I have two tables tblBank and tblBook... I'm getting duplicates... any clues? Looks like the LEFT JOIN would give me only 2 rows back:

SELECT DISTINCT Book.Store, Book.Amount
FROM tblBank LEFT JOIN tblBook ON tblBank.Amount = tblBook.Amount;

tblBank


Store Amount
15 400.00
21 900.00
27 300.00
29 500.00

tblBook
Store Amount
8 300.00
15 400.00
15 400.00
21 600.00
27 300.00
27 300.00
29 700.00
30 100.00

Query results:

Store Amount
15 400.00
15 400.00
27 300.00
27 300.00

[rpeare]

What do you want your result to be? You have two records in your tblBank for store 27 and 2 records in your tblBook for store 14, your query is going to give you #appearances in table 1 * #appearances in table 2 in terms of raw results. so if you had 3 of the same store in tblBank and 4 in tblBook you'd end up with 12 records.

So what is your desired end result are you trying to sum the amount in tblBank and subtract the sum of the amount in tblBook?

[BrockWade]

I'd like the results to be:

Store Amount
15 400.00
27 300.00

[rpeare]

So you are only looking for items that have a value in both tables and which table is the AMOUNT field supposed to come from is it a formula or part of the matching criteria?

Try this:

SELECT DISTINCT Book.Store, Book.Amount
FROM tblBank LEFT JOIN tblBook ON tblBank.Amount = tblBook.Amount
GROUP BY Store, Amount

[BrockWade]

This actually works no dupes, but I had greatly simplified things:

There is a date field also:

tblBank
Store Amount Date
15 400.00 11/20/13
21 900.00 11/01/13
27 300.00 11/15/13
29 500.00 11/13/13

tblBook
Store Amount Date
8 300.00 11/03/13
15 400.00 11/14/13
15 400.00 11/27/13
21 600.00 11/15/13
27 300.00 11/19/13
27 300.00 11/07/13
29 700.00 11/14/13
30 100.00 11/22/13

When I attempt to run this I get a message that Date needs to be in the aggregate function... so I put it in and the dupes reappear:

SELECT DISTINCT Book.Store, Book.Amount, Book.Date
FROM tblBank LEFT JOIN tblBook ON tblBank.Amount = tblBook.Amount
GROUP BY Store, Amount, Date

[rpeare]

Perhaps if you say what you are actually trying to accomplish. I explained why those 'extras' were appearing.

With the dates included it makes it even more baffling what you're trying to accomplish since your dates for store 27 in both tables have nothing in common, nor do the records for store 15.

[BrockWade]

I'm simply wanting to get one 400.00 figure returned instead of two for store 15... correct the dates have nothing in common as the date that an entry is "booked" is different from the date it is "banked" and a store can have two 400.00 entries on the "books" but only one appearing in the "bank" because of lag time

[rpeare]

That doesn't explain what you're trying to do, the query I gave you will work if you leave the dates out of it, your inclusion of the dates makes me think something else is going on and that one of the dates is relevant to you.