Too Many Access Fields After Importing

[Mytara]

Hi All,

I have a file I get each year from our testing company. It comes in as a
CSV or TXT file. I use the TXT file as it is way too long for Access or
Excel. I can import it without any incident. The problem is the way it comes
in. I only need 7 fields, but the TXT file is approximately 60 fields. I
have a couple of other tables that get sent to me that have to connect with
this one to get the correct testing information out. Here is how it comes
into Access after I import it...

SchoolName
GroupName
Level (Grade)
SubjectCode (Tells What Type of Test Given)
RptCat1 (Tells What Type of Category Question is From)
ItemDes1 (Tells What Type of Question was Answered)
ItemResp1 (Tells A Correct or Incorrect Answer)
RptCat2
ItemDes2
ItemResp2
RptCat3
ItemDesc3
ItemResp3.......to
RptCat60
ItemDes60
ItemResp60

There can be up to 100 Reporting Categories, however, we typically only use
60.

This is what I need to end up with...

SchoolName
GroupName
Level (Grade)
SubjectCode (Tells What Type of Test Given)
RptCat (Tells What Type of Category Question is From)
ItemDes (Tells What Type of Question was Answered)
ItemResp (Tells A Correct or Incorrect Answer)

I did a copy and paste to get it into just the 7 fields, but ended
duplicating information so I am not trusting what I get out of the database
to be correct. Is there a way this can be done in Access? I know that Excel
has the transpose and I tried that, but there is way too much info in this
file for it to handle doing it and then going back into Access with it.

Thanks!

 

[Jeff Boyce]

The incoming data is not well-normalized (nor would I expect it to be). You
need to use some queries to extract data from the incoming data and
append/update your more permanent tables' fields.

If "normalization" and "relational" are not familiar terms, plan to spend
some time learning more about them before you attempt to use Access (a
relational database that expects/works best with normalized data).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mytara]

Thanks so much Jeff. I am familar with those terms. Was checking to see if
there was an easier way.

 

[aaa]

I don't understand