Can SQL do this?

[Rina]

Hi all.

I have 2 tables in Access (see attachments).
1) InvertedFile
2) TermSet

From these 2 tables, I want to make a new db table called InverseDF (see attachment).

Table of InverseDF consists of fields: Terms which is retrieved from TermSet.Terms and another fields that I want it to be retrived from the DocNo (SELECT InvertedFile.DocNo FROM InvertedFile GROUP BY DocNo) which the result would be like in the attachment (InverseDF.jpeg).

The records for field 96 and 97 are retrieved from table InvertedFile. And, the record will be set to "0" if the term does not belong to the DocNo(96 or 97).

For the Terms field, this is easy for me to do.
conn.Execute "SELECT TermSet.Terms INTO InverseDF FROM TermSet;"

But for other fields, I think this is difficult for me to do.
I just wonder, is this possible in SQL to make rows become the columns of table? If possible, how to construct the SQL?
If this is not possible, can anyone give me an idea or another possible way to do this?

Pls help, thanks in advance.

[eranfox]

Hello Rina,
I'm on my way to go to sleep ,but...
if you want to do it one time only you can copy the table to excel and inside excel :

Selection.Copy
    Range("new range here").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

this will make the rows columns and the columns rows.
in SQL there is PIVOT but i dont remmember how is the syntax of that...

Best Regards,
ERAN

[Rina]

Copy the Access table inside Excel?
This will integrate VB6 that I currently use with MS Excel? Will this?
Can anybody explain this more? I never done this before.

[Rina]

Hmm..I dont want to do this just for one time.