Link to home
Start Free TrialLog in
Avatar of GIStewart
GIStewart

asked on

Modifying a query to account for spaces in field names

I have a query borrowed from Jeff Smith for comparing 2 tables in SQL server, and I'm now trying to adapt it for field names in a table (that I don't control) which have spaces.  For example, let's say COL1 below was actually COL 1.  I have tried square brackets, but that doesn't seem to work.  Thank you.

SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
FROM [SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
  FROM TblA as A
  UNION ALL
  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
  FROM TblB as B
]. AS tmp
GROUP BY tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Did you try adding the brackets to the field names or a whole query?

This should work:

SELECT Min(tmp.TableName) AS TableName, [tmp.ID], [tmp.COL 1], [tmp.COL 2], [tmp.COL 3]
...
you have to change the "[" to "(" on this part of the query  

FROM [SELECT  to FROM (SELECT

you have to change the "]." to ")" on this part

]. AS tmp    to   ) AS tmp

so your query will look like this


SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
FROM (SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
  FROM TblA as A
  UNION ALL
  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
  FROM TblB as B
) AS tmp
GROUP BY tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;


also, enclose fields with spaces in [ ]   like  [COL 1]
Avatar of GIStewart
GIStewart

ASKER

Neither option appeared to work. I am trying to do this in access, and I have attached my sample db.  

Query1 compares TblA and TblB, and seems to work fine.

Query2 compares TblC and TblD, and has the square brackets.  (TblC is the same as TblA, TblD is the same as TblB).  But when I try and run that query, I get an "Invalid bracketing of name....." message, without even adding any spaces in the field names.  I'm not sure if it's another bracketing issue as capricorn1 suggests.  Thank you.
EE-Compare-test.mdb
this is the revision of your query  "query2"


SELECT Min(tmp.TableName) AS TableName, (tmp.ID), (tmp.COL1), (tmp.COL2), (tmp.COL3)
FROM (SELECT 'Table C' as TableName, (C.ID), (C.COL1), (C.COL2), (C.COL3)
  FROM TblC as C
  UNION ALL SELECT 'Table D' as TableName, (D.ID), (D.COL1), (D.COL2), (D.COL3)
  FROM TblD as D
) AS tmp
GROUP BY (tmp.ID), (tmp.COL1), (tmp.COL2), (tmp.COL3)
HAVING (((Count(*))=1))
ORDER BY (tmp.ID);


see query3
EE-Compare-test.mdb
thank you - so if Col1 was really Col 1 (i.e. with a space), how would I modify Query3?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this works perfectly, thank you.