Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2010 Trying to find duplicate statements in a month

Posted on 2014-03-10
6
Medium Priority
?
339 Views
Last Modified: 2014-03-10
I am trying to find duplicate statements that are entered in 1 month but the data entry may be off a day.  
Example: Statement Dates are 01/31/14, 02/28/14, 02/28/14, 03/30/14
          or  Statement Dates are 01/31/14, 02/28/14, 03/05/14, 03/30/14

I should only have 1 statement a month.  I wanted to try to create a field to convert the statement date to Month format, then look for duplicates in the month.

Fields:  
AccountID (Account Number)
StatementDate (Statement Date)

I tried:
Expr1: Format([StatementDate],"mmm")
0
Comment
Question by:NNiicckk
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39918567
try this query

select AccountID,Count(Format([StatementDate],"yyyymm")) As Expr1,Count(Format([StatementDate],"yyyymm")) As expr2
from tableName
where Count(Format([StatementDate],"yyyymm"))>1
Group by AccountID,Count(Format([StatementDate],"yyyymm"))


or

select AccountID,Count(Format([StatementDate],"yyyymm")) As Expr1,Count(Format([StatementDate],"yyyymm")) As expr2
from tableName
Group by AccountID,Count(Format([StatementDate],"yyyymm"))
having Count(Format([StatementDate],"yyyymm"))>1
0
 

Author Comment

by:NNiicckk
ID: 39918680
It says I cannot have a aggregate funtion in GROUP BY clause (Count(Format([StatementDate],"yyyymm")))
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39918711
oops sorry


select AccountID,Count(Format([StatementDate],"yyyymm")) As Expr1,Count(Format([StatementDate],"yyyymm")) As expr2
from tableName
where Count(Format([StatementDate],"yyyymm"))>1
Group by AccountID, Format([StatementDate],"yyyymm")


or

select AccountID,Count(Format([StatementDate],"yyyymm")) As Expr1,Count(Format([StatementDate],"yyyymm")) As expr2
from tableName
Group by AccountID, Format([StatementDate],"yyyymm")
having Count(Format([StatementDate],"yyyymm"))>1
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NNiicckk
ID: 39918850
The first one had "Cannot have aggregate function in WHERE clause (Count(Format([StatementDate],"yyyymm"))>1

The second one worked, thanks.

The results give me the # of statements in expr1 and expr2:

AccountID      Expr1      Expr2
125555                  3               3
567891               2          2


Is there a way to show the Month duplicated or should I just hide 1 of the fields?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39918875
try


select AccountID,Count(Format([StatementDate],"yyyymm")) As Expr1,Format([StatementDate],"yyyymm") As expr2
from tableName
Group by AccountID, Format([StatementDate],"yyyymm")
having Count(Format([StatementDate],"yyyymm"))>1
0
 

Author Comment

by:NNiicckk
ID: 39919087
That works, thanks again
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question