Field names in an append query

[PA]

I am attempting to append one table with another. I have several fields
whose names are in the form of xxx - yyyy, aaaa - bb, etc. There is a
space on either side of the hyphen. One of the fields, named "name - last"
(no quotes) is not being recognized, for a reason I can not understand.
Therefore the query will not run.
I have tripled checked that the names are in fact exactly duplicated in each
table and that the properties are likewise duplicated.
I am at loss to determine what the problem might be?????

Thanks
Paul

 

[Jerry Whittle]

Are you doing this from the QBE grid in design mode? You should be able to
use the drop down to select the matching field.

Would you happen to have fields with just 'name' and/or 'last' as the field
name? Access might misinterpet things and is trying to subtract two columns.

Then there's the problem that "name" is a reserved word and "last" is a
group by function. That could cause Access to get confused. See the link
below.

http://support.microsoft.com/kb/286335/

As a rule, I never use any special characters, except the underscore, in
field names, table names, ect. This includes not using spaces. For example I
would name a date of birth field, Date_of_Birth, DateOfBirth, or DOB.

I also avoid reserved words. I'd probably name your "date - last" either
DateLast or Date_Last.

Time to get down off my soap box. By "Therefore the query will not run",
what do you mean? Error message? No data appended to the table or just that
field?

Please provide the SQL statement for that query. Maybe we can devine
something from it.

 

[Klatuu]

Since you are violating several naming rules, you are going to have this
problem. First, names in Access should not have spaces and second, Name is a
reserved word and with the space it can be confusing to Access. One thing
that may cure the problem would be to enclose all your names in brackets:
[xxx - yyyy], [aaaa - bb], [Name - Last]

Good naming conventions:

Do not use any reserved words (Name, Date, Value, Year, etc.)
Do not use anything except letters, numbers, and the underscore in names.
You can use reserved words if they are part of a name:

Entry_Date, LastName, Reserve_Value, PlanYear

 

[PA]

By saying the query did not run, no data was appended.

Thanking for the help, here is the SQL code. The query was created in
design view

INSERT INTO FINAL_TABLE_ACCOUNTING ( [Member ID - Consist - MCS],
[Name-Last], [Name-First], [Gender Code], [Date of Birth], [Addr - Street 1],
[Addr-City], [Addr-State], [Addr-Zip], [Norm Risk Score - Base Yr],
DM_CATEGORY )
SELECT COPD_NO_CHF.[Member ID - Consist - MCS], COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First], COPD_NO_CHF.[Gender Code], COPD_NO_CHF.[Date of
Birth], COPD_NO_CHF.[Addr - Street 1], COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State], COPD_NO_CHF.[Addr - Zip], COPD_NO_CHF.[Norm Risk
Score - Base Yr], "COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;

 

[Jerry Whittle]

Well the first thing it to see if the SELECT statement returns anything to
append.

SELECT COPD_NO_CHF.[Member ID - Consist - MCS],
COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First],
COPD_NO_CHF.[Gender Code],
COPD_NO_CHF.[Date of Birth],
COPD_NO_CHF.[Addr - Street 1],
COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State],
COPD_NO_CHF.[Addr - Zip],
COPD_NO_CHF.[Norm Risk Score - Base Yr],
"COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;

There's no WHERE clause so short of no data in the COPD_NO_CHF table, it
should return records. Still worth checking.

All the problem field names are surrounded by square brackets like [Name -
Last] so that should not be the problem.

Check the data types between the two tables. If you are trying to push text
into a number field, that could be a problem. Also if you are trying to push
in 30 characters into a text field sized for only 25, that will cause grief.
However you should be getting error messages unless someone Set Warnings off.

Next check the FINAL_TABLE_ACCOUNTING table for any primary keys or unique
indexes. It's possible that every record in the COPD_NO_CHF table violates
the constraint and can't be put into the table. If Set Warnings is turned
off, you won't see an error and no new records will be appended.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

By saying the query did not run, no data was appended.

Thanking for the help, here is the SQL code. The query was created in
design view

INSERT INTO FINAL_TABLE_ACCOUNTING ( [Member ID - Consist - MCS],
[Name-Last], [Name-First], [Gender Code], [Date of Birth], [Addr - Street 1],
[Addr-City], [Addr-State], [Addr-Zip], [Norm Risk Score - Base Yr],
DM_CATEGORY )
SELECT COPD_NO_CHF.[Member ID - Consist - MCS], COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First], COPD_NO_CHF.[Gender Code], COPD_NO_CHF.[Date of
Birth], COPD_NO_CHF.[Addr - Street 1], COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State], COPD_NO_CHF.[Addr - Zip], COPD_NO_CHF.[Norm Risk
Score - Base Yr], "COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;

Are you doing this from the QBE grid in design mode? You should be able to
use the drop down to select the matching field.

Would you happen to have fields with just 'name' and/or 'last' as the field
name? Access might misinterpet things and is trying to subtract two columns.

Then there's the problem that "name" is a reserved word and "last" is a
group by function. That could cause Access to get confused. See the link
below.

http://support.microsoft.com/kb/286335/

As a rule, I never use any special characters, except the underscore, in
field names, table names, ect. This includes not using spaces. For example I
would name a date of birth field, Date_of_Birth, DateOfBirth, or DOB.

I also avoid reserved words. I'd probably name your "date - last" either
DateLast or Date_Last.

Time to get down off my soap box. By "Therefore the query will not run",
what do you mean? Error message? No data appended to the table or just that
field?

Please provide the SQL statement for that query. Maybe we can devine
something from it.

 

[John Spencer]

DID you switch to Data Sheet view? If so, the query will display the
records it is about to insert, but will not actually do any insertion.

You must execute (run) the query.
--Press the run button (Red Exclamation) or
--select Query: Run from the menu or
--Close the query and double click on the query in the query window Or
--Right click on the query name and select Open or ...

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

By saying the query did not run, no data was appended.

Thanking for the help, here is the SQL code. The query was created in
design view

INSERT INTO FINAL_TABLE_ACCOUNTING ( [Member ID - Consist - MCS],
[Name-Last], [Name-First], [Gender Code], [Date of Birth], [Addr - Street
1],
[Addr-City], [Addr-State], [Addr-Zip], [Norm Risk Score - Base Yr],
DM_CATEGORY )
SELECT COPD_NO_CHF.[Member ID - Consist - MCS], COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First], COPD_NO_CHF.[Gender Code], COPD_NO_CHF.[Date
of
Birth], COPD_NO_CHF.[Addr - Street 1], COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State], COPD_NO_CHF.[Addr - Zip], COPD_NO_CHF.[Norm
Risk
Score - Base Yr], "COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;

Are you doing this from the QBE grid in design mode? You should be able
to
use the drop down to select the matching field.

Would you happen to have fields with just 'name' and/or 'last' as the
field
name? Access might misinterpet things and is trying to subtract two
columns.

Then there's the problem that "name" is a reserved word and "last" is a
group by function. That could cause Access to get confused. See the link
below.

http://support.microsoft.com/kb/286335/

As a rule, I never use any special characters, except the underscore, in
field names, table names, ect. This includes not using spaces. For
example I
would name a date of birth field, Date_of_Birth, DateOfBirth, or DOB.

I also avoid reserved words. I'd probably name your "date - last" either
DateLast or Date_Last.

Time to get down off my soap box. By "Therefore the query will not run",
what do you mean? Error message? No data appended to the table or just
that
field?

Please provide the SQL statement for that query. Maybe we can devine
something from it.

 

[Jerry Whittle]

Very good point!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

DID you switch to Data Sheet view? If so, the query will display the
records it is about to insert, but will not actually do any insertion.

You must execute (run) the query.
--Press the run button (Red Exclamation) or
--select Query: Run from the menu or
--Close the query and double click on the query in the query window Or
--Right click on the query name and select Open or ...

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

By saying the query did not run, no data was appended.

Thanking for the help, here is the SQL code. The query was created in
design view

INSERT INTO FINAL_TABLE_ACCOUNTING ( [Member ID - Consist - MCS],
[Name-Last], [Name-First], [Gender Code], [Date of Birth], [Addr - Street
1],
[Addr-City], [Addr-State], [Addr-Zip], [Norm Risk Score - Base Yr],
DM_CATEGORY )
SELECT COPD_NO_CHF.[Member ID - Consist - MCS], COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First], COPD_NO_CHF.[Gender Code], COPD_NO_CHF.[Date
of
Birth], COPD_NO_CHF.[Addr - Street 1], COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State], COPD_NO_CHF.[Addr - Zip], COPD_NO_CHF.[Norm
Risk
Score - Base Yr], "COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;

Are you doing this from the QBE grid in design mode? You should be able
to
use the drop down to select the matching field.

Would you happen to have fields with just 'name' and/or 'last' as the
field
name? Access might misinterpet things and is trying to subtract two
columns.

Then there's the problem that "name" is a reserved word and "last" is a
group by function. That could cause Access to get confused. See the link
below.

http://support.microsoft.com/kb/286335/

As a rule, I never use any special characters, except the underscore, in
field names, table names, ect. This includes not using spaces. For
example I
would name a date of birth field, Date_of_Birth, DateOfBirth, or DOB.

I also avoid reserved words. I'd probably name your "date - last" either
DateLast or Date_Last.

Time to get down off my soap box. By "Therefore the query will not run",
what do you mean? Error message? No data appended to the table or just
that
field?

Please provide the SQL statement for that query. Maybe we can devine
something from it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am attempting to append one table with another. I have several
fields
whose names are in the form of xxx - yyyy, aaaa - bb, etc. There is
a
space on either side of the hyphen. One of the fields, named "name -
last"
(no quotes) is not being recognized, for a reason I can not understand.
Therefore the query will not run.
I have tripled checked that the names are in fact exactly duplicated in
each
table and that the properties are likewise duplicated.
I am at loss to determine what the problem might be?????

Thanks
Paul

 

[PA]

Assuming the problem is with the field names, could/would there be any ill
effects if the problem fields were renamed, for example,
name - last to name_last or lname or something similiar.

Very good point!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

DID you switch to Data Sheet view? If so, the query will display the
records it is about to insert, but will not actually do any insertion.

You must execute (run) the query.
--Press the run button (Red Exclamation) or
--select Query: Run from the menu or
--Close the query and double click on the query in the query window Or
--Right click on the query name and select Open or ...

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

By saying the query did not run, no data was appended.

Thanking for the help, here is the SQL code. The query was created in
design view

INSERT INTO FINAL_TABLE_ACCOUNTING ( [Member ID - Consist - MCS],
[Name-Last], [Name-First], [Gender Code], [Date of Birth], [Addr - Street
1],
[Addr-City], [Addr-State], [Addr-Zip], [Norm Risk Score - Base Yr],
DM_CATEGORY )
SELECT COPD_NO_CHF.[Member ID - Consist - MCS], COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First], COPD_NO_CHF.[Gender Code], COPD_NO_CHF.[Date
of
Birth], COPD_NO_CHF.[Addr - Street 1], COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State], COPD_NO_CHF.[Addr - Zip], COPD_NO_CHF.[Norm
Risk
Score - Base Yr], "COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;


:

Are you doing this from the QBE grid in design mode? You should be able
to
use the drop down to select the matching field.

Would you happen to have fields with just 'name' and/or 'last' as the
field
name? Access might misinterpet things and is trying to subtract two
columns.

Then there's the problem that "name" is a reserved word and "last" is a
group by function. That could cause Access to get confused. See the link
below.

http://support.microsoft.com/kb/286335/

As a rule, I never use any special characters, except the underscore, in
field names, table names, ect. This includes not using spaces. For
example I
would name a date of birth field, Date_of_Birth, DateOfBirth, or DOB.

I also avoid reserved words. I'd probably name your "date - last" either
DateLast or Date_Last.

Time to get down off my soap box. By "Therefore the query will not run",
what do you mean? Error message? No data appended to the table or just
that
field?

Please provide the SQL statement for that query. Maybe we can devine
something from it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am attempting to append one table with another. I have several
fields
whose names are in the form of xxx - yyyy, aaaa - bb, etc. There is
a
space on either side of the hyphen. One of the fields, named "name -
last"
(no quotes) is not being recognized, for a reason I can not understand.
Therefore the query will not run.
I have tripled checked that the names are in fact exactly duplicated in
each
table and that the properties are likewise duplicated.
I am at loss to determine what the problem might be?????

Thanks
Paul

 

[Jerry Whittle]

It could be just fine or a diaster. If you change a table of field name, all
queries, forms, and reports looking for that name would break. Before doing
such things, I highly recommend making a backup of the database.

Access has Name Autocorrect which is suppose to fix such problems like field
name changes. Name Autocorrect often doesn't work right and is a major
performance hit even when working right. Therefore a lot of people turn it
off. Go to Tools, Options, General Tab to enable Name Autocorrect and Perform
Name Autocorrect if off. Now you could try changing field names and see what
happens. After you fix everything, you may want to disable Name Autocorrect.
Remember to do this only after making a backup copy.


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Assuming the problem is with the field names, could/would there be any ill
effects if the problem fields were renamed, for example,
name - last to name_last or lname or something similiar.

Very good point!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

DID you switch to Data Sheet view? If so, the query will display the
records it is about to insert, but will not actually do any insertion.

You must execute (run) the query.
--Press the run button (Red Exclamation) or
--select Query: Run from the menu or
--Close the query and double click on the query in the query window Or
--Right click on the query name and select Open or ...

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

By saying the query did not run, no data was appended.

Thanking for the help, here is the SQL code. The query was created in
design view

INSERT INTO FINAL_TABLE_ACCOUNTING ( [Member ID - Consist - MCS],
[Name-Last], [Name-First], [Gender Code], [Date of Birth], [Addr - Street
1],
[Addr-City], [Addr-State], [Addr-Zip], [Norm Risk Score - Base Yr],
DM_CATEGORY )
SELECT COPD_NO_CHF.[Member ID - Consist - MCS], COPD_NO_CHF.[Name - Last],
COPD_NO_CHF.[Name - First], COPD_NO_CHF.[Gender Code], COPD_NO_CHF.[Date
of
Birth], COPD_NO_CHF.[Addr - Street 1], COPD_NO_CHF.[Addr - City],
COPD_NO_CHF.[Addr - State], COPD_NO_CHF.[Addr - Zip], COPD_NO_CHF.[Norm
Risk
Score - Base Yr], "COPD" AS DM_CATEGORY
FROM COPD_NO_CHF;


:

Are you doing this from the QBE grid in design mode? You should be able
to
use the drop down to select the matching field.

Would you happen to have fields with just 'name' and/or 'last' as the
field
name? Access might misinterpet things and is trying to subtract two
columns.

Then there's the problem that "name" is a reserved word and "last" is a
group by function. That could cause Access to get confused. See the link
below.

http://support.microsoft.com/kb/286335/

As a rule, I never use any special characters, except the underscore, in
field names, table names, ect. This includes not using spaces. For
example I
would name a date of birth field, Date_of_Birth, DateOfBirth, or DOB.

I also avoid reserved words. I'd probably name your "date - last" either
DateLast or Date_Last.

Time to get down off my soap box. By "Therefore the query will not run",
what do you mean? Error message? No data appended to the table or just
that
field?

Please provide the SQL statement for that query. Maybe we can devine
something from it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am attempting to append one table with another. I have several
fields
whose names are in the form of xxx - yyyy, aaaa - bb, etc. There is
a
space on either side of the hyphen. One of the fields, named "name -
last"
(no quotes) is not being recognized, for a reason I can not understand.
Therefore the query will not run.
I have tripled checked that the names are in fact exactly duplicated in
each
table and that the properties are likewise duplicated.
I am at loss to determine what the problem might be?????

Thanks
Paul