query error

[Guest]

hi,

I have the following:
SELECT DISTINCT Tbl_MasterPop.Status, Count(Tbl_MasterPop.Status) AS
CountOfstatus, Sum(Tbl_MasterPop.[Principal Balance]) AS [SumOfprincipal
balance], Avg(Tbl_MasterPop.NETBAL) AS AvgOfNETBAL, Count(Tbl_MasterPop.[New
Institution Name]) AS [CountOfNew Institution Name]
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.Status) Not In
('CHARGEOFF','PAIDOFF','CURRENT_AMD/DEFR','CURRENT_PAID','CURRENT_BKAMD/DEF','REPO')))
GROUP BY Tbl_MasterPop.Status
ORDER BY Tbl_MasterPop.Status
UNION ALL
SELECT tbl_resolved.PrevStatus, *
FROM tbl_resolved
WHERE (((tbl_resolved.PopEnterDt)=(SELECT min(popenterdt)
FROM tbl_resolved
WHERE tbl_masterpop.[loan acct #] = tbl_resolved.[loan acct #])) AND
((tbl_resolved.PrevStatus)="new"));

however, I am getting an error message:
"the number of columns in the two selected tables or queries of a union
query do not match."

I do not want to list all the fields of the tbl_masterpop. all i want to do
is combine the results of both tables. what can i do differently or what am
i doing wrong?

thanks in advance,
geebee

 

[Tom Ellison]

Dear GB:

The lists of columns in all the parts of a UNION must match in number. They
need also to match in type.

You can put nulls in the columns you must add to one or another of the
queries so the number of columns match.

Logically, the columns should also match in datatype. If they do not, what
is the sense of having such a column? What use will it be?

Tom Ellison

 

[Guest]

hi,

ok. i listed the columns separately as follows:

SELECT DISTINCT Tbl_MasterPop.Status, Count(Tbl_MasterPop.Status) AS
CountOfstatus, Sum(Tbl_MasterPop.[Principal Balance]) AS [SumOfprincipal
balance], Avg(Tbl_MasterPop.NETBAL) AS AvgOfNETBAL, Count(Tbl_MasterPop.[New
Institution Name]) AS [CountOfNew Institution Name]
FROM Tbl_MasterPop
GROUP BY Tbl_MasterPop.Status
ORDER BY Tbl_MasterPop.Status
UNION ALL
SELECT Tbl_resolved.resolvedstatus, Count(Tbl_resolved.resolvedStatus) AS
CountOfstatus2, Sum(Tbl_resolved.[Principal Balance]) AS [SumOfprincipal
balance2], Avg(Tbl_resolved.NETBAL) AS AvgOfNETBAL2, Count(Tbl_resolved.[New
Institution Name]) AS [CountOfNew Institution Name2]
FROM tbl_resolved
WHERE (((tbl_resolved.PopEnterDt)=(SELECT min(popenterdt)
FROM tbl_resolved
WHERE tbl_masterpop.[loan acct #] = tbl_resolved.[loan acct #])) AND
((tbl_resolved.PrevStatus)="new"));

now I am getting the following error:
"you tried to execute a query that does not include the specified expression
'Status' as part of an agregate function.

im not sure what i should do now.

thanks in advance,
geebee

Dear GB:

The lists of columns in all the parts of a UNION must match in number. They
need also to match in type.

You can put nulls in the columns you must add to one or another of the
queries so the number of columns match.

Logically, the columns should also match in datatype. If they do not, what
is the sense of having such a column? What use will it be?

Tom Ellison

hi,

I have the following:
SELECT DISTINCT Tbl_MasterPop.Status, Count(Tbl_MasterPop.Status) AS
CountOfstatus, Sum(Tbl_MasterPop.[Principal Balance]) AS [SumOfprincipal
balance], Avg(Tbl_MasterPop.NETBAL) AS AvgOfNETBAL,
Count(Tbl_MasterPop.[New
Institution Name]) AS [CountOfNew Institution Name]
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.Status) Not In
('CHARGEOFF','PAIDOFF','CURRENT_AMD/DEFR','CURRENT_PAID','CURRENT_BKAMD/DEF','REPO')))
GROUP BY Tbl_MasterPop.Status
ORDER BY Tbl_MasterPop.Status
UNION ALL
SELECT tbl_resolved.PrevStatus, *
FROM tbl_resolved
WHERE (((tbl_resolved.PopEnterDt)=(SELECT min(popenterdt)
FROM tbl_resolved
WHERE tbl_masterpop.[loan acct #] = tbl_resolved.[loan acct #])) AND
((tbl_resolved.PrevStatus)="new"));

however, I am getting an error message:
"the number of columns in the two selected tables or queries of a union
query do not match."

I do not want to list all the fields of the tbl_masterpop. all i want to
do
is combine the results of both tables. what can i do differently or what
am
i doing wrong?

thanks in advance,
geebee

 

[Tom Ellison]

Dear GB:

The two SELECT queries must each be workable.

The second one is missing GROUP BY resolvedstatus. I cannot say whether
this is the only problem. Put the above just before the semi-colon at the
end of your SQL code.

Tom Ellison

hi,

ok. i listed the columns separately as follows:

SELECT DISTINCT Tbl_MasterPop.Status, Count(Tbl_MasterPop.Status) AS
CountOfstatus, Sum(Tbl_MasterPop.[Principal Balance]) AS [SumOfprincipal
balance], Avg(Tbl_MasterPop.NETBAL) AS AvgOfNETBAL,
Count(Tbl_MasterPop.[New
Institution Name]) AS [CountOfNew Institution Name]
FROM Tbl_MasterPop
GROUP BY Tbl_MasterPop.Status
ORDER BY Tbl_MasterPop.Status
UNION ALL
SELECT Tbl_resolved.resolvedstatus, Count(Tbl_resolved.resolvedStatus) AS
CountOfstatus2, Sum(Tbl_resolved.[Principal Balance]) AS [SumOfprincipal
balance2], Avg(Tbl_resolved.NETBAL) AS AvgOfNETBAL2,
Count(Tbl_resolved.[New
Institution Name]) AS [CountOfNew Institution Name2]
FROM tbl_resolved
WHERE (((tbl_resolved.PopEnterDt)=(SELECT min(popenterdt)
FROM tbl_resolved
WHERE tbl_masterpop.[loan acct #] = tbl_resolved.[loan acct #])) AND
((tbl_resolved.PrevStatus)="new"));

now I am getting the following error:
"you tried to execute a query that does not include the specified
expression
'Status' as part of an agregate function.

im not sure what i should do now.

thanks in advance,
geebee

Dear GB:

The lists of columns in all the parts of a UNION must match in number.
They
need also to match in type.

You can put nulls in the columns you must add to one or another of the
queries so the number of columns match.

Logically, the columns should also match in datatype. If they do not,
what
is the sense of having such a column? What use will it be?

Tom Ellison

hi,

I have the following:
SELECT DISTINCT Tbl_MasterPop.Status, Count(Tbl_MasterPop.Status) AS
CountOfstatus, Sum(Tbl_MasterPop.[Principal Balance]) AS
[SumOfprincipal
balance], Avg(Tbl_MasterPop.NETBAL) AS AvgOfNETBAL,
Count(Tbl_MasterPop.[New
Institution Name]) AS [CountOfNew Institution Name]
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.Status) Not In
('CHARGEOFF','PAIDOFF','CURRENT_AMD/DEFR','CURRENT_PAID','CURRENT_BKAMD/DEF','REPO')))
GROUP BY Tbl_MasterPop.Status
ORDER BY Tbl_MasterPop.Status
UNION ALL
SELECT tbl_resolved.PrevStatus, *
FROM tbl_resolved
WHERE (((tbl_resolved.PopEnterDt)=(SELECT min(popenterdt)
FROM tbl_resolved
WHERE tbl_masterpop.[loan acct #] = tbl_resolved.[loan acct #]))
AND
((tbl_resolved.PrevStatus)="new"));

however, I am getting an error message:
"the number of columns in the two selected tables or queries of a union
query do not match."

I do not want to list all the fields of the tbl_masterpop. all i want
to
do
is combine the results of both tables. what can i do differently or
what
am
i doing wrong?

thanks in advance,
geebee