How to export selected fields to Excel

[Victor]

I have an Access 2002 file containing the following fields:
Student-number, Student-name, Subject-1, score-1, subject-2, score-2,
subject-3, score-3

I want to export to Excel:
Student-number, Student-name, Subject-1, score-1
or
Student-number, Student-name, Subject-2, score-2
or
Student-number, Student-name, Subject-3, score-3

How to do it? Thanks for your help.


Victor

 

[John Vinson]

I have an Access 2002 file containing the following fields:

An Access 2002 "file" is a .mdb file potentially containing scores of
Tables, Forms, Reports and other objects. I think you mean that you
have a *table* contiaining this information.

Student-number, Student-name, Subject-1, score-1, subject-2, score-2,
subject-3, score-3

I want to export to Excel:
Student-number, Student-name, Subject-1, score-1
or
Student-number, Student-name, Subject-2, score-2
or
Student-number, Student-name, Subject-3, score-3

How to do it? Thanks for your help.

Create a Query by selecting New in the query design window; select
your table, and choose which fields you want to include. You can
export the Query directly to Excel.

Note that your table is *NOT* correctly normalized: your three export
files are! You might do well to create these three queries, change
them to Append queries, and migrate the data into a "tall-thin" table
with one subject and one score per record. Better, have *two* tables -
a table with StudentNumber and StudentName, and a second table with
StudentNumber, Subject, and Score.

 

[Larry Daugherty]

Hi Victor,

Design a query that selects just the fields you want from the underlying
table. Choose File|Export and away you go.

For what it's worth, your data design is such that you might as well be
doing it in Excel. In a relational database you don't just add another
column to show another instance of something for an entity.

I suggest that you'd do better to have a design such that you have three
tables: tblStudent, tblSubject and tblStudentSubject.

Each record in stbStudent would have the information about just one student
like name, address, notes, etc. Nothing here about subjects or scores.

Each record in tblSubject would have the information about just one subject.
Nothing here about students or scores.

Each record in tblStudentSubject will have the information about just one
student in one subject. It would have the score but might also have notes
and other information deemed necessary to maintain about a student in a
subject.

Table tblStudentSubject is called a 'junctin table'. Junction tables are
required to represent many-to-many relationships. It can have its own
Primary Key but will certainly have the primary keys of the other two tables
as foreign keys.

If you are trying to use Access without having had any training, it may seem
that people are telling you screwy ways to get things done. I suggest that
you lurk some other newsgroups like microsoft.public.access.gettingstarted
and microsoft.public.access.tablesdesign. In addition, I'd get a book or
two and learn Access from the beginning.

It's easy to get carried away with the design tools you have in Access and
to not realize that your data design is more important than the user
interface.

HTH

 

[Victor]

Hi Victor,

Design a query that selects just the fields you want from the underlying
table. Choose File|Export and away you go.

For what it's worth, your data design is such that you might as well be
doing it in Excel. In a relational database you don't just add another
column to show another instance of something for an entity.

Thanks to Larry & John for your prompt and detailed reply. I'll do some more
experiment with Access. Hope I can get it done. Thanks.

Victor