Union Query SQL

C

croy

This Union query has me stumped. The SQL for each of the
two querys runs fine on their own, but together in this
union the query runs, shows the first screen of data, and
then dumps "Data mismatch" in a dialog. I'm beginning to
suspect that the problem may not be data mismatch, but I
can't figure out what it is.

Here's the SQL (badly wrapped):

SELECT
IIf([GeoLocDE]="10.1","10.1",IIf([GeoLocDE]="11","11",IIf([GeoLocDE]="11.1","11",IIf([GeoLocDE]="11.2","11",IIf([GeoLocDE]="12.1","12.1",Int([GeoLocDE]))))))
AS Loc, tblIvSurv.SurveyDate, tblIvDetail.RiverMile,
tblMethod.MethDE, tblIvDetail.NumberAnglers,
tblIvDetail.HrsFished, tblIvDetail.SpeciesId AS Target,
tblIvDetail.Done, tblIvDetail.ZipCode,
IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE])))))
AS Spp, tblCatch.Kept, tblCatch.Released
FROM tblRiver INNER JOIN (tblMethod INNER JOIN (tblSpecies
RIGHT JOIN (((tblGeoLoc INNER JOIN tblIvSurv ON
tblGeoLoc.GeoLocId = tblIvSurv.GeoLocId) INNER JOIN
tblIvDetail ON tblIvSurv.IvSurvId = tblIvDetail.IvSurvId)
LEFT JOIN tblCatch ON tblIvDetail.IvDetId =
tblCatch.IvDetId) ON tblSpecies.SpeciesId =
tblCatch.SpeciesId) ON tblMethod.MethodId =
tblIvDetail.MethodId) ON tblRiver.RivId = tblGeoLoc.RivId
WHERE (((tblIvSurv.SurveyDate)<#7/1/2009#) AND
((IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="CS"
Or
(IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="RT"
Or
(IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="SH"
Or
(IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="SB"
Or
(IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="AS"
Or
(IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="ST"
Or
(IIf([SpeciesDE]="SHCL","SH",IIf([SpeciesDE]="SHU","SH",IIf([SpeciesDE]="RTCL","RT",IIf([SpeciesDE]="RTU","RT",IIf(IsNull([SpeciesDE]),"--",[SpeciesDE]))))))="--")
AND ((tblIvSurv.IvTypeId)=1) AND ((tblRiver.RiverDE)="SAC"
Or (tblRiver.RiverDE)="AM" Or (tblRiver.RiverDE)="FR"))


UNION ALL SELECT PhysDat.Section, PhysDat.SurveyDate,
IvDat.RiverMile, IIf([Method]="B","1","2") AS Mth,
IvDat.NumberAnglers, IvDat.HrsFished,
IIf([TargetSpecies]="KS","CS",[TargetSpecies]) AS Tgt,
IvDat.Done, IvDat.ZipCode,
IIf([OrganismCode]="KS","CS",[OrganismCode]) AS Spp,
nz([Kept],0) AS K, nz([Released],0) AS Rel
FROM PhysDat LEFT JOIN (IvDat LEFT JOIN CatchNum ON
IvDat.InterviewNumRowID = CatchNum.InterviewNumRowID) ON
PhysDat.PhysDatId = IvDat.PhysDatId
WHERE (((PhysDat.Section)<15) AND
((IIf([OrganismCode]="KS","CS",[OrganismCode]))="CS" Or
(IIf([OrganismCode]="KS","CS",[OrganismCode]))="RT" Or
(IIf([OrganismCode]="KS","CS",[OrganismCode]))="SH" Or
(IIf([OrganismCode]="KS","CS",[OrganismCode]))="SB" Or
(IIf([OrganismCode]="KS","CS",[OrganismCode]))="AS" Or
(IIf([OrganismCode]="KS","CS",[OrganismCode]))="ST"));
 
J

John Spencer

Rewriting the SQL

SELECT
IIf([GeoLocDE]="10.1","10.1"
,IIf([GeoLocDE]="11","11"
,IIf([GeoLocDE]="11.1","11"
,IIf([GeoLocDE]="11.2","11"
,IIf([GeoLocDE]="12.1","12.1"
,Int([GeoLocDE])))))) AS Loc
, tblIvSurv.SurveyDate
, tblIvDetail.RiverMile
, tblMethod.MethDE
, tblIvDetail.NumberAnglers
, tblIvDetail.HrsFished
, tblIvDetail.SpeciesId AS Target
, tblIvDetail.Done
, tblIvDetail.ZipCode
, IIf([SpeciesDE]="SHCL","SH"
,IIf([SpeciesDE]="SHU","SH"
,IIf([SpeciesDE]="RTCL","RT"
,IIf([SpeciesDE]="RTU","RT"
,IIf(IsNull([SpeciesDE]),"--"
,[SpeciesDE]))))) AS Spp
, tblCatch.Kept
, tblCatch.Released
FROM tblRiver INNER JOIN (tblMethod INNER JOIN (tblSpecies
RIGHT JOIN (((tblGeoLoc INNER JOIN tblIvSurv ON
tblGeoLoc.GeoLocId = tblIvSurv.GeoLocId) INNER JOIN
tblIvDetail ON tblIvSurv.IvSurvId = tblIvDetail.IvSurvId)
LEFT JOIN tblCatch ON tblIvDetail.IvDetId =
tblCatch.IvDetId) ON tblSpecies.SpeciesId =
tblCatch.SpeciesId) ON tblMethod.MethodId =
tblIvDetail.MethodId) ON tblRiver.RivId = tblGeoLoc.RivId

WHERE tblIvSurv.SurveyDate<#7/1/2009#
AND
IIf([SpeciesDE]="SHCL","SH"
,IIf([SpeciesDE]="SHU","SH"
,IIf([SpeciesDE]="RTCL","RT"
,IIf([SpeciesDE]="RTU","RT"
,IIf(IsNull([SpeciesDE]),"--",[SpeciesDE])))))
IN ("CS","RT","SH","SB","AS","ST","--")
AND tblIvSurv.IvTypeId=1
AND tblRiver.RiverDE in ("SAC","AM","FR")


UNION ALL
SELECT PhysDat.Section
, PhysDat.SurveyDate
, IvDat.RiverMile
, IIf([Method]="B","1","2") AS Mth
, IvDat.NumberAnglers
, IvDat.HrsFished
, IIf([TargetSpecies]="KS","CS",[TargetSpecies]) AS Tgt
, IvDat.Done
, IvDat.ZipCode
, IIf([OrganismCode]="KS","CS", [OrganismCode]) AS Spp
, nz([Kept],0) AS K
, nz([Released],0) AS Rel
FROM PhysDat LEFT JOIN (IvDat LEFT JOIN CatchNum ON
IvDat.InterviewNumRowID = CatchNum.InterviewNumRowID) ON
PhysDat.PhysDatId = IvDat.PhysDatId

WHERE PhysDat.Section)<15
AND IIf([OrganismCode]="KS","CS",[OrganismCode]) in
("CS","RT","SH","SB","AS","ST")


First possibility:
IIf([GeoLocDE]="10.1","10.1"
,IIf([GeoLocDE]="11","11"
,IIf([GeoLocDE]="11.1","11"
,IIf([GeoLocDE]="11.2","11"
,IIf([GeoLocDE]="12.1","12.1"
,Int([GeoLocDE])))))) AS Loc

This could be generating an error if GeoLocDE is ever contains a value that
cannot be interpreted as a number. For instance, a zero-length string will
cause an error, as well as a value like 10.1.1 or 10-1.

AND this expression is generating a TEXT value. If you want a number value
then you need to strip out the quote marks in the 2nd argument to all those IIF's

IIf([GeoLocDE]="10.1",10.1
,IIf([GeoLocDE]="11",11
,IIf([GeoLocDE]="11.1",11
,IIf([GeoLocDE]="11.2",11
,IIf([GeoLocDE]="12.1",12.1
,Int([GeoLocDE])))))) AS Loc

Is PhysDate.Section a string field or a number field? If it is a number you
have to make the changes recommended.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
C

croy

On Wed, 07 Oct 2009 16:47:19 -0400, John Spencer

[snip]
First possibility:
IIf([GeoLocDE]="10.1","10.1"
,IIf([GeoLocDE]="11","11"
,IIf([GeoLocDE]="11.1","11"
,IIf([GeoLocDE]="11.2","11"
,IIf([GeoLocDE]="12.1","12.1"
,Int([GeoLocDE])))))) AS Loc

This could be generating an error if GeoLocDE is ever contains a value that
cannot be interpreted as a number. For instance, a zero-length string will
cause an error, as well as a value like 10.1.1 or 10-1.

AND this expression is generating a TEXT value. If you want a number value
then you need to strip out the quote marks in the 2nd argument to all those IIF's

IIf([GeoLocDE]="10.1",10.1
,IIf([GeoLocDE]="11",11
,IIf([GeoLocDE]="11.1",11
,IIf([GeoLocDE]="11.2",11
,IIf([GeoLocDE]="12.1",12.1
,Int([GeoLocDE])))))) AS Loc

Is PhysDate.Section a string field or a number field? If it is a number you
have to make the changes recommended.
Click to expand...

PhysDat.Section is a string in the first SELECT, and a
number (single) in the UNION ALL SELECT!

Thank you so much! Off and running.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

stack query from a group query 1
SQL ORDER BY 1
System Resource Exceeded/Query Too Complex 4
Multiple Criteria Query 4
A query based on another query 2
A query based on another queries 1
IIF statement in query criteria to have Like * or Is Null as the result if true 5
Recordset is not updateable after adding a query 7

Top