S
Susan L
I've posted on this database before, but now I have a problem that I just
noticed -- that a query is only pulling 5 of the 10 "Open" records from the
table when I query on "Open."
I have a form with three combo boxes, two of which filter on the previous
selection(s). What is bound to the form is the numeric value, but of course
the text is what the user sees. My question relates to the fact that I need
to show the text for those values in reports, etc.
The record sources for the combos are three tables, which contain all
possible combinations of values for each level of filtering. These tables
also contain the text (primarily for my own convenience), but there are
repetitions.
Main table has unique, indexed primary key field called TicketNum. It has
three fields for the combo boxes, called ProgramID, CategoryID, and
Description ID. Table 1 has ProgramID and Program (text).
Table 2 has ProgramID, CategoryID, and Category (text).
Table 3 has ProgramID, CategoryID, DescriptionID and Description (text).
(I also have three tables that just contain the values and text -- not all
possible combinations of values, which maybe would be better to use as a
source for the text, because there would only be one foreign key, rather than
1, 2, and 3 respectively). Table 1 has ProgramID & Program; Table 2 has
CategoryID and Category, Table 3 has DescriptionID & Description.
Ken Snell helped me to get a query editable when I wanted to include the
text for "Description" by adding the three-field foreign key. I was happy as
a pig in mud! I didn't realize that the query was not pulling all the
records. I am now at a loss to figure out how to set up the relationships
between these tables properly. When I delete the relationships/tables for the
text, I get the proper number of records. When I put them back in, I get
fewer than I should.
Currently, I have an "indeterminate" relationship between the main and combo
box tables (the second set of tables with only the ID and text), on the
ProgramID, CategoryID etc. fields. I also tried adding TicketNum to the three
tables and relating that, but then I got no records. I've put all "ID" fields
from the main table in the query to provide the values and all IDs and text
fields from the other tables to provide the text value. I've tried so many
things that I can't remember what I've tried at this point.
Could you advise on a method to get the text values in a query, e.g., what
to relate to what, also whether relationships should be built in the
relationship window first, or can they be built in queries? I'm lost in a
quagmire of my own making! Would appreciate "a hand up." Thanks.
susan
noticed -- that a query is only pulling 5 of the 10 "Open" records from the
table when I query on "Open."
I have a form with three combo boxes, two of which filter on the previous
selection(s). What is bound to the form is the numeric value, but of course
the text is what the user sees. My question relates to the fact that I need
to show the text for those values in reports, etc.
The record sources for the combos are three tables, which contain all
possible combinations of values for each level of filtering. These tables
also contain the text (primarily for my own convenience), but there are
repetitions.
Main table has unique, indexed primary key field called TicketNum. It has
three fields for the combo boxes, called ProgramID, CategoryID, and
Description ID. Table 1 has ProgramID and Program (text).
Table 2 has ProgramID, CategoryID, and Category (text).
Table 3 has ProgramID, CategoryID, DescriptionID and Description (text).
(I also have three tables that just contain the values and text -- not all
possible combinations of values, which maybe would be better to use as a
source for the text, because there would only be one foreign key, rather than
1, 2, and 3 respectively). Table 1 has ProgramID & Program; Table 2 has
CategoryID and Category, Table 3 has DescriptionID & Description.
Ken Snell helped me to get a query editable when I wanted to include the
text for "Description" by adding the three-field foreign key. I was happy as
a pig in mud! I didn't realize that the query was not pulling all the
records. I am now at a loss to figure out how to set up the relationships
between these tables properly. When I delete the relationships/tables for the
text, I get the proper number of records. When I put them back in, I get
fewer than I should.
Currently, I have an "indeterminate" relationship between the main and combo
box tables (the second set of tables with only the ID and text), on the
ProgramID, CategoryID etc. fields. I also tried adding TicketNum to the three
tables and relating that, but then I got no records. I've put all "ID" fields
from the main table in the query to provide the values and all IDs and text
fields from the other tables to provide the text value. I've tried so many
things that I can't remember what I've tried at this point.
Could you advise on a method to get the text values in a query, e.g., what
to relate to what, also whether relationships should be built in the
relationship window first, or can they be built in queries? I'm lost in a
quagmire of my own making! Would appreciate "a hand up." Thanks.
susan