AOB
Registered User.
- Local time
- Today, 03:26
- Joined
- Sep 26, 2012
- Messages
- 617
I'm getting really strange behaviour trying to apply a (relatively simple) query as a data source for a continuous subform. The query works when I run it directly, but when I use it as the data source for the subform, some of the values are not the same. Trying to figure it out before I try a workaround.
I have two tables - categories and sub-categories. Currently, any category can be used with any sub-category. They are effectively independent of each other. Both categories and sub-categories can be enabled or disabled with a simple boolean Active flag in each table.
What I'm trying to achieve, is a way of maintaining relationships between the two such that administrators can link/de-link certain categories with certain sub-categories. To do this, I've created a junction table (
Rather than fill the junction table with every combination, I'm just going to store the combinations that admins have specified - otherwise, the defaults will be applied. This is just easier to maintain and keeps the junction table small and efficient.
So I've built the following queries to return the set of attributes for each possible combination of category and sub-category.
First, a query to (dynamically) produce all the possible combinations (I have to do this as a separate query otherwise I can't do the joins later...) Call this
Now, a second query to determine the attributes for each combination, based on whether the combination exists in the junction table and, if not, using the defaults as per the main tables :
So basically, return all the category/sub-category combinations, and for any combination that exists in the junction table, use the values in that table to determine whether the sub-category is selectable and/or mandatory for that category, otherwise selectability is based on whether the sub-category is active or not and mandatory is by default no.
I have deliberately left the junction table empty for now (so there are no records in it whatsoever)
Here's where the weirdness starts...
If I run that query directly from Query Design, it returns everything exactly as I would expect (i.e. all combinations present, all with IsSelectable = True and IsMandatory = False - which is what you would expect with an empty junction table, i.e. no exceptions)
But when I then use that query as the data source for a subform, to present the results visually on a form, the results are different?
I can see all the IDs fine but the checkboxes to represent IsSelectable and IsMandatory are all False? So the query is returning True values across the board for IsSelectable, but the checkboxes on the subform which are bound to the IsSelectable field in the query, are all unchecked?
If I add a record to the junction table for an arbitrary combination, the values as per the junction table for that specific combination do appear correctly in the checkboxes (and, obviously, in the underlying query itself) but any boolean values not represented in the junction table, appear False.
So - I'm really puzzled as to why the results from the query are not being represented in the subform, and I'm not sure if it's a problem with the query, or the subform? Given that the query appears to run perfectly fine in isolation?
(Side note - I am well aware that this query is, more than likely, non-updateable, given the joins, and thus the subform itself could not be used to make direct modifications - I am well aware of that, and indeed comfortable with it; the purpose of the subform is purely to present the current state visually and I will have separate controls for making modifications to the junction table as/when needed...)
Any and all suggestions as to what exactly is going on would be extremely gratefully received as I am at a loss as to why this isn't working the way I intended!
Thanks!
I have two tables - categories and sub-categories. Currently, any category can be used with any sub-category. They are effectively independent of each other. Both categories and sub-categories can be enabled or disabled with a simple boolean Active flag in each table.
CategoryID | Category | Active |
---|---|---|
1 | Category 1 | Yes |
2 | Category 2 | Yes |
3 | Category 3 | Yes |
SubCategoryID | SubCategory | Active |
---|---|---|
1 | Sub Category 1 | Yes |
2 | Sub Category 2 | Yes |
3 | Sub Category3 | Yes |
What I'm trying to achieve, is a way of maintaining relationships between the two such that administrators can link/de-link certain categories with certain sub-categories. To do this, I've created a junction table (
jctExceptions
) where combinations of categories and sub-categories can be stored along with specific flags for that combination. The table uses a composite primary key comprising the CategoryID and SubCategoryID, both are indexed with duplicates permitted.CategoryID | SubCategoryID | Selectable | Mandatory |
---|---|---|---|
1 | 1 | Yes | Yes |
1 | 2 | Yes | Yes |
2 | 1 | No | No |
Rather than fill the junction table with every combination, I'm just going to store the combinations that admins have specified - otherwise, the defaults will be applied. This is just easier to maintain and keeps the junction table small and efficient.
So I've built the following queries to return the set of attributes for each possible combination of category and sub-category.
First, a query to (dynamically) produce all the possible combinations (I have to do this as a separate query otherwise I can't do the joins later...) Call this
qryCombinations
:
Code:
SELECT C.CategoryID, SC.SubCategoryID
FROM tblCategories C, tblSubCategories SC
Now, a second query to determine the attributes for each combination, based on whether the combination exists in the junction table and, if not, using the defaults as per the main tables :
Code:
SELECT qC.CategoryID, qC.SubCategoryID, Nz(jE.Selectable, SC.Active) AS IsSelectable, Nz(jE.Mandatory, False) AS IsMandatory
FROM (qryCombinations qC
INNER JOIN tblSubCategories AS SC ON qC.SubCategoryID = SC.SubCategoryID)
LEFT JOIN jctExceptions AS jE ON (qC.CategoryID = jE.CategoryID) AND (qC.SubCategoryID = jE.SubCategoryID)
So basically, return all the category/sub-category combinations, and for any combination that exists in the junction table, use the values in that table to determine whether the sub-category is selectable and/or mandatory for that category, otherwise selectability is based on whether the sub-category is active or not and mandatory is by default no.
I have deliberately left the junction table empty for now (so there are no records in it whatsoever)
Here's where the weirdness starts...
If I run that query directly from Query Design, it returns everything exactly as I would expect (i.e. all combinations present, all with IsSelectable = True and IsMandatory = False - which is what you would expect with an empty junction table, i.e. no exceptions)
CategoryID | SubCategoryID | IsSelectable | IsMandatory |
---|---|---|---|
1 | 1 | -1 | 0 |
1 | 2 | -1 | 0 |
1 | 3 | -1 | 0 |
2 | 1 | -1 | 0 |
2 | 2 | -1 | 0 |
2 | 3 | -1 | 0 |
3 | 1 | -1 | 0 |
But when I then use that query as the data source for a subform, to present the results visually on a form, the results are different?
I can see all the IDs fine but the checkboxes to represent IsSelectable and IsMandatory are all False? So the query is returning True values across the board for IsSelectable, but the checkboxes on the subform which are bound to the IsSelectable field in the query, are all unchecked?
If I add a record to the junction table for an arbitrary combination, the values as per the junction table for that specific combination do appear correctly in the checkboxes (and, obviously, in the underlying query itself) but any boolean values not represented in the junction table, appear False.
So - I'm really puzzled as to why the results from the query are not being represented in the subform, and I'm not sure if it's a problem with the query, or the subform? Given that the query appears to run perfectly fine in isolation?
(Side note - I am well aware that this query is, more than likely, non-updateable, given the joins, and thus the subform itself could not be used to make direct modifications - I am well aware of that, and indeed comfortable with it; the purpose of the subform is purely to present the current state visually and I will have separate controls for making modifications to the junction table as/when needed...)
Any and all suggestions as to what exactly is going on would be extremely gratefully received as I am at a loss as to why this isn't working the way I intended!
Thanks!