Why doesn't my query omit criteria not asked for?

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

Guest

I have an Access query that looks like this...Criteria: Like "*71-0246*" that
reports non-like numbers as well. Why might this be? BTW, I've tried to
eliminate asterisks with no change. Also, duplicate table entries appear to
be returned.
 
Please post the entire SQL. You might have something else going on like an OR
statement or Cartesian join especially as you are seeing duplicates.
 
I used the "Create query in Design view" method. It consisted of 4 columns.
The only cell I entered data in was the "Criteria" cell on one of the
columns. Here is a copy of the cell contents: Like "*71-0246*". If you need
something else, please instruct me on how to rend the required data.

Thanks.
 
From the QBE grid, switch from Design View to SQL View (View | SQL
View), Copy the SQL you see there, and Paste it into your post.

LeAnne
 
Here is the SQL data:

SELECT SSRS_SRS_AD_HOC_COMBINED_V1.LOT_NUMBER,
SSRS_SRS_AD_HOC_COMBINED_V1.SERIAL_NUMBER,
SSRS_SRS_AD_HOC_COMBINED_V1.ITEM_NUMBER,
SSRS_SRS_AD_HOC_COMBINED_V1.ISSUED_DATE
FROM SSRS_SRS_AD_HOC_COMBINED_V1
WHERE (((SSRS_SRS_AD_HOC_COMBINED_V1.ITEM_NUMBER) Like "*71-0246*"));
 
Now we are getting somewhere! ;-)

Here's a very important question: Is SSRS_SRS_AD_HOC_COMBINED_V1 at Table or
a Query? The name suggests that it may be a query.

If a query please post the SQL for that query. If
SSRS_SRS_AD_HOC_COMBINED_V1 happens to be based on other queries (and I'm on
my knees praying that it isn't), we'll need to see those SQL statements also.

The SQL that you posted below is very straight forward and should not be
causing the problems that you described. I even check that negative sign " -
" in the like statement wasn't messing things up. Therefore we need to dig
deeper.
 
SSRS_SRS_AD_HOC_COMBINED_V1 appears to be made up of
SSRS_SRS_REPAIRS_AD_HOC_V1 and SSRS_SRS_REPAIRS_AD_HOC_V2. They all appear
to be identified as tables. If I do the same query on ...V2, I get the
non-duplicated records I am looking for. However, it doesn't give me all the
entries (~600). To further explain, I am expecting along the lines of 600
entries, and all that gets returned is 19. To give some background, this is
an archaic database that my company has been using for ~10 years, and no one
involved at its inception is still employed. It still routinely has records
entered.

The aforementioned query (based on the combined table) represents the number
of occurrences of a specific part replacement - again I'm expecting ~600 and
receive 19. Originally, I got query results in the 600 range, but only 19
that reported my criteria values. I believe it is possible that more than
just the criteria-identified part were replaced. Is it possible that the
entry for the criteria-identified part was "hidden", or not viewable, or not
reportable; and yet included in the original query results? We may be
looking for some obscure situation.

Also, I'm not sure how the duplication of records may play into it - when I
said I got ~600 originally, I really got ~1200. Half were duplicates.
 
Back
Top