Results 1 to 4 of 4
  1. #1
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25

    How do you create a new field based on imported header information?

    Hello again.



    I've gotten a lot done with the help of llkhoutx, but now I have 1 remaining question. Apologies in advanced if I'm confusing (I'm new to access!)

    See the previous thread if it helps.

    Our lab has several .dat files (.txt files) with one of the following headers (see attached file, LFCC1x1, as an example)

    LFCC1x1 95 00 SJL
    LFCP#x# 95 00 SJL
    OFCC#x# 95 00 SJL
    OFCP#x# 95 00 SJL

    LFCC (or one of the other varients), represents a Study. The first # in 1x1 represents the patient number, and the second # in 1x1 (the 2) represents the session number. Patients can have several sessions (maximum 9, so for patient 1, 1x9 is his or her last session).

    The 95 00 SJL (which varies from file-to-file) can be negated. In fact, I'd like it to be ignored completely.

    I'm trying to figure out a way for Access to distinguish the different header type (LFCC, LFCP, OFCC, or OFCP) and import that into a new field entitled Study. (So the Study field would have 1 of 4 variables, LFCC, LFCP, OFCC or OFCP).

    So far, I've been able import the data accordingly, along with the patient ID (ID) and session ID (File), everything else is its own field. However, I have no idea how to go about getting header data to be imported into this new field.

    Any advice would be grateful.
    Last edited by TheWolfster; 02-17-2010 at 02:07 PM.

  2. #2
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    I don't understand why you need a different column for each study. A Study, is a study is a study. You don't similarly contemplate multiple columns for each "principal investigator" ("PI" as used in your original thread). You can track and count multiple studies in one column via a query. Multiple studies can be related to a single PI or multiple PIs or the same studies related to multiple PIs>

    Import you data into a temporary table, then use a query to populate or build columns in the real data table. If you can't do it in a query, you can cycle through the imported temporary table recordset, building the real data table in VBA.

    I'm still with you.

  3. #3
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    Sorry for the confusion, I posted that while in a bit of a rush. I went ahead and cleared up the original post.

    We don't need a different column for each study, just one column that identifies each study.

    If anyone could point me in the right direction as to how do that, I'd be very thankful.

  4. #4
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    To import data Access requires that data in every column be of the same data type and that rows be identical in format. Comma delimited files import but are none the less identical in format.

    Your txt file violates those rule and will not accordingly import. However, all is not lost.

    Merely attach the txt file and read it row by row with DAO or ADO VBA code, unpacking each row according to its content. I personally prefer DAO coding for Access. It is very powerful.

    A very excellant book on DAO is Access Database Design & Programming, 3rd Edition, pulished by O'Reilly. New and used on Amazon.com at here.

    Posting a complete batch of your data would facilitate providing a more definitive solution. A header, spacer and one data row is not sufficient.

    Is the 2nd (data definition) row of the txt file always to same, regardless of the study or does it change with study? Coding can handle different data definitions.
    Last edited by llkhoutx; 02-17-2010 at 02:34 PM. Reason: Added a question for TheWolfster

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Default Information for an existing field HELP
    By opinionsrfun in forum Access
    Replies: 1
    Last Post: 06-19-2013, 06:30 AM
  2. Replies: 4
    Last Post: 09-03-2009, 02:01 PM
  3. Total based on Formula based on field value
    By cjbuechler in forum Reports
    Replies: 15
    Last Post: 07-10-2009, 09:56 AM
  4. Replies: 0
    Last Post: 02-15-2009, 09:14 PM
  5. Help with imported data
    By bubbasheeko in forum Queries
    Replies: 0
    Last Post: 01-12-2007, 07:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums