Query is not locating all records

[Guest]

My apologies in advance for the length of this. I have a query that is not
pulling all records available for the specified criteria. It pulls only 5 of
12 records with a value of "Open." I am sure that my problem is with
relationships of some of the tables in the query, because when I remove the
"additional" tables and just query the main table, I get all the "open"
records.

I enter criteria in an unbound form, with a button that opens another form
to show the results of the search. The latter form is bound to the query. My
data entry form has three cascading combo boxes that filter selections
successively. The bound column of those combos records a value, not text. In
this query, I want to obtain the text value from the selection made in combo
box 3.

Here is structure of the tables.

Main table has three fields for the combo box values (among other fields):

ProgramID
CategoryID
DescriptionID
(PK for this table is another field - TicketNum)
Table for combo 1: ProgramAreas
ProgramID (number)
Program (text)
PK is ProgramID

Table for combo 2: Categories
ProgramID (number)
CategoryID (number)
Category (text)
PK is composite of ProgramID and CategoryID)

Table for combo 3: Description
ProgramID (number)
CategoryID (number)
DescriptionID (number)
Description (text)
PK is composite of Program, Category, and Description IDs

I set up a relationship in the Relationships window between each of the
tables (IDs) and the main table, e.g., ProgramID to ProgramID (main),
CategoryID to Category ID (main) etc.

In the query, I created additional relationships between each table and all
three IDs in the main table (because using only the relationships from the
Relationships window results in duplicate records). So main table has
ProgramID relationship to three tables, CategoryID to two and DescriptionID
to one. I then included in the query all three IDs from the main table, as
well as the three IDs from from the table for combo 3. (I have also tried
putting everything from the combo box tables into the query as well -- in
desperation.) When I run the query, I don't get all the available records for
the criterion entered in the Search form. Can anyone provide me guidance on
this problem?

 

[John Vinson]

When I run the query, I don't get all the available records for
the criterion entered in the Search form. Can anyone provide me guidance on
this problem?

Are there records in the two child tables for every one of the 12 main
table records? If not, you may need some Left Outer joins instead of
the default Inner join.

Please open the Query in SQL view and post the SQL text here. It's a
lot easier to follow that than your description, I fear!

John W. Vinson[MVP]

 

[Guest]

John: Here's more information to clarify. -- and some additional weirdness.

About the child tables: Yes, there are records in them -- these are the
source of items for selection in the combo boxes on the main form.

I had made changes, though, in the values and structure, so what was on the
main form didn't match the new items for selection. I have now "reselected"
all values so that the main table and child table values are in synch.

However, I'm still having mysterious problems. My search form can be used to
enter up to 5 different criteria. When I enter criteria in the Status field
(Open/closed) in the query and do a search for "Open", the search yields 18
records (correct). Here's the criterion: Like
[Forms]![frm_Search]![cboSearchStatus] & "*". When I also enter a criterion
in the Assignee field in the query and do a search only for open records, I
get 16 (not 18) records. When I enter an assignee's name in the appropriate
control and leave the Status cbo blank, I get 11 records that include blanks
in the Assignee control, as well as the names of other people that I did not
search on.

Here is the SQL of the query. I've stripped out some fields to make it
easier to read, but it's still rather "bearish."

SELECT tbl_Service_Requests.TicketNum, tbl_Service_Requests.Status,
tbl_Service_Requests.ActionAssignee, tbl_Service_Requests.ReceivedDate,
tbl_Descriptions_Detail.ProgramID, tbl_Descriptions_Detail.CategoryID,
tbl_Descriptions_Detail.DescriptionID, tbl_Descriptions_Detail.Description
FROM ((tbl_Service_Requests INNER JOIN tbl_Program_Areas ON
tbl_Service_Requests.ProgramID = tbl_Program_Areas.ProgramID) INNER JOIN
tbl_Categories_Detail ON (tbl_Service_Requests.ProgramID =
tbl_Categories_Detail.ProgramID) AND (tbl_Service_Requests.CategoryID =
tbl_Categories_Detail.CategoryID)) INNER JOIN tbl_Descriptions_Detail ON
(tbl_Service_Requests.CategoryID = tbl_Descriptions_Detail.CategoryID) AND
(tbl_Service_Requests.ProgramID = tbl_Descriptions_Detail.ProgramID) AND
(tbl_Service_Requests.DescriptionID = tbl_Descriptions_Detail.DescriptionID)
WHERE (((tbl_Service_Requests.Status) Like
[Forms]![frm_Search]![cboSearchStatus] & "*") AND
((tbl_Service_Requests.ActionAssignee) Like
[Forms]![frm_Search]![cboSearchAssignee] & "*"))
ORDER BY tbl_Service_Requests.ReceivedDate DESC;

I have another query for a report on "Open" requests, which yields 16
records! "Open" is the only criterion in that query. But it is basically
structured the same way as the one above. I may be on my way to the "Funny
Farm" shortly!

 

[Guest]

John: Update. After more research, I did find that the reccord in the main
table must have a value in the DescriptionID field to be picked up by the
query. That accounted for 16 vs 18 records in the "other" query I mentioned.
Can something needs to be added to the query to pick up null/zero length
values? Or do I need to change the join as you suggested?

The query I originally wrote about is still "hosed." Since I have now
created a fresh database and imported all objects into it, I'm going to try
to recreate the query from scratch to see if that makes a difference.

John: Here's more information to clarify. -- and some additional weirdness.

About the child tables: Yes, there are records in them -- these are the
source of items for selection in the combo boxes on the main form.

I had made changes, though, in the values and structure, so what was on the
main form didn't match the new items for selection. I have now "reselected"
all values so that the main table and child table values are in synch.

However, I'm still having mysterious problems. My search form can be used to
enter up to 5 different criteria. When I enter criteria in the Status field
(Open/closed) in the query and do a search for "Open", the search yields 18
records (correct). Here's the criterion: Like
[Forms]![frm_Search]![cboSearchStatus] & "*". When I also enter a criterion
in the Assignee field in the query and do a search only for open records, I
get 16 (not 18) records. When I enter an assignee's name in the appropriate
control and leave the Status cbo blank, I get 11 records that include blanks
in the Assignee control, as well as the names of other people that I did not
search on.

Here is the SQL of the query. I've stripped out some fields to make it
easier to read, but it's still rather "bearish."

SELECT tbl_Service_Requests.TicketNum, tbl_Service_Requests.Status,
tbl_Service_Requests.ActionAssignee, tbl_Service_Requests.ReceivedDate,
tbl_Descriptions_Detail.ProgramID, tbl_Descriptions_Detail.CategoryID,
tbl_Descriptions_Detail.DescriptionID, tbl_Descriptions_Detail.Description
FROM ((tbl_Service_Requests INNER JOIN tbl_Program_Areas ON
tbl_Service_Requests.ProgramID = tbl_Program_Areas.ProgramID) INNER JOIN
tbl_Categories_Detail ON (tbl_Service_Requests.ProgramID =
tbl_Categories_Detail.ProgramID) AND (tbl_Service_Requests.CategoryID =
tbl_Categories_Detail.CategoryID)) INNER JOIN tbl_Descriptions_Detail ON
(tbl_Service_Requests.CategoryID = tbl_Descriptions_Detail.CategoryID) AND
(tbl_Service_Requests.ProgramID = tbl_Descriptions_Detail.ProgramID) AND
(tbl_Service_Requests.DescriptionID = tbl_Descriptions_Detail.DescriptionID)
WHERE (((tbl_Service_Requests.Status) Like
[Forms]![frm_Search]![cboSearchStatus] & "*") AND
((tbl_Service_Requests.ActionAssignee) Like
[Forms]![frm_Search]![cboSearchAssignee] & "*"))
ORDER BY tbl_Service_Requests.ReceivedDate DESC;

I have another query for a report on "Open" requests, which yields 16
records! "Open" is the only criterion in that query. But it is basically
structured the same way as the one above. I may be on my way to the "Funny
Farm" shortly!

Are there records in the two child tables for every one of the 12 main
table records? If not, you may need some Left Outer joins instead of
the default Inner join.

Please open the Query in SQL view and post the SQL text here. It's a
lot easier to follow that than your description, I fear!

John W. Vinson[MVP]