Null Group Fields Not Equal to Null Non-Grouped Fields

[Guest]

First query is a select query with multiple tables as input. Primary input
table has text and numeric fields. Text fields are FSTYLE, FCOLOR, GSTYLE,
and GCOLOR. Output uses those same field names. A numeric field,
SeamedStatusValue, is created by the query. Depending on the joined tables,
each row from the primary table may have more than one output row. In this
instance the primary table has 31 rows and the output has 57 rows.

Second query is a select query with the first query as input. It groups on
FSTYLE, FCOLOR, GSTYLE, and GCOLOR, and selects MIN of SeamedStatusValue.
The number of rows output should be (and is) the same as the primary table
used in the first query.

Third query is a select query with the first query and second query as
input. They are joined on FSTYLE, FCOLOR, GSTYLE, GCOLOR, and
SeamedStatusValue using "Only include rows where the joined fields from both
tables are equal."

I expect to get 31 rows from the third query, but I am only getting 29. The
two records that are missing are the ones where GSTYLE and GCOLOR are null in
the primary table. I thought that maybe the grouped fields weren't
evaluating to null, but I tested the first query GSTYLE and GCOLOR for null
and they matched and I tested the second query GSTYLE and GCOLOR for null and
they matched.

I even changed the second query to make a table and then use that table
instead of the second query as input to the third query and came up with the
same results.

I have searched Microsoft Access 97 support and the newsgroup and found
nothing that seems relevant. Can anyone help me?

Thanks in advance,

 

[Ken Snell [MVP]]

Do I understand correctly that your queries are joining the tables using the
fields where a Null value may be present? If yes, a Null value in a joining
field will always mean "no match" and the record with that Null value will
not be selected by the query.

It's possible to use calculated fields in place of the real fields for the
joining, such as:
MyJoinField: Nz(RealFieldName, "AnArbitraryValueThatWon'tMatch")

Then join the tables using the above field and not the real field.

 

[Guest]

Thank you, thank you, thank you!!! I spent hours on this yesterday, reading
and testing. Nothing seemed to work. You have made my morning.

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thank you, thank you, thank you!!! I spent hours on this yesterday,
reading
and testing. Nothing seemed to work. You have made my morning.