Union Query

B

broncojim

I am trying to combine the results of several different select queries. Two
of the select queries are listed below. The problem seems to be with the
[DEPR YEARS] field. I get an error saying "data type mismatch in criteria
expression" for the union query, but not the select queries.

SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE,
Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year],
ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC,
Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST],
IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS
[ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY
YEAR])/[LIFE YEARS]>1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]<=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS],
[DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF
INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE]
FROM ARCFM_CAPACITORBANK
GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE,
Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'),
ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30,
Year([ARCFM_CAPACITORBANK].[INSTALLDATE])
UNION ALL
SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE,
Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year],
ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC,
Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST],
IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS],
IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]>1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]<=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS],
[DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF
INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE]
FROM ARCFM_CONDUIT
GROUP BY ARCFM_CONDUIT.SUBTYPECODE,
Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER,
ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30,
Year([ARCFM_CONDUIT].[INSTALLDATE])
HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2"));
 
G

Golfinray

All of the depr years in the tables feeding the queries need to be the same
data type, probably date.
 
B

broncojim

This could be the problem, but I'm not sure. The depr years are fields that
are created inside the selection queries. They do not exist in the main
tables. However, since this field is doing the same thing and has the same
wording in each selection query, I simply copied and pasted the field from
the first selection query to the others. Any ideas?



Golfinray said:
All of the depr years in the tables feeding the queries need to be the same
data type, probably date.

broncojim said:
I am trying to combine the results of several different select queries. Two
of the select queries are listed below. The problem seems to be with the
[DEPR YEARS] field. I get an error saying "data type mismatch in criteria
expression" for the union query, but not the select queries.

SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE,
Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year],
ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC,
Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST],
IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS
[ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY
YEAR])/[LIFE YEARS]>1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]<=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS],
[DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF
INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE]
FROM ARCFM_CAPACITORBANK
GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE,
Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'),
ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30,
Year([ARCFM_CAPACITORBANK].[INSTALLDATE])
UNION ALL
SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE,
Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year],
ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC,
Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST],
IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS],
IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]>1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]<=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS],
[DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF
INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE]
FROM ARCFM_CONDUIT
GROUP BY ARCFM_CONDUIT.SUBTYPECODE,
Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER,
ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30,
Year([ARCFM_CONDUIT].[INSTALLDATE])
HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2"));
Click to expand...
Click to expand...
 
K

KARL DEWEY

You should not use an aliase ( [DEPR YEARS] ) within the same query that
creates it as the query may try to use it before it is created.
--
KARL DEWEY
Build a little - Test a little


broncojim said:
This could be the problem, but I'm not sure. The depr years are fields that
are created inside the selection queries. They do not exist in the main
tables. However, since this field is doing the same thing and has the same
wording in each selection query, I simply copied and pasted the field from
the first selection query to the others. Any ideas?



Golfinray said:
All of the depr years in the tables feeding the queries need to be the same
data type, probably date.

broncojim said:
I am trying to combine the results of several different select queries. Two
of the select queries are listed below. The problem seems to be with the
[DEPR YEARS] field. I get an error saying "data type mismatch in criteria
expression" for the union query, but not the select queries.

SELECT DISTINCTROW ARCFM_CAPACITORBANK.SUBTYPECODE,
Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year],
ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC,
Sum(ARCFM_CAPACITORBANK.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST],
IIf([SUBTYPECODE]="1","CAPACITOR",IIf([SUBTYPECODE]="2","CAPACITOR")) AS
[ASSET TYPE], 30 AS [LIFE YEARS], IIf((Year(Now())-[INSTALLDATE BY
YEAR])/[LIFE YEARS]>1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]<=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS],
[DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF
INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE]
FROM ARCFM_CAPACITORBANK
GROUP BY ARCFM_CAPACITORBANK.SUBTYPECODE,
Format$([ARCFM_CAPACITORBANK].[INSTALLDATE],'yyyy'),
ARCFM_CAPACITORBANK.PROJECTNUMBER, ARCFM_CAPACITORBANK.FERC, 30,
Year([ARCFM_CAPACITORBANK].[INSTALLDATE])
UNION ALL
SELECT DISTINCTROW ARCFM_CONDUIT.SUBTYPECODE,
Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy') AS [INSTALLDATE By Year],
ARCFM_CONDUIT.PROJECTNUMBER, ARCFM_CONDUIT.FERC,
Sum(ARCFM_CONDUIT.INSTALLEDCOST) AS [Sum Of INSTALLEDCOST],
IIf([SUBTYPECODE]="2","CONDUIT") AS [ASSET TYPE], 30 AS [LIFE YEARS],
IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]>1,0,IIf((Year(Now())-[INSTALLDATE BY YEAR])/[LIFE
YEARS]<=1,(Year(Now())-[INSTALLDATE BY YEAR])/[LIFE YEARS])) AS [DEPR YEARS],
[DEPR YEARS]*[SUM OF INSTALLEDCOST] AS [ACCUM DEPR], [SUM OF
INSTALLEDCOST]-[ACCUM DEPR] AS [NET BOOK VALUE]
FROM ARCFM_CONDUIT
GROUP BY ARCFM_CONDUIT.SUBTYPECODE,
Format$([ARCFM_CONDUIT].[INSTALLDATE],'yyyy'), ARCFM_CONDUIT.PROJECTNUMBER,
ARCFM_CONDUIT.FERC, IIf([SUBTYPECODE]="2","CONDUIT"), 30,
Year([ARCFM_CONDUIT].[INSTALLDATE])
HAVING (((ARCFM_CONDUIT.SUBTYPECODE)="2"));
Click to expand...
Click to expand...
Click to expand...
 

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

union query problem 16
System Resource Exceeded/Query Too Complex 4
Union Query Truncates Results? 2
UNION query taking 5 minutes for approx 2000 records - any tune uptips? 1
"Query too complex" workaround 4
Union query - sort of 4
Union query and pivot tables 2
UNION Query problem 4

Top