query field names changing to expr#:

  • Thread starter Thread starter Susan
  • Start date Start date
S

Susan

In Access 2007, I'm importing Excel data to tblUpload. Next I run an
append query to load the records from tbl Upload to tblHours. Lastly,
I delete the records in tblUpload using a delete query, so tblUpload
will be empty when I next perform the Excel import. After running the
delete query, all of the append query field names have changed from
EmployeeID to expr1:EmployeeID, and the append query doesn't work
unless I "fix" the field names first. How can I keep the field names
from changing in my append query? Thanks for your help.
 
In Access 2007, I'm importing Excel data to tblUpload. Next I run an
append query to load the records from tbl Upload to tblHours. Lastly,
I delete the records in tblUpload using a delete query, so tblUpload
will be empty when I next perform the Excel import. After running the
delete query, all of the append query field names have changed from
EmployeeID to expr1:EmployeeID, and the append query doesn't work
unless I "fix" the field names first. How can I keep the field names
from changing in my append query? Thanks for your help.
Click to expand...

By correcting the error in your code or your query... which you have chosen
not to post. Give us some help here?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
By correcting the error in your code or your query... which you have chosen
not to post. Give us some help here?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

Yes, including my code would certainly help.

Append query:

INSERT INTO [Time TestNLU] ( EmployeeID, ActivityDate, OKIL, PaidL,
HolidayL, YDAdmin, YDHost, YDOut, YDColl, YDTTA, YDP2P, YDPDev,
YDEval, YDOther, RHYTC, RHYTAC, RFT, DDSD, TRAUMA, JPTP, PARB, TCEO,
Pool )
SELECT [Upload Time TestNLU].EmployeeID, [Upload Time
TestNLU].ActivityDate, [Upload Time TestNLU].OKIL, [Upload Time
TestNLU].PaidL, [Upload Time TestNLU].OtherL, [Upload Time
TestNLU].YDAdmin, [Upload Time TestNLU].YDHost, [Upload Time
TestNLU].YDOut, [Upload Time TestNLU].YDColl, [Upload Time
TestNLU].YDTTA, [Upload Time TestNLU].YDP2P, [Upload Time
TestNLU].YDPDev, [Upload Time TestNLU].YDEval, [Upload Time
TestNLU].YDOther, [Upload Time TestNLU].RHYTC, [Upload Time
TestNLU].RHYTAC, [Upload Time TestNLU].RFT, [Upload Time
TestNLU].DDSD, [Upload Time TestNLU].TRAUMA, [Upload Time
TestNLU].JPTP, [Upload Time TestNLU].PARB, [Upload Time TestNLU].TCEO,
[Upload Time TestNLU].Pool
FROM [Upload Time TestNLU];

Delete query:

DELETE *
FROM [Upload Time TestNLU];
 
INSERT INTO [Time TestNLU] ( EmployeeID, ActivityDate, OKIL, PaidL,
HolidayL, YDAdmin, YDHost, YDOut, YDColl, YDTTA, YDP2P, YDPDev,
YDEval, YDOther, RHYTC, RHYTAC, RFT, DDSD, TRAUMA, JPTP, PARB, TCEO,
Pool )
SELECT [Upload Time TestNLU].EmployeeID, [Upload Time
TestNLU].ActivityDate, [Upload Time TestNLU].OKIL, [Upload Time
TestNLU].PaidL, [Upload Time TestNLU].OtherL, [Upload Time
TestNLU].YDAdmin, [Upload Time TestNLU].YDHost, [Upload Time
TestNLU].YDOut, [Upload Time TestNLU].YDColl, [Upload Time
TestNLU].YDTTA, [Upload Time TestNLU].YDP2P, [Upload Time
TestNLU].YDPDev, [Upload Time TestNLU].YDEval, [Upload Time
TestNLU].YDOther, [Upload Time TestNLU].RHYTC, [Upload Time
TestNLU].RHYTAC, [Upload Time TestNLU].RFT, [Upload Time
TestNLU].DDSD, [Upload Time TestNLU].TRAUMA, [Upload Time
TestNLU].JPTP, [Upload Time TestNLU].PARB, [Upload Time TestNLU].TCEO,
[Upload Time TestNLU].Pool
FROM [Upload Time TestNLU];
Click to expand...

It sounds like the fieldnames in [Upload Time TestNLU] may not be the same as
those in the query. How is the upload table created? The fact that it's
inserting Expr1: and the like suggests that Access is creating fieldnames for
want of existing ones.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
INSERT INTO [Time TestNLU] ( EmployeeID, ActivityDate, OKIL, PaidL,
HolidayL, YDAdmin, YDHost, YDOut, YDColl, YDTTA, YDP2P, YDPDev,
YDEval, YDOther, RHYTC, RHYTAC, RFT, DDSD, TRAUMA, JPTP, PARB, TCEO,
Pool )
SELECT [Upload Time TestNLU].EmployeeID, [Upload Time
TestNLU].ActivityDate, [Upload Time TestNLU].OKIL, [Upload Time
TestNLU].PaidL, [Upload Time TestNLU].OtherL, [Upload Time
TestNLU].YDAdmin, [Upload Time TestNLU].YDHost, [Upload Time
TestNLU].YDOut, [Upload Time TestNLU].YDColl, [Upload Time
TestNLU].YDTTA, [Upload Time TestNLU].YDP2P, [Upload Time
TestNLU].YDPDev, [Upload Time TestNLU].YDEval, [Upload Time
TestNLU].YDOther, [Upload Time TestNLU].RHYTC, [Upload Time
TestNLU].RHYTAC, [Upload Time TestNLU].RFT, [Upload Time
TestNLU].DDSD, [Upload Time TestNLU].TRAUMA, [Upload Time
TestNLU].JPTP, [Upload Time TestNLU].PARB, [Upload Time TestNLU].TCEO,
[Upload Time TestNLU].Pool
FROM [Upload Time TestNLU];
Click to expand...

It sounds like the fieldnames in [Upload Time TestNLU] may not be the same as
those in the query. How is the upload table created? The fact that it's
inserting Expr1: and the like suggests that Access is creating fieldnamesfor
want of existing ones.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

I will double check. I created the Upload table using the fieldnames
in the time table that is being appended to. The Upload table isn't a
temp or make table. Do the fields have to be in the same order on the
upload table as in the time table?

There is one condition where the upload table field is OtherL, and the
time table field is HolidayL. I thought I accounted for that in the
query, but may need to add specific criteria that states where
[UploadTable].[OtherL]=[TimeTable].[HolidayL]. Could that be it?
Thanks.
 
I will double check. I created the Upload table using the fieldnames
in the time table that is being appended to. The Upload table isn't a
temp or make table. Do the fields have to be in the same order on the
upload table as in the time table?
Click to expand...

The order of fields in the table is irrelevant - your query specifies the list
of fields into which the data will be inserted in the parentheses in the
INSERT INTO clause, which should match the *data* in the fields in the SELECT
clause. The names need not be the same, but you should be sure you're matching
them correctly.
There is one condition where the upload table field is OtherL, and the
time table field is HolidayL. I thought I accounted for that in the
query, but may need to add specific criteria that states where
[UploadTable].[OtherL]=[TimeTable].[HolidayL]. Could that be it?
Thanks.
Click to expand...

This I don't understand. Criteria would just limit which *records* are
inserted. If your query has

INSERT INTO [TimeTable] (... , HolidayL, ....)
SELECT ... , [OtherL], ...

in matching positions you should be fine.

Where are you seeing this Expr1: showing up? Is that text being inserted into
your output table, or are you opening a datasheet based on the append query,
or what??
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
I will double check. I created the Upload table using the fieldnames
in the time table that is being appended to. The Upload table isn't a
temp or make table. Do the fields have to be in the same order on the
upload table as in the time table?
Click to expand...

The order of fields in the table is irrelevant - your query specifies thelist
of fields into which the data will be inserted in the parentheses in the
INSERT INTO clause, which should match the *data* in the fields in the SELECT
clause. The names need not be the same, but you should be sure you're matching
them correctly.
There is one condition where the upload table field is OtherL, and the
time table field is HolidayL. I thought I accounted for that in the
query, but may need to add specific criteria that states where
[UploadTable].[OtherL]=[TimeTable].[HolidayL]. Could that be it?
Thanks.
Click to expand...

This I don't understand. Criteria would just limit which *records* are
inserted. If your query has

INSERT INTO [TimeTable] (... , HolidayL, ....)
SELECT ... , [OtherL], ...

in matching positions you should be fine.

Where are you seeing this Expr1: showing up? Is that text being inserted into
your output table, or are you opening a datasheet based on the append query,
or what??
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

When I open the design view of the Append (insert) query, in the Field
Names fields from the Upload Time table, the field names have changed
from EmployeeID to Expr1:EmployeeID, and ActivityDate to
Expr2:ActivityDate. The field names in the Append To: fields remain
correct. I correct the field names, save. When I re-open the
database, they have changed back to the Expr# format. thanks, again,
for your thoughts.
 
Susan said:
I will double check. I created the Upload table using the fieldnames
in the time table that is being appended to. The Upload table isn't
a temp or make table. Do the fields have to be in the same order on
the upload table as in the time table?
Click to expand...

The order of fields in the table is irrelevant - your query
specifies the list of fields into which the data will be inserted in
the parentheses in the INSERT INTO clause, which should match the
*data* in the fields in the SELECT clause. The names need not be the
same, but you should be sure you're matching them correctly.
There is one condition where the upload table field is OtherL, and
the time table field is HolidayL. I thought I accounted for that in
the query, but may need to add specific criteria that states where
[UploadTable].[OtherL]=[TimeTable].[HolidayL]. Could that be it?
Thanks.
Click to expand...

This I don't understand. Criteria would just limit which *records*
are inserted. If your query has

INSERT INTO [TimeTable] (... , HolidayL, ....)
SELECT ... , [OtherL], ...

in matching positions you should be fine.

Where are you seeing this Expr1: showing up? Is that text being
inserted into your output table, or are you opening a datasheet
based on the append query, or what??
Click to expand...

When I open the design view of the Append (insert) query, in the Field
Names fields from the Upload Time table, the field names have changed
from EmployeeID to Expr1:EmployeeID, and ActivityDate to
Expr2:ActivityDate. The field names in the Append To: fields remain
correct. I correct the field names, save. When I re-open the
database, they have changed back to the Expr# format. thanks, again,
for your thoughts.
Click to expand...

Why bother changing them back? They're just column aliases and don't affect
the functionality of your query, as long as they are in the appropriate
order to populate the correct fields in the destination (Append To) table.
 
The order of fields in the table is irrelevant - your query specifies the list
of fields into which the data will be inserted in the parentheses in the
INSERT INTO clause, which should match the *data* in the fields in the SELECT
clause. The names need not be the same, but you should be sure you're matching
them correctly.
There is one condition where the upload table field is OtherL, and the
time table field is HolidayL. I thought I accounted for that in the
query, but may need to add specific criteria that states where
[UploadTable].[OtherL]=[TimeTable].[HolidayL]. Could that be it?
Thanks.
Click to expand...
Click to expand...
This I don't understand. Criteria would just limit which *records* are
inserted. If your query has
Click to expand...
INSERT INTO [TimeTable] (... , HolidayL, ....)
SELECT ... , [OtherL], ...
Click to expand...
in matching positions you should be fine.
Click to expand...
Where are you seeing this Expr1: showing up? Is that text being inserted into
your output table, or are you opening a datasheet based on the append query,
or what??
--
Click to expand...
             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

When I open the design view of the Append (insert) query, in the Field
Names fields from the Upload Time table, the field names have changed
from EmployeeID to Expr1:EmployeeID, and ActivityDate to
Expr2:ActivityDate. The field names in the Append To: fields remain
correct.  I correct the field names, save. When I re-open the
database, they have changed back to the Expr# format. thanks, again,
for your thoughts.- Hide quoted text -

- Show quoted text -
Click to expand...

Here's how the sql changes:

INSERT INTO [Time TestNLU] (EmployeeID, ActivityDate, OKIL, PaidL,
HolidayL, YDAdmin, YDHost, YDOut, YDColl, YDTTA, YDP2P, YDPDev,
YDEval, YDOther, RHYTC, RHYTAC, RFT, DDSD, TRAUMA, JPTP, PARB, TCEO,
Pool )
SELECT [Upload Time TestNLU].EmployeeID AS Expr1, [Upload Time
TestNLU].ActivityDate AS Expr2, [Upload Time TestNLU].OKIL AS Expr3,
[Upload Time TestNLU].[OtherL] AS Expr1, [Upload Time
TestNLU].HolidayL AS Expr4, [Upload Time TestNLU].YDAdmin AS Expr5,
[Upload Time TestNLU].YDHost AS Expr6, [Upload Time TestNLU].YDOut AS
Expr7, [Upload Time TestNLU].YDColl AS Expr8, [Upload Time
TestNLU].YDTTA AS Expr9, [Upload Time TestNLU].YDP2P AS Expr10,
[Upload Time TestNLU].YDPDev AS Expr11, [Upload Time TestNLU].YDEval
AS Expr12, [Upload Time TestNLU].YDOther AS Expr13, [Upload Time
TestNLU].RHYTC AS Expr14, [Upload Time TestNLU].RHYTAC AS Expr15,
[Upload Time TestNLU].RFT AS Expr16, [Upload Time TestNLU].DDSD AS
Expr17, [Upload Time TestNLU].TRAUMA AS Expr18, [Upload Time
TestNLU].JPTP AS Expr19, [Upload Time TestNLU].PARB AS Expr20, [Upload
Time TestNLU].TCEO AS Expr21, [Upload Time TestNLU].Pool AS Expr22
FROM [Upload Time TestNLU];

Notice in the Select statement how the Select field name that
corresponds to the Insert field name PaidL not only reverts back to
Expr1 (rather than Expr4), but it changes the Select field name to
OtherL from PaidL- when the append query is in it's corrected form,
the Select and Insert matching field names are PaidL. Then the next
Insert field name is HolidayL, and the matching Select field name in
the correct version is OtherL. These are the only two fields that seem
to be inconsistent in the Expr# format.
 
Back
Top