Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    7

    Question Unanswered: Conversion difficulties

    I am in the process of converting an existing db to Access.

    In the existing db, there is a table containing a number of Yes/No fields, each representing a "Test" performed/not performed at a "Facility". In the new db, I would like to represent each of the "Yes" fields as a "Test" record tied to the "Facility" (the facility would have a one-to-many relationship to the test records).

    Is there a way using a query I can examine the record and append a new "Test" record for each of the "Yes/No" fields having a "Yes" value?

    Thank you.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Conversion difficulties

    Originally posted by dinger
    I am in the process of converting an existing db to Access.

    In the existing db, there is a table containing a number of Yes/No fields, each representing a "Test" performed/not performed at a "Facility". In the new db, I would like to represent each of the "Yes" fields as a "Test" record tied to the "Facility" (the facility would have a one-to-many relationship to the test records).

    Is there a way using a query I can examine the record and append a new "Test" record for each of the "Yes/No" fields having a "Yes" value?

    Thank you.
    Can you create a new table or do you have to append to an existing one? If this is a new table or you can create one, then I would use a Make Table query.

    Is there a test description or some other defining characteristic for the rows that have tests besides the Yes/No field?

    To answer your question, yes there are ways to do what you are asking.

    Gregg

  3. #3
    Join Date
    Feb 2004
    Location
    AZ
    Posts
    12
    What I've done in the past (I've actually converted a "test" database and had to do something very similar) is create code to do this kind of stuff. May not be best, but I would do something like...

    'Using DAO because my ADO is shakey (more shakey than my DAO)
    public sub ConvertTestTrack()

    dim strSQL as string
    dim dbCurrent as dao.database
    dim rstCurrent as dao.recordset

    strsql = "SELECT * FROM OldTestTable;"
    set dbcurrent = currentdb
    set rstcurrent=dbcurrent.openrecordset(strsql)

    while not rstcurrent.eof
    if rstcurrent!DesertTrack = true then AddTestTrack("DesertTrack")
    elseif rstcurrent!WinterTrack = true then AddTestTrack("WinterTrack")
    'etc..etc...for each yes/no field you want to do
    end if
    rstcurrent.movenext
    wend

    end sub

    private sub AddTestTrack (byval pstrLocationName as string)
    dim strSQL as string
    dim rstNewFacility as dao.recordset
    dim dbCurrent as dao.database

    strSql = "SELECT * FROM tblTestsAtFacitlity;
    set dbcurrent = currentdb
    set rstNewFacility = currentdb.openrecorset(strsql)

    rstCurrent.add
    rstCurrent!TestTrack=pstrLocationName
    rstCurrent.update
    end sub

    'End Code

    I really can't think of any other way to do this. Of course, you'd want to run a query to see if any record has more than 1 Yes/No field set to yes cause this code will only pick up on the first one. I've always written code like this in data migration when trying to normalize database...it just seems eaiser to me.

    HTH, Jeff

  4. #4
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    7

    Re: Conversion difficulties

    Originally posted by basicmek
    Can you create a new table or do you have to append to an existing one? If this is a new table or you can create one, then I would use a Make Table query.

    Is there a test description or some other defining characteristic for the rows that have tests besides the Yes/No field?

    To answer your question, yes there are ways to do what you are asking.

    Gregg
    I can go either way on the new table/append to existing.

    The record in the current db has several Y/N fields, each related to a particular test. I would like to create a record in the new db with a corresponding "Test Code" for each of them that has a value of True.

    I.E. if Test_X = True, add test record with Test_Code = 12
    if Test_Y = True, add test record with Test_Code = 13, etc.


    Thanks!

  5. #5
    Join Date
    Feb 2004
    Location
    Baltimore, MD
    Posts
    7
    I should mention that more than one of the Y/N fields may be true, and I would want to create a new record for each one that is.

  6. #6
    Join Date
    Feb 2004
    Location
    AZ
    Posts
    12
    Originally posted by dinger
    I should mention that more than one of the Y/N fields may be true, and I would want to create a new record for each one that is.
    If more than 1 test location can exist, then simply change the elseif to else. Example...

    Code:
    if rstcurrent!DesertTrack = true
     then AddTestTrack("DesertTrack")
    end if
    if rstcurrent!WinterTrack = true
     then AddTestTrack("WinterTrack")
    end if
    'etc..etc...for each yes/no field you want to do
    end if

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by dinger
    I should mention that more than one of the Y/N fields may be true, and I would want to create a new record for each one that is.
    Are you familiar with using QBE in Access and/or using Make Table queries?

    The goal is to create a select query the returns the rows and columns that you want in the new table and then create the table with this information. You will include the field that you want to use to tie the two tables together, which in your case, I would say this is some Facility identifier.

    So, fields I would see included would be FacilityID, Test(Y/N), TestName (or whatever you call it). In the criterea of the Test(Y/N) field just choose Yes.

    Make sure in the query properties window, you select Unique Values to "YES". This will keep you from returning duplicate rows of information.

    When you are satisfied that you are returning the rows that you want to include in the new table, change the query type to Make Table and run it.

    If you need more specific or additional clues just call back.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •