Simple Query Not Producing Certain Records...

[policeteeth]

hi.

i have a very basic Table with three simple Fields (first name, last
name, company)

i then made a very simple Query where it asks you to input the First
Name (the code i used:

Like [First Name?] & "*") the Last Name and Company - and it returns
the records that match.

Before I wrote the Query, i had 200 records in my table - if I ran the
query and did not specify any information it would return all of the
records (all 200) - furthermore, if i did specify certain criteria, it
would return the correct records...

However, I just noticed that any of the records that I've added (i've
added 5 contacts and filled out all three fields for each one in the
table) do not come up when i do a Query.

For example, if i run the Query, and I do not specify any criteria, it
still only results in the original 200 records...same goes for doing a
search on a specific last name...it seems to only be "seeing" or
"searching" the original 200 records.

i've browsed through the archives and seen similar problems, but havent
really seen any explanations on how i can fix this.

any help would be appreciated.

by the way - if i write a new Query, looking at the same table and do a
search w/ no Criteria - it will show up all 205 records...but i dont
want to have to rewrite the Query each time i add new data to my table.

thanks.

 

[[MVP] S.Clark]

Post your SQL.

 

[policeteeth]

SELECT [Contact Directory].[First Name], [Contact Directory].[Last
Name], [Contact Directory].[Company Name], [Contact Directory].[Area
Code], [Contact Directory].[Phone Number]
FROM [Company Directory] INNER JOIN [Contact Directory] ON [Company
Directory].Alpha = [Contact Directory].Alpha
WHERE ((([Contact Directory].[First Name]) Like [First Name?] & "*")
AND (([Contact Directory].[Last Name]) Like [Last Name?] & "*") AND
(([Contact Directory].[Company Name]) Like [Company Name?] & "*"));


----
let me know what you think. looking at the SQL - i'm not sure why it
mentions "From: Company Directory" when the Table I'm searching is
"Contact Directory" (mentioned as "inner join")

thanks.

 

[John Spencer]

It could be that you don't have anything in Contact Directory.Alpha or what
you do have doesn't match any value in Company Directory.Alpha. If that is
the case, then the Inner Join will drop out the records since there is no
match

Try removing the reference to company directory.

SELECT [Contact Directory].[First Name], [Contact Directory].[Last
Name], [Contact Directory].[Company Name], [Contact Directory].[Area
Code], [Contact Directory].[Phone Number]
FROM [Contact Directory]
WHERE ((([Contact Directory].[First Name]) Like [First Name?] & "*")
AND (([Contact Directory].[Last Name]) Like [Last Name?] & "*") AND
(([Contact Directory].[Company Name]) Like [Company Name?] & "*"))

Or change the INNER JOIN to a RIGHT JOIN - which should give you all matches
in Contact Directory even if there is no matching Company Directory.Alpha
entry.

SELECT [Contact Directory].[First Name], [Contact Directory].[Last
Name], [Contact Directory].[Company Name], [Contact Directory].[Area
Code], [Contact Directory].[Phone Number]
FROM [Company Directory] RIGHT JOIN [Contact Directory] ON [Company
Directory].Alpha = [Contact Directory].Alpha
WHERE ((([Contact Directory].[First Name]) Like [First Name?] & "*")
AND (([Contact Directory].[Last Name]) Like [Last Name?] & "*") AND
(([Contact Directory].[Company Name]) Like [Company Name?] & "*"));

 

[policeteeth]

John.

THANK YOU.

the "Right Join" fix worked.

thank you again.

It could be that you don't have anything in Contact Directory.Alpha or what
you do have doesn't match any value in Company Directory.Alpha. If that is
the case, then the Inner Join will drop out the records since there is no
match

Try removing the reference to company directory.

SELECT [Contact Directory].[First Name], [Contact Directory].[Last
Name], [Contact Directory].[Company Name], [Contact Directory].[Area
Code], [Contact Directory].[Phone Number]
FROM [Contact Directory]
WHERE ((([Contact Directory].[First Name]) Like [First Name?] & "*")
AND (([Contact Directory].[Last Name]) Like [Last Name?] & "*") AND
(([Contact Directory].[Company Name]) Like [Company Name?] & "*"))

Or change the INNER JOIN to a RIGHT JOIN - which should give you all matches
in Contact Directory even if there is no matching Company Directory.Alpha
entry.

SELECT [Contact Directory].[First Name], [Contact Directory].[Last
Name], [Contact Directory].[Company Name], [Contact Directory].[Area
Code], [Contact Directory].[Phone Number]
FROM [Company Directory] RIGHT JOIN [Contact Directory] ON [Company
Directory].Alpha = [Contact Directory].Alpha
WHERE ((([Contact Directory].[First Name]) Like [First Name?] & "*")
AND (([Contact Directory].[Last Name]) Like [Last Name?] & "*") AND
(([Contact Directory].[Company Name]) Like [Company Name?] & "*"));

SELECT [Contact Directory].[First Name], [Contact Directory].[Last
Name], [Contact Directory].[Company Name], [Contact Directory].[Area
Code], [Contact Directory].[Phone Number]
FROM [Company Directory] INNER JOIN [Contact Directory] ON [Company
Directory].Alpha = [Contact Directory].Alpha
WHERE ((([Contact Directory].[First Name]) Like [First Name?] & "*")
AND (([Contact Directory].[Last Name]) Like [Last Name?] & "*") AND
(([Contact Directory].[Company Name]) Like [Company Name?] & "*"));


----
let me know what you think. looking at the SQL - i'm not sure why it
mentions "From: Company Directory" when the Table I'm searching is
"Contact Directory" (mentioned as "inner join")

thanks.