Append Query to a different field name

R

Ricoy-Chicago

(Access XP) I have an append query that add several fields to the table
[Student Grades], no problem here.

One field, [ProspectID], is appended to the [Student Grades]. However, all
the reports and forms that use the [Student Grades] table are based on the
field [StudentID] (don't ask me why!).

My question is: How can I append (or copy?) the contents of the [ProspectID]
field into the [StudentID] field?

This will save me all the time of trying to figure out how the reports and
forms work and then redisign them to the new field [ProspectID] |:(

I can do it manullay but I do not have the time to do it everytime the staff
has to work on the students' grades.

Any help will be greatly appreciated.
 
R

Ricoy-Chicago

Karl,

Sorry about the delayed answer... posted my Q' before going home on Friday...

here is the SQL from the Query design view. Again, the [Student Grades]
table has already a field named [Student ID], I added the field [Prospect ID]
to the [Student Grades] table. I know there should not be spaces in the field
names but that is how I inhereted all this from the previous designer.

INSERT INTO [Student Grades] ( [Prospect ID], [Class Code], [Subject ID],
Quarter )
SELECT [Prospect Students].[Prospect ID], [CLASS Information].[Class Code],
[Subjects per Program].[Subject ID], [Subjects per Program].Quarter
FROM (([Program Info] INNER JOIN [CLASS Information] ON [Program
Info].[Program Code] = [CLASS Information].[Program Code]) INNER JOIN
[Subjects per Program] ON [Program Info].[Program Code] = [Subjects per
Program].[Program Code]) INNER JOIN [Prospect Students] ON ([CLASS
Information].[Class Code] = [Prospect Students].[Class Code]) AND ([Program
Info].[Program Code] = [Prospect Students].[Program Code])
WHERE ((([CLASS Information].[Class Code])=[Forms]![Add Current Students to
Student Grades]![txtClassCode]) AND (([Subjects per
Program].Quarter)=[Forms]![Add Current Students to Student Grades]![txtQtr])
AND (([Prospect Students].Enrolled)=Yes) AND (([Prospect
Students].Drop)=No));

KARL DEWEY said:
Post the SQL of your append query.
--
KARL DEWEY
Build a little - Test a little


Ricoy-Chicago said:
(Access XP) I have an append query that add several fields to the table
[Student Grades], no problem here.

One field, [ProspectID], is appended to the [Student Grades]. However, all
the reports and forms that use the [Student Grades] table are based on the
field [StudentID] (don't ask me why!).

My question is: How can I append (or copy?) the contents of the [ProspectID]
field into the [StudentID] field?

This will save me all the time of trying to figure out how the reports and
forms work and then redisign them to the new field [ProspectID] |:(

I can do it manullay but I do not have the time to do it everytime the staff
has to work on the students' grades.

Any help will be greatly appreciated.
 
K

KARL DEWEY

If your table has field [StudentID] then just can the first line of your
SQL to this ---
INSERT INTO [Student Grades] ( [StudentID], [Class Code], [Subject ID], .....


--
KARL DEWEY
Build a little - Test a little


Ricoy-Chicago said:
Karl,

Sorry about the delayed answer... posted my Q' before going home on Friday...

here is the SQL from the Query design view. Again, the [Student Grades]
table has already a field named [Student ID], I added the field [Prospect ID]
to the [Student Grades] table. I know there should not be spaces in the field
names but that is how I inhereted all this from the previous designer.

INSERT INTO [Student Grades] ( [Prospect ID], [Class Code], [Subject ID],
Quarter )
SELECT [Prospect Students].[Prospect ID], [CLASS Information].[Class Code],
[Subjects per Program].[Subject ID], [Subjects per Program].Quarter
FROM (([Program Info] INNER JOIN [CLASS Information] ON [Program
Info].[Program Code] = [CLASS Information].[Program Code]) INNER JOIN
[Subjects per Program] ON [Program Info].[Program Code] = [Subjects per
Program].[Program Code]) INNER JOIN [Prospect Students] ON ([CLASS
Information].[Class Code] = [Prospect Students].[Class Code]) AND ([Program
Info].[Program Code] = [Prospect Students].[Program Code])
WHERE ((([CLASS Information].[Class Code])=[Forms]![Add Current Students to
Student Grades]![txtClassCode]) AND (([Subjects per
Program].Quarter)=[Forms]![Add Current Students to Student Grades]![txtQtr])
AND (([Prospect Students].Enrolled)=Yes) AND (([Prospect
Students].Drop)=No));

KARL DEWEY said:
Post the SQL of your append query.
--
KARL DEWEY
Build a little - Test a little


Ricoy-Chicago said:
(Access XP) I have an append query that add several fields to the table
[Student Grades], no problem here.

One field, [ProspectID], is appended to the [Student Grades]. However, all
the reports and forms that use the [Student Grades] table are based on the
field [StudentID] (don't ask me why!).

My question is: How can I append (or copy?) the contents of the [ProspectID]
field into the [StudentID] field?

This will save me all the time of trying to figure out how the reports and
forms work and then redisign them to the new field [ProspectID] |:(

I can do it manullay but I do not have the time to do it everytime the staff
has to work on the students' grades.

Any help will be greatly appreciated.
 
R

Ricoy-Chicago

Thanx.

I knew it was going to be something very simple...

KARL DEWEY said:
If your table has field [StudentID] then just can the first line of your
SQL to this ---
INSERT INTO [Student Grades] ( [StudentID], [Class Code], [Subject ID], .....


--
KARL DEWEY
Build a little - Test a little


Ricoy-Chicago said:
Karl,

Sorry about the delayed answer... posted my Q' before going home on Friday...

here is the SQL from the Query design view. Again, the [Student Grades]
table has already a field named [Student ID], I added the field [Prospect ID]
to the [Student Grades] table. I know there should not be spaces in the field
names but that is how I inhereted all this from the previous designer.

INSERT INTO [Student Grades] ( [Prospect ID], [Class Code], [Subject ID],
Quarter )
SELECT [Prospect Students].[Prospect ID], [CLASS Information].[Class Code],
[Subjects per Program].[Subject ID], [Subjects per Program].Quarter
FROM (([Program Info] INNER JOIN [CLASS Information] ON [Program
Info].[Program Code] = [CLASS Information].[Program Code]) INNER JOIN
[Subjects per Program] ON [Program Info].[Program Code] = [Subjects per
Program].[Program Code]) INNER JOIN [Prospect Students] ON ([CLASS
Information].[Class Code] = [Prospect Students].[Class Code]) AND ([Program
Info].[Program Code] = [Prospect Students].[Program Code])
WHERE ((([CLASS Information].[Class Code])=[Forms]![Add Current Students to
Student Grades]![txtClassCode]) AND (([Subjects per
Program].Quarter)=[Forms]![Add Current Students to Student Grades]![txtQtr])
AND (([Prospect Students].Enrolled)=Yes) AND (([Prospect
Students].Drop)=No));

KARL DEWEY said:
Post the SQL of your append query.
--
KARL DEWEY
Build a little - Test a little


:

(Access XP) I have an append query that add several fields to the table
[Student Grades], no problem here.

One field, [ProspectID], is appended to the [Student Grades]. However, all
the reports and forms that use the [Student Grades] table are based on the
field [StudentID] (don't ask me why!).

My question is: How can I append (or copy?) the contents of the [ProspectID]
field into the [StudentID] field?

This will save me all the time of trying to figure out how the reports and
forms work and then redisign them to the new field [ProspectID] |:(

I can do it manullay but I do not have the time to do it everytime the staff
has to work on the students' grades.

Any help will be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top