Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Manta ray database matrix to field conversion

Discussion in 'Information Technology' started by Tim, Oct 29, 2005.

  1. Tim

    Tim
    Expand Collapse
    Guest

    I am doing a study on manta rays, and just imported my data from Excel to
    Access. The study is a photo-ID study, where we record what individual manta
    rays are seen on a particular date. Each manta has a name, so we can easily
    keep track of them. I have a date field, and a field for each manta, where
    the manta field gets a 0 or 1 depending on if the manta is absent (0) or
    present (1) on that date. I needed this binary matrix form in the past for a
    specific program, but now I am wanting a single date field and a single manta
    field. For example, I have the current format:

    Date Amy Andrea Andy
    10/28 1 0 1
    10/29 0 1 1

    and I would like to have:

    Date Manta
    10/28 Amy
    10/28 Andy
    10/29 Andrea
    10/29 Andy

    I am new to access, and can't get it to do this. Any help would be greatly
    appreciated.

    Thanks,

    Tim
     
  2. Loading...


  3. tina

    tina
    Expand Collapse
    Guest

    well, you're definitely thinking in the right direction - the design you
    indicated as your desired goal is in fact a normalized table design. but
    it's not clear just what your over-all goal is.

    are you migrating the existing data into Access with the intention of adding
    future data directly to Access, rather than using Excel? if so, then you
    need to create normalized tables to store the current and future data (an
    Excel "flat file" design won't cut it in Access), and then use Append
    queries to distribute the existing data into the proper tables. this can be
    quite a challenge, but at least you only have to do it once.

    or did you just dump the Excel data into Access to query it for reports?

    hth


    "Tim" <Tim@discussions.microsoft.com> wrote in message
    news:655F332E-3C46-453F-9114-FC98BCA24238@microsoft.com...
    > I am doing a study on manta rays, and just imported my data from Excel to
    > Access. The study is a photo-ID study, where we record what individual

    manta
    > rays are seen on a particular date. Each manta has a name, so we can

    easily
    > keep track of them. I have a date field, and a field for each manta,

    where
    > the manta field gets a 0 or 1 depending on if the manta is absent (0) or
    > present (1) on that date. I needed this binary matrix form in the past

    for a
    > specific program, but now I am wanting a single date field and a single

    manta
    > field. For example, I have the current format:
    >
    > Date Amy Andrea Andy
    > 10/28 1 0 1
    > 10/29 0 1 1
    >
    > and I would like to have:
    >
    > Date Manta
    > 10/28 Amy
    > 10/28 Andy
    > 10/29 Andrea
    > 10/29 Andy
    >
    > I am new to access, and can't get it to do this. Any help would be

    greatly
    > appreciated.
    >
    > Thanks,
    >
    > Tim
    >
     
  4. Tim

    Tim
    Expand Collapse
    Guest

    Tina,

    I had all the data in an Excel file, so I have simply imported my file into
    Access. Now I want to be able to query the Access file in order to do
    various statistics. I already have a Manta ID file with the names of each of
    the mantas, an ID number for each individual, and all the data we have
    collected on the individual. Now I need a table with the date each manta was
    seen (the Date Manta table).

    Once I get the database in the proper format, all future data entry will be
    into the Access database. I have the rest of the database ready (both entry
    forms, normalized tables, and queries). I am just needing to get this old
    data into the proper format so I can use it.

    Tim




    "tina" wrote:

    > well, you're definitely thinking in the right direction - the design you
    > indicated as your desired goal is in fact a normalized table design. but
    > it's not clear just what your over-all goal is.
    >
    > are you migrating the existing data into Access with the intention of adding
    > future data directly to Access, rather than using Excel? if so, then you
    > need to create normalized tables to store the current and future data (an
    > Excel "flat file" design won't cut it in Access), and then use Append
    > queries to distribute the existing data into the proper tables. this can be
    > quite a challenge, but at least you only have to do it once.
    >
    > or did you just dump the Excel data into Access to query it for reports?
    >
    > hth
    >
    >
    > "Tim" <Tim@discussions.microsoft.com> wrote in message
    > news:655F332E-3C46-453F-9114-FC98BCA24238@microsoft.com...
    > > I am doing a study on manta rays, and just imported my data from Excel to
    > > Access. The study is a photo-ID study, where we record what individual

    > manta
    > > rays are seen on a particular date. Each manta has a name, so we can

    > easily
    > > keep track of them. I have a date field, and a field for each manta,

    > where
    > > the manta field gets a 0 or 1 depending on if the manta is absent (0) or
    > > present (1) on that date. I needed this binary matrix form in the past

    > for a
    > > specific program, but now I am wanting a single date field and a single

    > manta
    > > field. For example, I have the current format:
    > >
    > > Date Amy Andrea Andy
    > > 10/28 1 0 1
    > > 10/29 0 1 1
    > >
    > > and I would like to have:
    > >
    > > Date Manta
    > > 10/28 Amy
    > > 10/28 Andy
    > > 10/29 Andrea
    > > 10/29 Andy
    > >
    > > I am new to access, and can't get it to do this. Any help would be

    > greatly
    > > appreciated.
    > >
    > > Thanks,
    > >
    > > Tim
    > >

    >
    >
    >
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    Tim

    This is not an "elegant" solution, more "brute force"...

    You haven't indicated how many mantas you have, so this may be too time
    consuming...

    One approach would be to create a query of your existing data, with the
    DateSeen (don't use [Date] as a field name -- this is a reserved word) and
    [Amy] selected. Set your criterion for [Amy] to 1. This should return all
    rows (dates) on which Amy was seen.

    Convert the query to an append query and select your "permanent" table. Add
    a field something like:

    Individual: "Amy"

    When you run this query, it will add all DateSeens and the literal "Amy" to
    your permanent table.

    Here's the brute force part ... modify the query to use the Andrea column,
    and change the literal to "Andrea". Re-run the query. Repeat as needed to
    get all individuals added.

    <g> Hopefully you don't have more than 100 or so!<g>

    (and if you are quite comfortable with creating a procedure, you could
    automate the whole process. If you are not familiar/experienced, it could
    take much longer than this brute force method. Heck, since it sounds like a
    one-time conversion, it could take you longer to work out the code even if
    you are familiar!)

    Good luck!

    Jeff Boyce
    <Office/Access MVP>

    "Tim" <Tim@discussions.microsoft.com> wrote in message
    news:655F332E-3C46-453F-9114-FC98BCA24238@microsoft.com...
    > I am doing a study on manta rays, and just imported my data from Excel to
    > Access. The study is a photo-ID study, where we record what individual

    manta
    > rays are seen on a particular date. Each manta has a name, so we can

    easily
    > keep track of them. I have a date field, and a field for each manta,

    where
    > the manta field gets a 0 or 1 depending on if the manta is absent (0) or
    > present (1) on that date. I needed this binary matrix form in the past

    for a
    > specific program, but now I am wanting a single date field and a single

    manta
    > field. For example, I have the current format:
    >
    > Date Amy Andrea Andy
    > 10/28 1 0 1
    > 10/29 0 1 1
    >
    > and I would like to have:
    >
    > Date Manta
    > 10/28 Amy
    > 10/28 Andy
    > 10/29 Andrea
    > 10/29 Andy
    >
    > I am new to access, and can't get it to do this. Any help would be

    greatly
    > appreciated.
    >
    > Thanks,
    >
    > Tim
    >
     
  6. tina

    tina
    Expand Collapse
    Guest

    okay. if you don't have a normalized table set up already, to hold the "date
    each manta was seen" data, then set up something along the following lines:

    tblMantaSeen
    SeenID (primary key)
    MantaID (foreign key from tblMantas - your table that lists all the mantas
    with an ID for each)
    DateSeen (date/time data type)

    relationship is:
    tblMantas 1:n tblMantaSeen

    i'm assuming that the *name* of each manta, that's stored in tblMantas, is
    exactly the same as the *name* of each manta in your imported Excel table. i
    came up with an automated solution, using the following tables to "mirror"
    your table setup.

    tblMantas
    MantaID (primary key)
    MantaName

    tblExcelImport
    xDate
    Amy
    Andrea
    Andy
    (the above are field names, of course, not data values.)
    note: even in a temporary table, i wouldn't have a field named Date because
    it's an Access reserved word.

    tblMantaSeen
    SeenID (primary key)
    fkMantaID (foreign key from tblMantas)
    DateSeen

    i ran the following code from a standard module, which looped through all
    the fields in the excel import table, using the fieldname (which is a
    manta's name) to lookup the manta's ID and then appending the ID and the
    seen date into tblMantaSeen:

    Public Sub isUpdate()

    Dim rst As DAO.Recordset, fld As DAO.Field
    Dim lngID As Long, strSQL As String

    Set rst = CurrentDb.OpenRecordset("tblExcelImport", dbOpenDynaset)

    For Each fld In rst.Fields
    If Not fld.Name = "xDate" Then
    lngID = DLookup("MantaID", "tblMantas", "MantaName = '" _
    & fld.Name & "'")
    strSQL = "INSERT INTO tblMantaSeen ( fkMantaID, DateSeen ) " _
    & "SELECT " & lngID & ", xDate FROM tblExcelImport WHERE " _
    & "tblExcelImport." & fld.Name & " = 1"
    CurrentDb.Execute strSQL, dbFailOnError
    End If
    Next

    MsgBox "done"

    End Sub

    you'll need to go through the code and substitute the correct table and
    field names, of course. if you can't figure it out, post back the exact
    names of your three tables and the relevant fields, and i'll ring the
    changes for you. btw, if you're using Access 2000, make sure you have a
    reference set to DAO, otherwise the code will err out.

    hth


    "Tim" <Tim@discussions.microsoft.com> wrote in message
    news:5D43D74C-456B-404E-B66F-EE656F08054D@microsoft.com...
    > Tina,
    >
    > I had all the data in an Excel file, so I have simply imported my file

    into
    > Access. Now I want to be able to query the Access file in order to do
    > various statistics. I already have a Manta ID file with the names of each

    of
    > the mantas, an ID number for each individual, and all the data we have
    > collected on the individual. Now I need a table with the date each manta

    was
    > seen (the Date Manta table).
    >
    > Once I get the database in the proper format, all future data entry will

    be
    > into the Access database. I have the rest of the database ready (both

    entry
    > forms, normalized tables, and queries). I am just needing to get this old
    > data into the proper format so I can use it.
    >
    > Tim
    >
    >
    >
    >
    > "tina" wrote:
    >
    > > well, you're definitely thinking in the right direction - the design you
    > > indicated as your desired goal is in fact a normalized table design. but
    > > it's not clear just what your over-all goal is.
    > >
    > > are you migrating the existing data into Access with the intention of

    adding
    > > future data directly to Access, rather than using Excel? if so, then you
    > > need to create normalized tables to store the current and future data

    (an
    > > Excel "flat file" design won't cut it in Access), and then use Append
    > > queries to distribute the existing data into the proper tables. this can

    be
    > > quite a challenge, but at least you only have to do it once.
    > >
    > > or did you just dump the Excel data into Access to query it for reports?
    > >
    > > hth
    > >
    > >
    > > "Tim" <Tim@discussions.microsoft.com> wrote in message
    > > news:655F332E-3C46-453F-9114-FC98BCA24238@microsoft.com...
    > > > I am doing a study on manta rays, and just imported my data from Excel

    to
    > > > Access. The study is a photo-ID study, where we record what

    individual
    > > manta
    > > > rays are seen on a particular date. Each manta has a name, so we can

    > > easily
    > > > keep track of them. I have a date field, and a field for each manta,

    > > where
    > > > the manta field gets a 0 or 1 depending on if the manta is absent (0)

    or
    > > > present (1) on that date. I needed this binary matrix form in the

    past
    > > for a
    > > > specific program, but now I am wanting a single date field and a

    single
    > > manta
    > > > field. For example, I have the current format:
    > > >
    > > > Date Amy Andrea Andy
    > > > 10/28 1 0 1
    > > > 10/29 0 1 1
    > > >
    > > > and I would like to have:
    > > >
    > > > Date Manta
    > > > 10/28 Amy
    > > > 10/28 Andy
    > > > 10/29 Andrea
    > > > 10/29 Andy
    > > >
    > > > I am new to access, and can't get it to do this. Any help would be

    > > greatly
    > > > appreciated.
    > > >
    > > > Thanks,
    > > >
    > > > Tim
    > > >

    > >
    > >
    > >
     
  7. tina

    tina
    Expand Collapse
    Guest

    i posted this once, but it didn't seem to go through, so here it is again.

    okay. if you don't have a normalized table set up already, to hold the "date
    each manta was seen" data, then set up something along the following lines:

    tblMantaSeen
    SeenID (primary key)
    fkMantaID (foreign key from tblMantas - your table that lists all the mantas
    with an ID for each)
    DateSeen (date/time data type)

    relationship is:
    tblMantas 1:n tblMantaSeen

    i'm assuming that the *name* of each manta, that's stored in tblMantas, is
    exactly the same as the *name* of each manta in your imported Excel table. i
    came up with an automated solution, using the following tables to "mirror"
    your table setup.

    tblMantas
    MantaID (primary key)
    MantaName

    tblExcelImport
    xDate
    Amy
    Andrea
    Andy
    (the above are field names, of course, not data values.)
    note: even in a temporary table, i wouldn't have a field named Date because
    it's an Access reserved word.

    tblMantaSeen
    SeenID (primary key)
    fkMantaID (foreign key from tblMantas)
    DateSeen

    i ran the following code from a standard module, which looped through all
    the fields in the excel import table, using the fieldname (which is a
    manta's name) to lookup the manta's ID and then appending the ID and the
    seen date into tblMantaSeen:

    Public Sub isUpdate()

    Dim rst As DAO.Recordset, fld As DAO.Field
    Dim lngID As Long, strSQL As String

    Set rst = CurrentDb.OpenRecordset("tblExcelImport", dbOpenDynaset)

    For Each fld In rst.Fields
    If Not fld.Name = "xDate" Then
    lngID = DLookup("MantaID", "tblMantas", "MantaName = '" _
    & fld.Name & "'")
    strSQL = "INSERT INTO tblMantaSeen ( fkMantaID, DateSeen ) " _
    & "SELECT " & lngID & ", xDate FROM tblExcelImport WHERE " _
    & "tblExcelImport." & fld.Name & " = 1"
    CurrentDb.Execute strSQL, dbFailOnError
    End If
    Next

    MsgBox "done"

    End Sub

    the code worked, so try it at your end. you'll need to go through the code
    and substitute the correct table and field names, of course. if you can't
    figure it out, post back the exact names of your three tables and the
    relevant fields, and i'll ring the changes for you. btw, if you're using
    Access 2000, make sure you have a reference set to DAO, otherwise the code
    will err out.

    hth


    "Tim" <Tim@discussions.microsoft.com> wrote in message
    news:5D43D74C-456B-404E-B66F-EE656F08054D@microsoft.com...
    > Tina,
    >
    > I had all the data in an Excel file, so I have simply imported my file

    into
    > Access. Now I want to be able to query the Access file in order to do
    > various statistics. I already have a Manta ID file with the names of each

    of
    > the mantas, an ID number for each individual, and all the data we have
    > collected on the individual. Now I need a table with the date each manta

    was
    > seen (the Date Manta table).
    >
    > Once I get the database in the proper format, all future data entry will

    be
    > into the Access database. I have the rest of the database ready (both

    entry
    > forms, normalized tables, and queries). I am just needing to get this old
    > data into the proper format so I can use it.
    >
    > Tim
    >
    >
    >
    >
    > "tina" wrote:
    >
    > > well, you're definitely thinking in the right direction - the design you
    > > indicated as your desired goal is in fact a normalized table design. but
    > > it's not clear just what your over-all goal is.
    > >
    > > are you migrating the existing data into Access with the intention of

    adding
    > > future data directly to Access, rather than using Excel? if so, then you
    > > need to create normalized tables to store the current and future data

    (an
    > > Excel "flat file" design won't cut it in Access), and then use Append
    > > queries to distribute the existing data into the proper tables. this can

    be
    > > quite a challenge, but at least you only have to do it once.
    > >
    > > or did you just dump the Excel data into Access to query it for reports?
    > >
    > > hth
    > >
    > >
    > > "Tim" <Tim@discussions.microsoft.com> wrote in message
    > > news:655F332E-3C46-453F-9114-FC98BCA24238@microsoft.com...
    > > > I am doing a study on manta rays, and just imported my data from Excel

    to
    > > > Access. The study is a photo-ID study, where we record what

    individual
    > > manta
    > > > rays are seen on a particular date. Each manta has a name, so we can

    > > easily
    > > > keep track of them. I have a date field, and a field for each manta,

    > > where
    > > > the manta field gets a 0 or 1 depending on if the manta is absent (0)

    or
    > > > present (1) on that date. I needed this binary matrix form in the

    past
    > > for a
    > > > specific program, but now I am wanting a single date field and a

    single
    > > manta
    > > > field. For example, I have the current format:
    > > >
    > > > Date Amy Andrea Andy
    > > > 10/28 1 0 1
    > > > 10/29 0 1 1
    > > >
    > > > and I would like to have:
    > > >
    > > > Date Manta
    > > > 10/28 Amy
    > > > 10/28 Andy
    > > > 10/29 Andrea
    > > > 10/29 Andy
    > > >
    > > > I am new to access, and can't get it to do this. Any help would be

    > > greatly
    > > > appreciated.
    > > >
    > > > Thanks,
    > > >
    > > > Tim
    > > >

    > >
    > >
    > >
     
  8. tina

    tina
    Expand Collapse
    Guest

    oops, i forgot one thing. in the previous code i posted, add the following
    two lines just above the Msgbox line, as

    rst.Close
    Set rst = Nothing
    MsgBox "done"



    "Tim" <Tim@discussions.microsoft.com> wrote in message
    news:5D43D74C-456B-404E-B66F-EE656F08054D@microsoft.com...
    > Tina,
    >
    > I had all the data in an Excel file, so I have simply imported my file

    into
    > Access. Now I want to be able to query the Access file in order to do
    > various statistics. I already have a Manta ID file with the names of each

    of
    > the mantas, an ID number for each individual, and all the data we have
    > collected on the individual. Now I need a table with the date each manta

    was
    > seen (the Date Manta table).
    >
    > Once I get the database in the proper format, all future data entry will

    be
    > into the Access database. I have the rest of the database ready (both

    entry
    > forms, normalized tables, and queries). I am just needing to get this old
    > data into the proper format so I can use it.
    >
    > Tim
    >
    >
    >
    >
    > "tina" wrote:
    >
    > > well, you're definitely thinking in the right direction - the design you
    > > indicated as your desired goal is in fact a normalized table design. but
    > > it's not clear just what your over-all goal is.
    > >
    > > are you migrating the existing data into Access with the intention of

    adding
    > > future data directly to Access, rather than using Excel? if so, then you
    > > need to create normalized tables to store the current and future data

    (an
    > > Excel "flat file" design won't cut it in Access), and then use Append
    > > queries to distribute the existing data into the proper tables. this can

    be
    > > quite a challenge, but at least you only have to do it once.
    > >
    > > or did you just dump the Excel data into Access to query it for reports?
    > >
    > > hth
    > >
    > >
    > > "Tim" <Tim@discussions.microsoft.com> wrote in message
    > > news:655F332E-3C46-453F-9114-FC98BCA24238@microsoft.com...
    > > > I am doing a study on manta rays, and just imported my data from Excel

    to
    > > > Access. The study is a photo-ID study, where we record what

    individual
    > > manta
    > > > rays are seen on a particular date. Each manta has a name, so we can

    > > easily
    > > > keep track of them. I have a date field, and a field for each manta,

    > > where
    > > > the manta field gets a 0 or 1 depending on if the manta is absent (0)

    or
    > > > present (1) on that date. I needed this binary matrix form in the

    past
    > > for a
    > > > specific program, but now I am wanting a single date field and a

    single
    > > manta
    > > > field. For example, I have the current format:
    > > >
    > > > Date Amy Andrea Andy
    > > > 10/28 1 0 1
    > > > 10/29 0 1 1
    > > >
    > > > and I would like to have:
    > > >
    > > > Date Manta
    > > > 10/28 Amy
    > > > 10/28 Andy
    > > > 10/29 Andrea
    > > > 10/29 Andy
    > > >
    > > > I am new to access, and can't get it to do this. Any help would be

    > > greatly
    > > > appreciated.
    > > >
    > > > Thanks,
    > > >
    > > > Tim
    > > >

    > >
    > >
    > >
     

Share This Page