S
Susan L
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?
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?