Select Query producing inaccurate results

[dubez]

Hi,

I have a query that is returning inaccurate "Date Completed" results, please help! Here is the SQL:

PARAMETERS [Enter Start Date] Text ( 255 ), [Enter End Date] Text ( 255 );
SELECT DISTINCT [Qry BDM FA English Email Test Part 1].ADI_DISTRIBUTOR_ADVISOR, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_DATE_COMPLETED, [Qry BDM FA English Email Test Part 1].AGA_EMAIL, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID, DateAdd("d",180,Date()) AS [Next Update], IIf([Next Update]<=[Next Update],"No","Yes") AS [Use in List?]
FROM ADL_LAN_LANGUAGE INNER JOIN ((ADL_AAH_ADVISOR_ACTIVITY_HISTORY INNER JOIN ([Qry BDM FA English Email Test Part 1] INNER JOIN ADL_AGA_AGENT_ADVISOR ON [Qry BDM FA English Email Test Part 1].AGA_EMAIL = ADL_AGA_AGENT_ADVISOR.AGA_EMAIL) ON ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_ADI_ID = ADL_AGA_AGENT_ADVISOR.AGA_ID) INNER JOIN ADL_CME_CONTACT_METHOD ON ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_CME_ID = ADL_CME_CONTACT_METHOD.CME_ID) ON ADL_LAN_LANGUAGE.LAN_ID = ADL_AGA_AGENT_ADVISOR.AGA_LAN_ID
WHERE (((ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_DATE_COMPLETED) Between ([Enter Start Date]) And ([Enter End Date])) AND ((ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="281" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="388" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="393" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="433" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="440" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="551" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="736" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="751" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="757" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="775" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="776" Or (ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID)="825") AND ((ADL_AGA_AGENT_ADVISOR.AGA_ACTIVE)="true") AND ((ADL_AGA_AGENT_ADVISOR.AGA_LAN_ID)="1") AND ((ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_CME_ID)="4") AND ((ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_AHS_ID)="6"));

 

[spikepl]

I can assure you that the query returns perfectly accurate results. Perhaps not the ones you want, but thats not evident from the query itself.

Don't pose such informationless questions. When unhappy, say what you expected/wanted to get and what you got instead. "Inaccurate" is not a unique measure of anything.

 

[dubez]

Well I am matching the dates up to what I have in the back end and it does not match. All other fields appear to be showing accurate results, however the "Date Completed" field is wonky and it is not matching up to the "ADI_DISTRIBUTOR_ADVISOR" number.

Should I be doing this in another way or using different join properties?

Thanks!

 

[spikepl]

Wonky, not matching .. blah - use something that others can measure/test.

You need to be specific and give examples: what record with what date did you expect to get , or what record containing what in the date field did you not get even if teh criterion, which was .. whatever it was (give example) - inCluded that date.

 

[dubez]

I actually removed the "Distinct" Clause in the SQL statement and it looks like it now works.

Thanks for the help!