query field names changing to expr#:
Susan
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.
John W. Vinson
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
Susan
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
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];
John W. Vinson
>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];
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.
Susan
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>
> - Show quoted text -
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.
John W. Vinson
>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?
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.
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??
Susan
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
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.
Bob Barrows
> 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.
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.
Susan
>
> - Show quoted text -
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.
Susan
Because the query field names get changed to the Expr# format, and two
of them get changed to Expr1, I get the error message: Duplicate
output alias 'Expr1'
Susan
>
> - Show quoted text -
My last post was in reponse to Bob Barrow's response.
Bob Barrows
>> 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 -
>
> Because the query field names get changed to the Expr# format, and two
> of them get changed to Expr1, I get the error message: Duplicate
> output alias 'Expr1'
Well then I retract my reply - this is the first I heard that dupe column
aliases were being produced. This is very weird. Usually the designer won't
allow duplicate column names.
What happens if you create a simple saved select query (a view), and base
your append query on the saved view?
Bob Barrows
Oh! And you might try putting brackest around all the field names in the
SELECT clause. They shouldn't be needed, but perhaps it wil prevent Access
from generating the aliases.
The query would be much more readable if you provided an alias for that
table name ... since there is only a single table in the FROM clause, there
really is no need to qualify the field names in the select list, but
unfortunately Access does it anyways.
Another thing you can do is create a new query, entering the SQL directly in
SQL View, and never switching it to Design View (it's that switch that is
likely causing this).
That the designer is geerating duplicate column aliases definitely sounds
like a bug.
Susan
Thanks, Bob. I'll try each of your suggestions and post my results. I
appreciate your time.