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!
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" wrote in message news:D84F2517-BE61-4471-AB26-0DF7CB79D070@microsoft.com... > 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! >
Thanks so much Jeff. I am familar with those terms. Was checking to see if there was an easier way. "Jeff Boyce" wrote: > 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" wrote in message > news:D84F2517-BE61-4471-AB26-0DF7CB79D070@microsoft.com... > > 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! > > > > >
