Deleting offsetting debits and credits

S

Smigidy

I have offsetting debits and credits in a query, such as shown below. How
can I ignore (delete) offsets such as this? Below is the code used to
generate the results.


Table 1
Receipt A
Amount $1000.00
Amount -$1000.00


This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks,
Michael
 
K

KARL DEWEY

Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0);
 
K

KARL DEWEY

Previous post in error ---------
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0)
GROUP BY [1].[Receipt];
 
S

Smigidy

Does this code below what I have already? If so it's not woring.
Is this replacement code? If so I have an error in the WHERE statement.

KARL DEWEY said:
Previous post in error ---------
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0)
GROUP BY [1].[Receipt];

--
Build a little, test a little.


Smigidy said:
I have offsetting debits and credits in a query, such as shown below. How
can I ignore (delete) offsets such as this? Below is the code used to
generate the results.


Table 1
Receipt A
Amount $1000.00
Amount -$1000.00


This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks,
Michael
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
GROUP BY [1].[Receipt]
HAVING Sum([1].Amount) <>0;

--
Build a little, test a little.


Smigidy said:
Does this code below what I have already? If so it's not woring.
Is this replacement code? If so I have an error in the WHERE statement.

KARL DEWEY said:
Previous post in error ---------
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0)
GROUP BY [1].[Receipt];

--
Build a little, test a little.


Smigidy said:
I have offsetting debits and credits in a query, such as shown below. How
can I ignore (delete) offsets such as this? Below is the code used to
generate the results.


Table 1
Receipt A
Amount $1000.00
Amount -$1000.00


This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks,
Michael
Click to expand...
Click to expand...
Click to expand...
 
S

Smigidy

Getting closer. It summed the amounts per DOV and left them there, giving me
what's below. I want to get rid of the lines completely if the net amount is
0.00.

B0JV0873 (730.25)
9H044994 730.25

KARL DEWEY said:
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
GROUP BY [1].[Receipt]
HAVING Sum([1].Amount) <>0;

--
Build a little, test a little.


Smigidy said:
Does this code below what I have already? If so it's not woring.
Is this replacement code? If so I have an error in the WHERE statement.

KARL DEWEY said:
Previous post in error ---------
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0)
GROUP BY [1].[Receipt];

--
Build a little, test a little.


:

I have offsetting debits and credits in a query, such as shown below. How
can I ignore (delete) offsets such as this? Below is the code used to
generate the results.


Table 1
Receipt A
Amount $1000.00
Amount -$1000.00


This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks,
Michael
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Your example below has two different Receipt numbers and therefore cannot
match the two for elimination.

Do you have something higher in the order of things like an account number
so they can be equivalent and omitted?

SELECT [1].[Account], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
GROUP BY [1].[Account]
HAVING Sum([1].Amount) <>0;

--
Build a little, test a little.


Smigidy said:
Getting closer. It summed the amounts per DOV and left them there, giving me
what's below. I want to get rid of the lines completely if the net amount is
0.00.

B0JV0873 (730.25)
9H044994 730.25

KARL DEWEY said:
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
GROUP BY [1].[Receipt]
HAVING Sum([1].Amount) <>0;

--
Build a little, test a little.


Smigidy said:
Does this code below what I have already? If so it's not woring.
Is this replacement code? If so I have an error in the WHERE statement.

:

Previous post in error ---------
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0)
GROUP BY [1].[Receipt];

--
Build a little, test a little.


:

I have offsetting debits and credits in a query, such as shown below. How
can I ignore (delete) offsets such as this? Below is the code used to
generate the results.


Table 1
Receipt A
Amount $1000.00
Amount -$1000.00


This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks,
Michael
Click to expand...
Click to expand...
Click to expand...
 
S

Smigidy

Unfortunately no.

KARL DEWEY said:
Your example below has two different Receipt numbers and therefore cannot
match the two for elimination.

Do you have something higher in the order of things like an account number
so they can be equivalent and omitted?

SELECT [1].[Account], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
GROUP BY [1].[Account]
HAVING Sum([1].Amount) <>0;

--
Build a little, test a little.


Smigidy said:
Getting closer. It summed the amounts per DOV and left them there, giving me
what's below. I want to get rid of the lines completely if the net amount is
0.00.

B0JV0873 (730.25)
9H044994 730.25

KARL DEWEY said:
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
GROUP BY [1].[Receipt]
HAVING Sum([1].Amount) <>0;

--
Build a little, test a little.


:

Does this code below what I have already? If so it's not woring.
Is this replacement code? If so I have an error in the WHERE statement.

:

Previous post in error ---------
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0)
GROUP BY [1].[Receipt];

--
Build a little, test a little.


:

I have offsetting debits and credits in a query, such as shown below. How
can I ignore (delete) offsets such as this? Below is the code used to
generate the results.


Table 1
Receipt A
Amount $1000.00
Amount -$1000.00


This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks,
Michael
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Too many duplicate entries are being deleted 3
Macro to delete rows with offsetting values - Accounting 4
Remove Identical words 0
Accounting Problem. Complex Copy/Paste and Index/Match too. 1
Dynamic RecordSet Field Name 4
Macro to delete rows with matching Credits and Debits 10
Improving Macro That Keeps First Letter 0
Date Table 0

Top