Questionable Results

  • Thread starter Thread starter Bethany P via AccessMonster.com
  • Start date Start date
B

Bethany P via AccessMonster.com

A co-worker of mine wrote a pretty straight forward query with criteria. The
results that are returned do not match the criteria that is input. Records
with RR.PAYEE_ID="N0115444" are being returned. How can this be?

SELECT RR.TRANSACTION_ID, RR.PAYEE_ID, RR.ORG_NODE_ID, RR.CALENDAR_PERIOD_ID,
RR.CALENDAR_PERIOD_END_DATE, RR.CALCULATION_PERIOD_ID, RR.
CALCULATION_PERIOD_END_DATE, RR.PLAN_NAME, RR.POST_ID, RR.BUSINESS_UNIT_ID,
RR.MODIFIED_DATE, RR.POLICY_NUMBER, RR.POLICY_COUNT, RR.POLICY_FACTOR, RR.
POLICY_FACTOR_OVERRIDE, RR.POLICY_FACTOR_USED, RR.TRANSACTION_DATE, RR.
TRANSACTION_TYPE, RR.LIFE_RECORD_TYPE_CODE, RR.TRANSACTION_CODE, RR.
TRANS_EFFECTIVE_DATE, RR.POLICY_EFFECTIVE_DATE, RR.CHANNEL, RR.
MULTIPLE_CREDIT, RR.SOURCE_OF_BUSINESS, RR.PROCESSING_ERRORS, RR.FOLIO_NO, RR.
RUN_NO, RR.LOCAL_SALES_OFFICE, RR.LINE_OF_BUSINESS, RR.POLICY_NUMERAL, RR.
POLICY_CODE, RR.STATE_PROVINCE_CODE, RR.RATE_TERRITORY, RR.NEW_RENEWAL, RR.
COMMISSIONABLE_PREMIUM, RR.RDC_CODE, RR.MASS_MARKETING_CLIENT, RR.CANCEL_CODE,
RR.POLICY_FIRST_YEAR, RR.RESIDENTIAL_ZIP_CODE, RR.BILLING_METHOD, RR.
CHANNEL_OVERRIDE, RR.ROLE_FACTOR, RR.CHANNEL_FACTOR, RR.LOB_FACTOR, RR.
POLICY_FACTOR_OVERRIDE_REASON, RR.REP_NO, RR.REP_LAST_NAME, RR.REP_FIRST_NAME,
RR.DRC_OFFICE, RR.COMMISSIONABLE_PREMIUM_UOM, RR.COMMENTS, RR.CREATED_BY, RR.
CREATE_DATE, RR.POLICY_HOLDER_NAME, RR.MODIFIED_BY, RR.PRODUCT_LINE, RR.
COMPANY, RR.MARKET, RR.UNDERWRITING_DIVISION, RR.UNDERWRITING_SUBDIVISION, RR.
POLICY_SERIAL_NUMBER, RR.POST_ID
FROM RR
WHERE (((RR.PAYEE_ID)="N0137283") AND ((RR.POST_ID)=1405));

--
Thank you,
Bethany


Message posted via AccessMonster.com
 
This sounds like a corrupted index.

Try repairing the database:
Tools | Database Utilities | Compact/Repair

If that does not solve the problem, post a further reply.
 
That still didn't work. And to further complicate matters he now has issues
with other queries, regardless of what is entered for criteria the results
are not accurate. For example, he puts '23' in the criteria field and gets
results with 23 and 22. Any further thoughts as to what may be happening. If
I didn't see this for myself, I wouldn't believe it.

Thanks for your help.
 
Okay, your query is very simple. Let me verify that:
- RR is a table (not the name of another query)?
- PAYEE_ID is a Text type field (when you open the table in design view)?
- POST_ID is a Number type field?

There are two possible explanations for this:
a) Access is confused about which fields Payee_ID and Post_ID are, and is
therefore applying the criteria against the wrong thing.
b) The index is still bad (was not repaired.)

To solve a), uncheck the boxes under:
Tools | Options | General | Name AutoCorrect.
Then compact the database again to get rid of this junk:
Tools | Database Utilities | Compact
For more info on this issue, see:
http://allenbrowne.com/bug-03.html

To solve b), remove all relations and indexes for this table, and get Access
to recreate it for you. The steps are spelled out in the 2nd symptom in this
article:
Recovering from corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, and you can verify that the 3 assumptions
are correct, we will walk you through the steps to get Access to recreate
the entire database.
 
Back
Top