Duplicate Query between dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to track invoices and payments for customers, and appending
"paid" invoices to a new table. Invoices and payments are entered by invoice
number. I currently have a duplicate query that finds my duplicate records
perfectly. However, I want to find duplicate records between two dates,
which is not working. My formula is resulting in any duplicate record,
however, if one of the dates is outside of the range, it includes just the
one that is inside the range.

For instance, if i want to see all paid invoices from June 30 through July
31, I should see all invoices that have a date between June 30 and July 31
with a duplice "payment" within the same date. I do not see this. I see
just the invoice, even if they payment is August 1.

Here is the select SQL i have:

INSERT INTO [Paid Invoices] ( TransactionNumber, TransactionID,
TransactionDate, TransactionDescription, AccountID, WithdrawalAmount,
DepositAmount )
SELECT Transactions.TransactionNumber, Transactions.TransactionID,
Transactions.TransactionDate, Transactions.TransactionDescription,
Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionNumber) In (SELECT [TransactionNumber] FROM
[Transactions] As Tmp GROUP BY [TransactionNumber] HAVING Count(*)>1 )))
ORDER BY Transactions.TransactionNumber;



Any help you can give me would be greatly appreciated!
 
INSERT INTO [Paid Invoices] ( TransactionNumber, TransactionID,
TransactionDate, TransactionDescription, AccountID, WithdrawalAmount,
DepositAmount )
SELECT Transactions.TransactionNumber
, Transactions.TransactionID
, Transactions.TransactionDate
, Transactions.TransactionDescription
, Transactions.AccountID
, Transactions.WithdrawalAmount
, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionNumber) In
(SELECT [TransactionNumber]
FROM [Transactions] As Tmp
WHERE TransactionDate Between #2007-01-01# and #2007-12-31#
GROUP BY [TransactionNumber]
HAVING Count(*)>1 )))
AND Transactions.TransactionDate Between #2007-01-01# and #2007-12-31#

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
That worked beautifully! Thank you so much for your help!

John Spencer said:
INSERT INTO [Paid Invoices] ( TransactionNumber, TransactionID,
TransactionDate, TransactionDescription, AccountID, WithdrawalAmount,
DepositAmount )
SELECT Transactions.TransactionNumber
, Transactions.TransactionID
, Transactions.TransactionDate
, Transactions.TransactionDescription
, Transactions.AccountID
, Transactions.WithdrawalAmount
, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionNumber) In
(SELECT [TransactionNumber]
FROM [Transactions] As Tmp
WHERE TransactionDate Between #2007-01-01# and #2007-12-31#
GROUP BY [TransactionNumber]
HAVING Count(*)>1 )))
AND Transactions.TransactionDate Between #2007-01-01# and #2007-12-31#

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Difficult1 said:
I am attempting to track invoices and payments for customers, and appending
"paid" invoices to a new table. Invoices and payments are entered by
invoice
number. I currently have a duplicate query that finds my duplicate
records
perfectly. However, I want to find duplicate records between two dates,
which is not working. My formula is resulting in any duplicate record,
however, if one of the dates is outside of the range, it includes just the
one that is inside the range.

For instance, if i want to see all paid invoices from June 30 through July
31, I should see all invoices that have a date between June 30 and July 31
with a duplice "payment" within the same date. I do not see this. I see
just the invoice, even if they payment is August 1.

Here is the select SQL i have:

INSERT INTO [Paid Invoices] ( TransactionNumber, TransactionID,
TransactionDate, TransactionDescription, AccountID, WithdrawalAmount,
DepositAmount )
SELECT Transactions.TransactionNumber, Transactions.TransactionID,
Transactions.TransactionDate, Transactions.TransactionDescription,
Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionNumber) In (SELECT [TransactionNumber]
FROM
[Transactions] As Tmp GROUP BY [TransactionNumber] HAVING Count(*)>1 )))
ORDER BY Transactions.TransactionNumber;



Any help you can give me would be greatly appreciated!
Click to expand...
Click to expand...
 
Back
Top