Access Query Problem

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

Guest

I have a query issue. It's a bit complicated, so please bear with me...

I am running this query,
SELECT SSRS_SRS_AD_HOC_COMBINED_V1.ISSUED_DATE,
SSRS_SRS_AD_HOC_COMBINED_V1.REVIEWED_DATE,
SSRS_SRS_AD_HOC_COMBINED_V1.PRODUCT_NUMBER,
SSRS_SRS_AD_HOC_COMBINED_V1.SERIAL_NUMBER,
SSRS_SRS_AD_HOC_COMBINED_V1.RETURN_REASON_DESC,
SSRS_SRS_AD_HOC_COMBINED_V1.TECH_COMMENTS,
SSRS_SRS_AD_HOC_COMBINED_V1.ERROR_CODE,
SSRS_SRS_AD_HOC_COMBINED_V1.ITEM_NUMBER,
SSRS_SRS_AD_HOC_COMBINED_V1.CUST_RETURN_CODE,
SSRS_SRS_AD_HOC_COMBINED_V1.INCIDENT_CODE,
SSRS_SRS_AD_HOC_COMBINED_V1.PI_CODE,
SSRS_SRS_AD_HOC_COMBINED_V1.PI_ITEM_NUMBER
FROM SSRS_SRS_AD_HOC_COMBINED_V1
WHERE (((SSRS_SRS_AD_HOC_COMBINED_V1.PRODUCT_NUMBER) Like "21-610*") AND
((SSRS_SRS_AD_HOC_COMBINED_V1.ITEM_NUMBER)="70-0149")).

The outputs are table records that contain 70-0149 in the ITEM_NUMBER field
(as would be expected), as well as records that don't contain 70-0149 in the
ITEM_NUMBER field (as wouldn't be expected).

If I look at the record, using a company-created utility, I see that the
ITEM_NUMBER field contains multiple entries - it's as though the table
possesses a 3-d ITEM_NUMBER field. These ITEM_NUMBERs represent part numbers
replaced during a repair, and in any given table entry there may be more than
one part number listed. The query sees the requested part number, but then
reports only one of the ITEM_NUMBERs. This ITEM_NUMBER may not be what was
being queried. I've seen results where there have been multiple, duplicate
records; meaning that there are x duplicate records containing the same
ITEM_NUMBER (where x equals the number of parts replaced). I've also seen
situations where there is only one record reported, with the unexpected
ITEM_NUMBER being returned (where there may have been >1 part replaced). I
see no pattern in the results - there seems to be randomness.

I have used another query tool, and am getting correct results – so it must
be related to Access.
 
Try this change ---
WHERE (SSRS_SRS_AD_HOC_COMBINED_V1.PRODUCT_NUMBER Like "21-610*") AND
(SSRS_SRS_AD_HOC_COMBINED_V1.ITEM_NUMBER="70-0149");
 
Unfortunately, this did not change the outcome.

KARL DEWEY said:
Try this change ---
WHERE (SSRS_SRS_AD_HOC_COMBINED_V1.PRODUCT_NUMBER Like "21-610*") AND
(SSRS_SRS_AD_HOC_COMBINED_V1.ITEM_NUMBER="70-0149");
Click to expand...
 
Back
Top