Solved Continuous subform displaying boolean results differently to underlying query

[AOB]

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.

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 :

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 :

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!

 

[ebs17]

If I understand this correctly, then the problem here lies in information that is too much and incorrect in terms of content.

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)
WHERE jE.CategoryID IS NULL

Change to Nz(jE.Selectable, False) AS IsSelectable
The INNER JOIN has to go.
The WHERE clause needs to be added.

If you want to take a status Active from the master tables, you would have to include it in the first query (CROSS JOIN).

 

[arnelgp]

Nz() function returns a Variant type, so convert it to Numeric:

SELECT qC.CategoryID, qC.SubCategoryID, Val(Nz(jE.Selectable, SC.Active)) AS IsSelectable,
Val(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)

then on Design view, select one by one the columns IsSelectable and IsMandatory.
and on Property Window->Format:

Yes/No

 

[ebs17]

SELECT
   C.CategoryID,
   SC.SubCategoryID
FROM
   (
      SELECT
         CategoryID
      FROM
         tblCategories
      WHERE
         Active = True
   ) AS C,
   (
      SELECT
         SubCategoryID
      FROM
         tblCategories
      WHERE
         Active = True
   ) AS SC

SELECT
   qC.CategoryID,
   qC.SubCategoryID,
   Nz(jE.Selectable, False) AS IsSelectable,
   Nz(jE.Mandatory, False) AS IsMandatory
FROM
   qryCombinations AS qC
      LEFT JOIN jctExceptions AS jE
      ON qC.CategoryID = jE.CategoryID
         AND
      qC.SubCategoryID = jE.SubCategoryID
WHERE
   jE.CategoryID IS NULL

Or with additional display of Active from both master tables (as an overall query)

SELECT
   qC.CategoryID,
   qC.cActive,
   qC.SubCategoryID,
   qC.scActive,
   Nz(jE.Selectable, False) AS IsSelectable,
   Nz(jE.Mandatory, False) AS IsMandatory
FROM
   (
      SELECT
         C.CategoryID,
         C.Active AS cActive,
         SC.SubCategoryID,
         SC.Active AS scActive
      FROM
         (
            SELECT
               CategoryID,
               Active
            FROM
               tblCategories
         ) AS C,
         (
            SELECT
               SubCategoryID,
               Active
            FROM
               tblCategories
         ) AS SC) AS qC
            LEFT JOIN jctExceptions AS jE
            ON qC.CategoryID = jE.CategoryID
               AND
            qC.SubCategoryID = jE.SubCategoryID
WHERE
   jE.CategoryID IS NULL

 

[AOB]

If I understand this correctly, then the problem here lies in information that is too much and incorrect in terms of content.

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)
WHERE jE.CategoryID IS NULL

Change to Nz(jE.Selectable, False) AS IsSelectable
The INNER JOIN has to go.
The WHERE clause needs to be added.

If you want to take a status Active from the master tables, you would have to include it in the first query (CROSS JOIN).

Thanks @ebs17 I do take your point on the superfluous INNER JOIN and have moved those details into the initial "combo" query thus :

SELECT C.CategoryID, SC.SubCategoryID, SC.Active AS SubCategoryActive
FROM tblCategories C, tblSubCategories SC

However, to your two other observations, I suspect my explanation may not have been clear...

Change to Nz(jE.Selectable, False) AS IsSelectable

I can't do this because this says, if jE.Selectable is null (i.e. no corresponding entry in the junction table) then set this to False. That is not the intention. It should be, if jE.Selectable is null, then whether or not the sub-category is selectable should be determined based on whether it is Active or not. So if jE.Selectable is null, the result could still be True or False depending on the Active flag in the SubCategory table.

WHERE jE.CategoryID IS NULL

I can't do this either because this will only return the results which don't have corresponding entries in the junction table. That is not the intention either. It should return all combinations, irrespective of whether that combination exists in the junction table or not. The junction table merely serves to override the default settings. If the junction table is empty, this query returns no results whatsoever?

 

[ebs17]

True, the WHERE clause is wrong, my mistake.

As the note before: Of course you have to implement your desired logic, I wasn't aware of this.

 

[AOB]

Nz() function returns a Variant type, so convert it to Numeric:

SELECT qC.CategoryID, qC.SubCategoryID, Val(Nz(jE.Selectable, SC.Active)) AS IsSelectable,
Val(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)

then on Design view, select one by one the columns IsSelectable and IsMandatory.
and on Property Window->Format:

Yes/No

View attachment 113087

Thanks @arnelgp , very interesting! However...

I have followed the steps you suggested and modified the query thusly (bearing in mind the suggestions by @ebs17 above to remove the INNER JOIN)

SELECT qC.CategoryID, qC.SubCategoryID, Val(Nz(jE.Selectable, qC.SubCategoryActive)) AS IsSelectable, Val(Nz(jE.Mandatory, False)) AS IsMandatory
FROM qryCombinations qC
LEFT JOIN jctExceptions AS jE ON (qC.CategoryID = jE.CategoryID) AND (qC.SubCategoryID = jE.SubCategoryID)

And set the format of both columns to Yes/No in the Design view (I did not realise you could do this with a calculated column in a query!) as instructed.

So now my query returns something like this :

CategoryID	SubCategoryID	IsSelectable	IsMandatory
1	1	Yes	No
1	2	Yes	No
1	3	Yes	No
2	1	Yes	No
2	2	Yes	No
2	3	Yes	No
3	1	Yes	No

But the same problem persists on the subform (all the "IsSelectable" checkboxes are still unchecked, even though the underlying query suggests they should be checked) All the "IsMandatory" checkboxes are unchecked but I don't know if that's because they're using the true values or suffering the same problem as the other set (I suspect the latter)

One odd thing I noticed - when I was setting the format of the output columns in the Design view, the dropdown option didn't actually offer Yes/No, or True/False, or On/Off as options - it only offered about 5 or 6 - so I had to type "Yes/No" in manually (which it accepted) When I saved the query, closed it and then opened it again, and returned to Design view and went to the Properties sidebar for those columns, I got the full list of standard formatting options, including all the boolean ones. Thought that was a bit strange and now trying to figure out if that's an indicator for something?

And no, selecting those options from the dropdown at this point didn't have any impact on the subform problem...

 

[AOB]

Figured it out...

The subform property Recordset Type was set to Snapshot.

Changing this to Dynaset somehow made the results in the subform, match the results in the actual query. Working perfectly now.

I'm not entirely sure why this is relevant here, given that the data is static. I can't reconcile why a Dynaset recordset evaluates correctly whereas a Snapshot recordset, doesn't (or, at least, only partially - the boolean values being the only fields that weren't appearing correctly)

I'm going to mark the thread as solved and highlight this post as the answer but I think there's lots of value in what both @ebs17 and @arnelgp have suggested above, so anybody else with a similar issue would be well served by reading their respective responses.

Would also be fascinated to understand why on earth Snapshot vs Dynaset has this kind of impact on this kind of "static" query / subform (I can see the relevance elsewhere, but not here?)