Number of columns error

[John]

Hi All,

First Happy New Year to all,

Second: I have a query that was working fine. The database admin
changed one of the fields in a linked table from number to type string.
After refreshing the link I get this error:
"The number of columns in the two selected tables or queries of a union
do not match"

I have also lost the design view on the query. It gives an error
stating it can not show the design view because the fields have
changed.
Here is the query:
SELECT tmp.PC_ID, tmp.ClientAFE, tmp.PENumber,
dbo_tlkpClientName.ClientName, [ProjectTitle] & ' ' & [Project_Abb] AS
[Desc], tmp.ClientNameID, dbo_tlkpStatus.Status, tmp.ProjectTitle,
tmp.ProjectTypeID, tmp.Table_Name INTO tblPC_update
FROM ((([SELECT 'PC' as Table_Name, PC.* FROM DBO_PROJECTCONTROL AS PC

UNION ALL SELECT 'tblPC' as Table_Name, tblPC.* FROM tblPC
as tblPC]. AS tmp INNER JOIN dbo_tlkpClientName ON tmp.ClientNameID =
dbo_tlkpClientName.CLientNameID) INNER JOIN dbo_tlkpProjectType ON
tmp.ProjectTypeID = dbo_tlkpProjectType.ProjectTypeID) INNER JOIN
dbo_tlkpProjectArea ON tmp.PAreaID = dbo_tlkpProjectArea.PAreaID) INNER
JOIN dbo_tlkpStatus ON tmp.Status_ID = dbo_tlkpStatus.Status_ID
GROUP BY tmp.PC_ID, tmp.ClientAFE, tmp.PENumber,
dbo_tlkpClientName.ClientName, [ProjectTitle] & ' ' & [Project_Abb],
tmp.ClientNameID, dbo_tlkpStatus.Status, tmp.ProjectTitle,
tmp.ProjectTypeID, tmp.Table_Name
ORDER BY tmp.PC_ID, tmp.Table_Name;

Before this when all was working in design view I had the temp table
showing up with fields in it. Now there are no fields in the temp
table. It looks like an empty table.

Third: thanks for taking the time to help me.

 

[Michel Walsh]

Hi,


Have you checked to see if

SELECT 'PC' as Table_Name, PC.* FROM DBO_PROJECTCONTROL


and


SELECT 'tblPC' as Table_Name, tblPC.* FROM tblPC


indeed, return the same number of columns? Maybe you change one of these two
tables recently, by adding or removing a column and now, it is impossible to
'match' the two SELECTs.



Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

Second: I have a query that was working fine. The database admin
changed one of the fields in a linked table from number to type string.
After refreshing the link I get this error:
"The number of columns in the two selected tables or queries of a union
do not match"

In a UNION query you need to match not only the number of columns, but
their datatypes as well. It seems that you now have a Text field in
this table, and you're trying to UNION it with a number field in the
other query of the UNION. You'll need to make the corresponding
change, either permanently in the table design, or by using CStr() to
convert the number to text.

John W. Vinson[MVP]

 

[John]

Hi Guys,

Thank You Vanderghast and Thanks John. As it turns out you are both
right. There was an extra field added and the field was changed in only
one table, not both. Thnaks for your help and time.

John