Deleting offsetting debits and credits

  • Thread starter Thread starter Smigidy
  • Start date Start date
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
 
Try this --
SELECT [1].[Receipt], Sum([1].Amount) AS [Debit-Credit]
FROM [1]
WHERE (Sum([1].Amount) <>0);
 
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];
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top