Re: Union query not working properly if no records exist
From: Tony (anonymous_at_discussions.microsoft.com)
Date: 09/20/04
- Next message: Nikos Yannacopoulos: "Re: Field Properties: Decimal Places - DOES NOT WORK!!!"
- Previous message: Richard: "Field Properties: Decimal Places - DOES NOT WORK!!!"
- In reply to: John Vinson: "Re: Union query not working properly if no records exist"
- Next in thread: John Vinson: "Re: Union query not working properly if no records exist"
- Reply: John Vinson: "Re: Union query not working properly if no records exist"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Sep 2004 03:56:31 -0700
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;
>-----Original Message-----
>On Fri, 17 Sep 2004 02:02:44 -0700, "Tony"
><anonymous@discussions.microsoft.com> wrote:
>
>>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
>.
>
- Next message: Nikos Yannacopoulos: "Re: Field Properties: Decimal Places - DOES NOT WORK!!!"
- Previous message: Richard: "Field Properties: Decimal Places - DOES NOT WORK!!!"
- In reply to: John Vinson: "Re: Union query not working properly if no records exist"
- Next in thread: John Vinson: "Re: Union query not working properly if no records exist"
- Reply: John Vinson: "Re: Union query not working properly if no records exist"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
