Space in field name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
The tables I imported have spaces in the field names and this has cause the
following querry to fail:
SELECT q.[Title], q.[Company] INTO AgtUnion
FROM [SELECT Title, Company FROM Agt95758
UNION
SELECT Title, Company FROM Agt95831]. AS q;
The querry above fail when "Title" is replaced by say, "First Name" which
contains a space.
My questions are:
(i) Can this problem be avoided by modifying the syntax in the above SQL?
(ii) If not, then what is the simplest way to (programmatically) handle the
spaces in the field names as I import data into tables?
Thanks.
David
 
No, you can't solve it by modifying the query (at least directly).

You can make a separate query of the subquery and use aliases for the field
names with spaces
OR
you can use multiple append queries to add the records into a pre-defined table.
OR
Use a make table query for the first group of records and an append for the
second (and any other) group of records.
 
Excellent! Thanks for the response.
Could you illustrate how to create aliases for a field name?
I am using ACCESS 2003 (MDB not ADP).
The Access online help has a discussion for ADP only.
David



John Spencer (MVP) said:
No, you can't solve it by modifying the query (at least directly).

You can make a separate query of the subquery and use aliases for the field
names with spaces
OR
you can use multiple append queries to add the records into a pre-defined table.
OR
Use a make table query for the first group of records and an append for the
second (and any other) group of records.
Hi,
The tables I imported have spaces in the field names and this has cause the
following querry to fail:
SELECT q.[Title], q.[Company] INTO AgtUnion
FROM [SELECT Title, Company FROM Agt95758
UNION
SELECT Title, Company FROM Agt95831]. AS q;
The querry above fail when "Title" is replaced by say, "First Name" which
contains a space.
My questions are:
(i) Can this problem be avoided by modifying the syntax in the above SQL?
(ii) If not, then what is the simplest way to (programmatically) handle the
spaces in the field names as I import data into tables?
Thanks.
David
Click to expand...
Click to expand...
 
Besides how to use alias, I also need help with using append for multiple
tables.
My question here is: How to append multiple tables (of the same structure)?
Can this be done with using one querry, or must I use a separate query for
each table appended?
If the latter is the case, is there a way to write just one querry, and with
table name input as a parameter interactively (sort of like parametric querry
I guess).
David

----
David said:
Excellent! Thanks for the response.
Could you illustrate how to create aliases for a field name?
I am using ACCESS 2003 (MDB not ADP).
The Access online help has a discussion for ADP only.
David



John Spencer (MVP) said:
No, you can't solve it by modifying the query (at least directly).

You can make a separate query of the subquery and use aliases for the field
names with spaces
OR
you can use multiple append queries to add the records into a pre-defined table.
OR
Use a make table query for the first group of records and an append for the
second (and any other) group of records.
Hi,
The tables I imported have spaces in the field names and this has cause the
following querry to fail:
SELECT q.[Title], q.[Company] INTO AgtUnion
FROM [SELECT Title, Company FROM Agt95758
UNION
SELECT Title, Company FROM Agt95831]. AS q;
The querry above fail when "Title" is replaced by say, "First Name" which
contains a space.
My questions are:
(i) Can this problem be avoided by modifying the syntax in the above SQL?
(ii) If not, then what is the simplest way to (programmatically) handle the
spaces in the field names as I import data into tables?
Thanks.
David
Click to expand...
Click to expand...
Click to expand...
 
In addition: Can multiple tables (of the same structure) be appended using
one querry?
In a separate querry must be used for each table appended, then can the name
of the table be added interactively (in the style of a "parametric" query)?
David

David said:
Excellent! Thanks for the response.
Could you illustrate how to create aliases for a field name?
I am using ACCESS 2003 (MDB not ADP).
The Access online help has a discussion for ADP only.
David



John Spencer (MVP) said:
No, you can't solve it by modifying the query (at least directly).

You can make a separate query of the subquery and use aliases for the field
names with spaces
OR
you can use multiple append queries to add the records into a pre-defined table.
OR
Use a make table query for the first group of records and an append for the
second (and any other) group of records.
Hi,
The tables I imported have spaces in the field names and this has cause the
following querry to fail:
SELECT q.[Title], q.[Company] INTO AgtUnion
FROM [SELECT Title, Company FROM Agt95758
UNION
SELECT Title, Company FROM Agt95831]. AS q;
The querry above fail when "Title" is replaced by say, "First Name" which
contains a space.
My questions are:
(i) Can this problem be avoided by modifying the syntax in the above SQL?
(ii) If not, then what is the simplest way to (programmatically) handle the
spaces in the field names as I import data into tables?
Thanks.
David
Click to expand...
Click to expand...
Click to expand...
 
First, Access only allows you to have one append query at a time.

Alias for a field name or a tablename

SELECT [Field with Spaces] As FieldWithNoSpaces
FROM [Table With Spaces] as TableWithNoSpaces
....

The only way you could do that would be to use vba to build your query and loop
through the names. Obviously you would need a source for the tablenames.

Besides how to use alias, I also need help with using append for multiple
tables.
My question here is: How to append multiple tables (of the same structure)?
Can this be done with using one querry, or must I use a separate query for
each table appended?
If the latter is the case, is there a way to write just one querry, and with
table name input as a parameter interactively (sort of like parametric querry
I guess).
David

----
David said:
Excellent! Thanks for the response.
Could you illustrate how to create aliases for a field name?
I am using ACCESS 2003 (MDB not ADP).
The Access online help has a discussion for ADP only.
David



John Spencer (MVP) said:
No, you can't solve it by modifying the query (at least directly).

You can make a separate query of the subquery and use aliases for the field
names with spaces
OR
you can use multiple append queries to add the records into a pre-defined table.
OR
Use a make table query for the first group of records and an append for the
second (and any other) group of records.

David wrote:

Hi,
The tables I imported have spaces in the field names and this has cause the
following querry to fail:
SELECT q.[Title], q.[Company] INTO AgtUnion
FROM [SELECT Title, Company FROM Agt95758
UNION
SELECT Title, Company FROM Agt95831]. AS q;
The querry above fail when "Title" is replaced by say, "First Name" which
contains a space.
My questions are:
(i) Can this problem be avoided by modifying the syntax in the above SQL?
(ii) If not, then what is the simplest way to (programmatically) handle the
spaces in the field names as I import data into tables?
Thanks.
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top