Manta ray database matrix to field conversion

[Tim]

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

 

[tina]

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]

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

 

[Jeff Boyce]

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>

 

[tina]

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

 

[tina]

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

 

[tina]

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"