Automatically append a field to a table in Access (2000 or XP) 
Author Message
 Automatically append a field to a table in Access (2000 or XP)

My name is Ann Marie and I work at Plumsted Township
School District in the Technology Department. I am hoping
someone could help me.

I need to export a table file from an access database we
use at our school into either a text file or csv file
type. NOT a problem for me because I'm Access oriented.
The exported file needs a field appended to it that is
required and not on the original table in Access. Our
table has a grade level field (09, 10, 11,12), however,
this new export needs a Graduation_Year field appended to
it (2003, 2004, etc..). No problem for me..I wrote a make
table query and modified the table to include this new
field and then wrote an update query to translate the
grade-level to a graduation_year, thus populating the new
field. Next,I ran the export procedure to create a tabbed
delimited file (and I also created a comma-delimited csv
file, too just to test). I even created macros to execute
my queries,etc. The problem is I'm not the one who will
be running this at the end of each semester. Two
secretaries will be responsible for doing that. So I am
trying to create some type of routine perhaps using a
module object (VBA) in the database itself to simplifiy
the process for a non-access, non-technical person. I am
not too sure how to do this in VB. I tried converting my
macros to VBA but it just translated them using the
DoCMD. Ideally, what I would like is something that takes
our demographics table, copies it to another table using
only certain fields and not all the original table's
fields; appends the new field, graduation_year to the new
table; sets the student ID to a primary key; and then
populates the graduation-Year with a valid value based on
the grade-level. Finally, a text file is produced for
exporting. Is this possible in Visual Basic? Another
posted message mentioned a Columns.Append method. Is this
something you did in macro? Or in a VB module? Could
someone possibly help me with the code? I am desperate! I
know there must be away of doing this programmatically. I
am just not sure of the syntax. Like I said above, I
tried converting my macros but it converted them a
functions. I think I need a subroutine of some sort.

I would be grateful for whatever information you can
share with me. I am pleading ignorant at this point...
Thanks so much,
Ann Marie



Tue, 27 Sep 2005 01:26:28 GMT  
 Automatically append a field to a table in Access (2000 or XP)


Quote:
>  Ideally, what I would like is something that takes
> our demographics table, copies it to another table using
> only certain fields and not all the original table's
> fields; appends the new field, graduation_year to the new
> table; sets the student ID to a primary key; and then
> populates the graduation-Year with a valid value based on
> the grade-level. Finally, a text file is produced for
> exporting. Is this possible in Visual Basic?

I am not sure of the details of what you are doing, but it seems to me that
you should be aiming for a single SELECT query that contains all the fields
you want, and then exporting that using a TransferDatabase action?

Tim F



Tue, 27 Sep 2005 02:31:02 GMT  
 Automatically append a field to a table in Access (2000 or XP)
1.  Actually, you don't even need to create the (expected) GraduationYear
Field.  Since if any student is in Grade 12 (final year of high school?),
you expect he/she to finish in the current year (this year), Grade 11 in the
current Year + 1, etc ...   Thus, you can simply create a calculated Field
in the Query using expression like (assuming grade is stored as numeric):

ExpectedGraduationYear: Year(Date()) + (12-Grade)

For example, from A2K Debug window:

?Year(Date()) + (12-12)
 2003

?Year(Date()) + (12-11)
 2004

2.  Another point is that if you want to use the same database year after
year, the Grade shouldn't even be the Student Detail Records.  After all,
the student will have different grades if he / she stays at your school a
number of years.  (Perhaps, I shouldn't worry you about this at present.)

3.  You can create a CommandButton or similar MenuItem to run your Macro /
VBA code (both OK) using the TransferText Macro action or the TransferText
Method of the DoCmd (Do Command) Object.

--
HTH
Van T. Dinh
MVP (Access)


Quote:
> My name is Ann Marie and I work at Plumsted Township
> School District in the Technology Department. I am hoping
> someone could help me.

> I need to export a table file from an access database we
> use at our school into either a text file or csv file
> type. NOT a problem for me because I'm Access oriented.
> The exported file needs a field appended to it that is
> required and not on the original table in Access. Our
> table has a grade level field (09, 10, 11,12), however,
> this new export needs a Graduation_Year field appended to
> it (2003, 2004, etc..). No problem for me..I wrote a make
> table query and modified the table to include this new
> field and then wrote an update query to translate the
> grade-level to a graduation_year, thus populating the new
> field. Next,I ran the export procedure to create a tabbed
> delimited file (and I also created a comma-delimited csv
> file, too just to test). I even created macros to execute
> my queries,etc. The problem is I'm not the one who will
> be running this at the end of each semester. Two
> secretaries will be responsible for doing that. So I am
> trying to create some type of routine perhaps using a
> module object (VBA) in the database itself to simplifiy
> the process for a non-access, non-technical person. I am
> not too sure how to do this in VB. I tried converting my
> macros to VBA but it just translated them using the
> DoCMD. Ideally, what I would like is something that takes
> our demographics table, copies it to another table using
> only certain fields and not all the original table's
> fields; appends the new field, graduation_year to the new
> table; sets the student ID to a primary key; and then
> populates the graduation-Year with a valid value based on
> the grade-level. Finally, a text file is produced for
> exporting. Is this possible in Visual Basic? Another
> posted message mentioned a Columns.Append method. Is this
> something you did in macro? Or in a VB module? Could
> someone possibly help me with the code? I am desperate! I
> know there must be away of doing this programmatically. I
> am just not sure of the syntax. Like I said above, I
> tried converting my macros but it converted them a
> functions. I think I need a subroutine of some sort.

> I would be grateful for whatever information you can
> share with me. I am pleading ignorant at this point...
> Thanks so much,
> Ann Marie



Tue, 27 Sep 2005 12:59:19 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Alter Table in Access 2000 under XP

2. VBA - Access 2000 Create a table and access the fields

3. Purge IMAP in Outlook 2000/XP automatically on Exit

4. Access 2000 append query run from Outlook 2000 fails - no error message - REPOST

5. Appending fields to existing Access Table

6. Writing Fields to an ACCESS 2000 Table

7. Add fields to an Access 2000 Table on the fly using ADO 2.1

8. Writing Fields to an ACCESS 2000 Table

9. Writing Fields to an ACCESS 2000 Table

10. Importing an Access 2000 table into another Access 2000 database with VB Code

11. VB3 appending a field to a table, then entering data into the new field

12. Access 2000 vs. Access 2002 (XP)

 

 
Powered by phpBB® Forum Software