Query Returning Duplicates

[rlsublime]

I have a query that is returning duplicate records. I created 2 helper queries (EPPR_A) for approved date and (EPPR_R) for received date. The iif statement below is bucketing based on time it took for received to approved. There can be the same loan number with two or 3 different received and approved dates and that is where i am having the issue. basically the scenario below is where i am getting the duplicates:

Scenario
Loan Received Date Approved Date
12345 4/4/2013 4/4/2013


12345 4/12/2013 4/13/2013
12345 4/4/2013 4/13/2013 (DUPLICATE RECORD that is non existant in any of the fields. It seems as though it is taking the received and approved dates from above and combining them. How can I get rid of this and stop this from occuring.) Thanks



ReceivedtoApproved: IIf([EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]<=1,"0-24 hours",IIf([EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]>1and [EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]<=2,"24-48 hours",IIf([EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]>=3 and [EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]<=4,"48-72 hours",IIf([EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]>4 and [EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]<=5,"72-96 hours",IIf([EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]>5 and [EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]<=6,"96-120 hours",IIf([EPPR_A]![ACTUAL_COMPLETION_DATE]-[EPPR_R]![ACTUAL_COMPLETION_DATE]>6,"120+ hours"))))))

[June7]

All records in one query will join with all records in the other query where the Loan number is the same if Loan number is the only linking value. The query is working as designed. What do you want it to do?

Showing expression calculating a field isn't helpful.

[rlsublime]

I am trying to have it not display the 3rd record in the above example that is combining data from the first and second records. Is there a way to have this not occur? I am using tables linked from sql so there is no Auto ID, but I can create a make table or append to create auto ID if the auto ID would help? Do you know any other possible solutions to solve this? Thanks

[June7]

Need to join the queries on enough fields that result in a unique identifer. An autonumber ID as you proposed might do that. I don't know your data so can't get specific. If you want to provide db follow instructions at bottom of my post.

[rlsublime]

Please see attached database as a sample of what I am trying to do. I am trying to get the received to approved query to only display the following:

Loan number	ELPR_A.Completed Date	ELPR_R.Completed Date
12345 12345	2/4/2013 2/13/2013	2/4/2013 2/12/2013

[June7]

The file you posted is corrupted. There are no data tables.

Correction, my first download attempt corrupted the file. Now I have good file.

No, autonumber field in each table will not help.

There is no way for the query to know which date in each table matches to date in other table. So the query does the best it can and joins all the Loan Number records from each table. What rule should determine that 2/12/2013 and 2/13/2013 should be on same row?

You said these were queries but you provided the data as tables. Is only one table the source for both queries? Need raw source data these queries are based on to analyse this issue. You could import some of the SQL records to table.

[rlsublime]

Is there a way to say that: For every matching loan number in the column, if dates are between 0 to 3 days then they should be on the same row else mark them as duplicates and do not include in the query? Currently this is the only data source that i have available. the other thought is can we do some sort of concatenation to make the dates unique?

Or better yet, is there a way to add the following: If there is a duplicate in Loan Number column, Received Date column and Approved Date column and all of those records fall into the same row then delete the row or not include the row in the query? Thanks

[June7]

Try:

SELECT ELPR_A.[Loan number], ELPR_A.[Completed Date], ELPR_R.[Completed Date]
FROM ELPR_A, ELPR_R
WHERE (((ELPR_A.[Completed Date]) Between [ELPR_R].[Completed Date] And [ELPR_R].[Completed Date]+3));

Don't really understand your other ideas.

[rlsublime]

If there is a way to do the following this would really help me too. I am trying to create a Dcount inline statement in my query that will count duplicates in table A and the field is LOAN_NUMBER. If there are duplicates I want to display 1, and 2 if no duplicates. Thanks

I realized that the +3/-3 days will not always work, but if I can somehow create a Dcount inline statement, this will really help me! thanks

[June7]

CountApp: IIf(DCount("*","ELPR_A","[Loan Number]=" & [ELPR_A].[Loan Number])>1,1,2)

Your sample data doesn't show duplicates in table A.

I removed the -3 term from the suggested query.

[rlsublime]

i am getting an error when i run this. Any ideas. I entered it exactly as stated above.Thanks

[rlsublime]

i am only trying to get it to count the duplicates in the LOAN_NUMBER column.

[rlsublime]

so for every duplicate loan number in the loan number column,i want to display a 1 in the row

[rlsublime]

I was able to get it to work when I created used a table instead of a query. What I am trying to do next is do the same thing but for a Date field. I tried the below but it did not work on the date field. How can I add the iif Dcount to a date. Thanks

CountApp2: IIf(DCount("[R]","1234","[R]=" & [R])>1,1,2)

[June7]

You could have included the expression in the query I suggested

The DCount you show doesn't make sense to me. You have a table called 1234 and field called R?

I thought you just needed to know if there are duplicate records for each Loan Number, regardless of the date. That's what you originally wanted to eliminate - the additional records for each Loan Number.