Show records even with NULL data? WHERE...IN?

[cjg.groups]

Hello, is there an easy way to force many types of queries to show all
records in a set, even if their fields have no data? I am using
multiple queries to populate a single Word table, so the data in each
row must be for the same entity, even if there are blanks.

For example, query1 does AVG to produce:
id avgs
1 3300
2 4400
4 9900

query2 is a cross-tab doing SUM to produce:
id sums
2 56
4 60
7 71

I use VB to populate a single Word table in two steps, one query at a
time, to get:
id avgs sums
1 3300
2 4400 56
4 9900 60
7 71

This would be much easier if query1 and query2 would show a record for
each id in the complete set. Currently, I LEFT JOIN the actual
queries to a query of all id's. This isn't working when my cross-tab
returns an empty set. It would be fine if the cross-tab returned a
set of all id's, even with NULL in the other fields.

I thought you could do:
WHERE <conditionals> IN [SELECT id FROM table WHERE <conditionals>]
to get a consistent list of id's to display.

Any easier methods would be appreciated. Thanks!

 

[KARL DEWEY]

Create a union query of 1 and 2 --
SELECT ID
FROM Query1
UNION SELECT ID
FROM Query2; Save as ID_All.

Then this --
Select ID_All.ID, avgs, sums
FROM (ID_All LEFT JOIN Query1 ON ID_All.ID = Query1.ID) LEFT JOIN Query2 ON
ID_All.ID = Query2.ID;

 

[cjg.groups]

Thank you. That works, but has the same problem that my current
queries have:

One of the queries aggregated by the LEFT JOIN is a cross-tab. When
that cross-tab query returns an empty recorset, the LEFT JOIN query
fails with the error:
"The Microsoft Jet database engine does not recognize 'joinquery' as a
valid field name or expression."

That's why I'm trying to force the cross-tab query to show a record
for each id in the set, even if it has no data. Since LEFT JOIN can't
do it, I'm hoping WHERE...IN can.

Specifically, can I force this complete id column into a cross-tab
even when the cross-tab query result has no data at all? Thank you.

Create a union query of 1 and 2 --
SELECT ID
FROM Query1
UNION SELECT ID
FROM Query2;          Save as ID_All.

Then this --
Select ID_All.ID, avgs, sums
FROM (ID_All LEFT JOIN Query1 ON ID_All.ID = Query1.ID) LEFT JOIN Query2 ON
ID_All.ID = Query2.ID;

--
KARL DEWEY
Build a little - Test a little

Hello, is there an easy way to force many types of queries to show all
records in a set, even if their fields have no data?  I am using
multiple queries to populate a single Word table, so the data in each
row must be for the same entity, even if there are blanks.

For example, query1 does AVG to produce:
id   avgs
1    3300
2    4400
4    9900

query2 is a cross-tab doing SUM to produce:
id   sums
2    56
4    60
7    71

I use VB to populate a single Word table in two steps, one query at a
time, to get:
id   avgs   sums
1    3300
2    4400   56
4    9900   60
7              71

This would be much easier if query1 and query2 would show a record for
each id in the complete set.  Currently, I LEFT JOIN the actual
queries to a query of all id's.  This isn't working when my cross-tab
returns an empty set.  It would be fine if the cross-tab returned a
set of all id's, even with NULL in the other fields.

I thought you could do:
WHERE <conditionals> IN [SELECT id FROM table WHERE <conditionals>]
to get a consistent list of id's to display.

Any easier methods would be appreciated.  Thanks!

 

[KARL DEWEY]

PIVOT .... IN("X", "Y", "Z"); makes a crosstab query return the specified
columns of the IN function.

--
KARL DEWEY
Build a little - Test a little

Thank you. That works, but has the same problem that my current
queries have:

One of the queries aggregated by the LEFT JOIN is a cross-tab. When
that cross-tab query returns an empty recorset, the LEFT JOIN query
fails with the error:
"The Microsoft Jet database engine does not recognize 'joinquery' as a
valid field name or expression."

That's why I'm trying to force the cross-tab query to show a record
for each id in the set, even if it has no data. Since LEFT JOIN can't
do it, I'm hoping WHERE...IN can.

Specifically, can I force this complete id column into a cross-tab
even when the cross-tab query result has no data at all? Thank you.

Create a union query of 1 and 2 --
SELECT ID
FROM Query1
UNION SELECT ID
FROM Query2; Save as ID_All.

Then this --
Select ID_All.ID, avgs, sums
FROM (ID_All LEFT JOIN Query1 ON ID_All.ID = Query1.ID) LEFT JOIN Query2 ON
ID_All.ID = Query2.ID;

--
KARL DEWEY
Build a little - Test a little

Hello, is there an easy way to force many types of queries to show all
records in a set, even if their fields have no data? I am using
multiple queries to populate a single Word table, so the data in each
row must be for the same entity, even if there are blanks.

For example, query1 does AVG to produce:
id avgs
1 3300
2 4400
4 9900

query2 is a cross-tab doing SUM to produce:
id sums
2 56
4 60
7 71

I use VB to populate a single Word table in two steps, one query at a
time, to get:
id avgs sums
1 3300
2 4400 56
4 9900 60
7 71

This would be much easier if query1 and query2 would show a record for
each id in the complete set. Currently, I LEFT JOIN the actual
queries to a query of all id's. This isn't working when my cross-tab
returns an empty set. It would be fine if the cross-tab returned a
set of all id's, even with NULL in the other fields.

I thought you could do:
WHERE <conditionals> IN [SELECT id FROM table WHERE <conditionals>]
to get a consistent list of id's to display.

Any easier methods would be appreciated. Thanks!

 

[cjg.groups]

Your solution helps, even though the goal is to return the specified
records instead of columns.

The problem was: The right table in a LEFT JOIN was a cross-tab that
sometimes returned no records. When it returned no records, the JOIN
query wouldn't run, probably because the PIVOT part of the cross-tab
was not displaying the fields that the JOIN query was asking for.

Karl's solution forces those cross-tab fields to exist even when there
are no records. This satisfies the parent JOIN query and this runs
without a problem.

Another note, for anyone as uninformed as I was: When a cross-tab
returns a field called <>, that's where it puts data whose PIVOT field
is null. ie: PIVOT uses the YrHr field which contains "Year" or
"Hour", thus the cross-tab creates a "Year" and "Hour" column. But
when a record has NULL in YrHr, the cross-tab puts that record's data
in <>. Please correct me if I'm wrong on this.

Drilling back up to topic, the reason for the LEFT JOIN was to force a
certain set of records to show even if the query didn't have data for
all records. The query on the left of the LEFT JOIN was a simply
SELECT of the id field for all the records I wanted to display. It
was the SELECT pulling from this JOIN that wanted fields which
sometimes didn't exist in the cross-tab, but now I force them to
always exist.

Thanks for your help.

PIVOT .... IN("X", "Y", "Z");   makes a crosstab query return the specified
columns of the IN function.

--
KARL DEWEY
Build a little - Test a little

Thank you.  That works, but has the same problem that my current
queries have:

One of the queries aggregated by the LEFT JOIN is a cross-tab.  When
that cross-tab query returns an empty recorset, the LEFT JOIN query
fails with the error:
"The Microsoft Jet database engine does not recognize 'joinquery' as a
valid field name or expression."

That's why I'm trying to force the cross-tab query to show a record
for each id in the set, even if it has no data.  Since LEFT JOIN can't
do it, I'm hoping WHERE...IN can.

Specifically, can I force this complete id column into a cross-tab
even when the cross-tab query result has no data at all?  Thank you.

Create a union query of 1 and 2 --
SELECT ID
FROM Query1
UNION SELECT ID
FROM Query2;          Save as ID_All.
Then this --
Select ID_All.ID, avgs, sums
FROM (ID_All LEFT JOIN Query1 ON ID_All.ID = Query1.ID) LEFT JOIN Query2 ON
ID_All.ID = Query2.ID;
--
KARL DEWEY
Build a little - Test a little
:
Hello, is there an easy way to force many types of queries to show all
records in a set, even if their fields have no data?  I am using
multiple queries to populate a single Word table, so the data in each
row must be for the same entity, even if there are blanks.
For example, query1 does AVG to produce:
id   avgs
1    3300
2    4400
4    9900
query2 is a cross-tab doing SUM to produce:
id   sums
2    56
4    60
7    71
I use VB to populate a single Word table in two steps, one query ata
time, to get:
id   avgs   sums
1    3300
2    4400   56
4    9900   60
7              71
This would be much easier if query1 and query2 would show a record for
each id in the complete set.  Currently, I LEFT JOIN the actual
queries to a query of all id's.  This isn't working when my cross-tab
returns an empty set.  It would be fine if the cross-tab returneda
set of all id's, even with NULL in the other fields.
I thought you could do:
WHERE <conditionals> IN [SELECT id FROM table WHERE <conditionals>]
to get a consistent list of id's to display.
Any easier methods would be appreciated.  Thanks!