Append Query to a different field name

[Guest]

(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.

 

[Guest]

Post the SQL of your append query.

 

[Guest]

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));

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.

 

[Guest]

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

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));

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.

 

[Guest]

Thanx.

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

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

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));

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.