Union query not working properly if no records exist

[Tony]

Hi Group,

I am stumped with one and need help.
I have designed a Union query (SQL copied below) to enable
me to produce stats from the major tables in my DB. To
get it to work properly I have to put a test record
(blank) into certain tables for it to work correctly. How
can I get it to work properly if no records exist in some
tables, which is currently the case.

All help appreciated

Tony


'My SQL ----------------------------

SELECT qryTotalICT.Measure, qryTotalICT.[Total
Applications], qryTotalICT.[Total Amount]
FROM qryTotalICT;

UNION ALL SELECT qryTotal17A.Measure, qryTotal17A.[Total
Applications], qryTotal17A.[Total Amount]
FROM qryTotal17A;

UNION ALL SELECT qryTotal19.Measure, qryTotal19.[Total
Applications], qryTotal19.[Total Amount]
FROM qryTotal19;

UNION ALL SELECT qryTotal56B.Measure, qryTotal56B.[Total
Applications], qryTotal56B.[Total Amount]
FROM qryTotal56B;

UNION ALL SELECT qryTotalFocusFarm.Measure,
qryTotalFocusFarm.[Total Applications], qryTotalFocusFarm.
[Total Amount]
FROM qryTotalFocusFarm;

UNION ALL SELECT qryTotalGroup.Measure, qryTotalGroup.
[Total Applications], qryTotalGroup.[Total Amount]
FROM qryTotalGroup;

 

[John Vinson]

I have designed a Union query (SQL copied below) to enable
me to produce stats from the major tables in my DB. To
get it to work properly I have to put a test record
(blank) into certain tables for it to work correctly. How
can I get it to work properly if no records exist in some
tables, which is currently the case.

What is 'not working properly'? What result do you expect, and what
result are you getting?

A UNION query in which one of the SELECT clauses returns no records
"works" in the sense that it returns all the records from all the
other SELECTs; there is no error and no record returned for the empty
recordset. Is this not what you expect or need?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tony]

Hi John,

Thank you for your reply. Apologies for not getting back
sooner...I have just returned to work this morning.
You are correct - I've had a good look at the problem and
the problem itself is not being generated by the union
query (which I had mistakenly thought) but is instead
being generated by a cross tab query. The cross tab pull
together all records for all tables but if no records
exist in a certain table then it doesn't show a column
heading for that table. The union query then fails
to "work" correctly as it cannot retrieve a desired record
count from the offending table.

For example, if no records exist in a table called
tblMeasure56B, the union query returns the following error
problem...

The Microsoft Database engine does not
recognize 'qryAllFarmersAndMeasures'_Crosstab.[5.6B] as a
valid field name or expression.

Is there a workaround for this problem - maybe I am doing
things back-to-front or upside down. I'm not really sure
as fairly new to working with databases.

Grateful for your help

Regards

Tony
BTW, I have copied the SQL of the cross tab just in case
you need to see it.


TRANSFORM Count(qryAllFamersAndMeasures.FarmerID) AS
CountOfFarmerID
SELECT qryAllFamersAndMeasures.Measure,
qryAllFamersAndMeasures.Amount,
qryAllFamersAndMeasures.Surname,
qryAllFamersAndMeasures.FarmerID, Count
(qryAllFamersAndMeasures.FarmerID) AS [Total Of FarmerID]
FROM qryAllFamersAndMeasures
GROUP BY qryAllFamersAndMeasures.Measure,
qryAllFamersAndMeasures.Amount,
qryAllFamersAndMeasures.Surname,
qryAllFamersAndMeasures.FarmerID
PIVOT qryAllFamersAndMeasures.Measure;

 

[John Vinson]

The cross tab pull
together all records for all tables but if no records
exist in a certain table then it doesn't show a column
heading for that table.

List all the desired column headers in the Headers property of the
crosstab query (right mouseclick the table background in design mode).
This will force the existance of a column even if it has no data.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tony]

Thank you John, working fine now.
I never knew you could do that - have learnt something new
here.

Thanks again

Tony