Export transposed from Access to Excel (Office XP)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone suggest a method to export one DB field as a column and another
field as rows?

Field one is name and field two is class.

I want the resultant sheet to have one name per row, but as many colmns in
that row as there are classes in the various records for that name.

Kind of a mutant pivot table.

Sample DB data is:

Name 1 Class 1
Name 1 Class 2
Name 2 Class 1
name 2 Class 2
Name 2 Class 3
etc.

Sheet should appear as follows:

Name 1 Class 1 Class 2
Name 2 Class 1 Class 2 Class 3
etc.

Thanks for your assistance.
 
hi,
the more i look at this the more i think this could be
done as a crosstab query(PivotTable). have you tried this?
i would write a query selecting the data then use that
select query as the sorce for the crosstab then down load
the crosstab.
Try it.
Regards
Frank
 
I agree that it seems like a crosstab would work. One thing you may need to
do though, is calculate a rank for the classes. I'm guessing that Class 1
for Name 1 may be different than Class 1 for Name 2, and that you just want
to list all of the classes horizontally. If that is the case, use DCount()
or a correlated subquery to rank each class for each name. Then use the Rank
value as your column heading.

example:

Rank: DCount("*","YourTable","[NameField] = '" & [NameField] & "' AND
[ClassName] <= '" & [ClassName] & "'")

HTH, Ted Allen
 
Back
Top