[ANSS-netops] Access database
Agnew, James D.
jagnew at water.ca.gov
Mon Feb 9 23:04:24 GMT 2009
I started an MS-Access database in self-defense here at DWR about 3.5
years ago to track just about everything associated with our weak- and
strong-motion seismic networks. It's up to about 60 mostly-interlinked
tables (some are stand-alone, such as an Employees table, and others are
for obsolete equipment). My plan was to track not only the current
status of the seismic sites/equipment and lab equipment, but the history
as well, so that you would know the instrumentation and response
characteristics for any site, for any past date. I did take a course in
SQL, which helped.
It also includes several earthquake catalogs for California (NCEDC,
SCEDC, Townley-Allen, and our own, which is still under construction)
and a table of nuclear blasts. I hope to relate our quake catalog to the
others, eventually.
I do have a table for Field Maintenance visits which is loosely tied to
the Station table, but it's purely a brute-force data entry thing,
certainly not elegant (yet). It (the database) is capable of tracking
down to sensor level, so that if you switch a sensor but keep the same
datalogger, or vice-versa, it can follow that. It will also help keep
track if you replace something temporarily, fix it, and put it back.
Everything is tracked by serial number.
The trick to tracking the history between two items is using three
tables instead of two. For example, one table is for
Recorders/Dataloggers, another is for Sensors, and there is an
in-between table (tblRecSens) which associates which sensor goes with
which datalogger over time. The same is true for Station,
Recorder/Datalogger and an in-between table, tblStaRec, etc. Again, it's
not especially elegant, but it works for me.
I am not a Visual Basic whiz, so that will be the biggest challenge to
me, making everything work smoothly without having to go down into the
individual tables to enter the data, which is what I'm doing now.
Access has a 2Gb limit on the database as a whole, and on each
individual table. So far, I'm up to about 270Mb after "packing" (By the
way, do not use the "automatic pack-on-close" feature, it will destroy
your data! I learned this the hard way).
At some point, we hope to migrate over to MySQL or maybe Oracle, but
that will require a consultant and lots of real money. I expect I'll be
using Access for a long while, as the state is worse-than-broke (in the
hole) and they're only paying me for 36 hrs/week as of Friday...
I have found Access to be easy to use and it seems to be fairly
forgiving. You can add fields on the fly, change field lengths and data
types, etc. It appears to be designed for the mere mortal who just wants
to get his job done, which is me. I literally started learning it with
the book "Access 2003 for Dummies" which is actually pretty good, but I
now have several other Access books for reference purposes. DO NOT buy
the book "VBA for Dummies", which was written by someone who should
never be allowed to write another book!
Jim Agnew, M.S.
Sr. Seismologist/Section Chief
Earthquake Engineering Section
Dam Safety Branch
Operations & Maintenance Division
Calif. Dept. of Water Resources
1416 9th St., Room 204-10
Sacramento, CA 95814
Tel. (916) 653-6150
Email: jagnew at water.ca.gov
-----Original Message-----
From: anss-netops-bounces at geohazards.usgs.gov
[mailto:anss-netops-bounces at geohazards.usgs.gov] On Behalf Of
anss-netops-request at geohazards.usgs.gov
Sent: Monday, February 09, 2009 2:00 PM
To: anss-netops at geohazards.usgs.gov
Subject: ANSS-netops Digest, Vol 8, Issue 2
Send ANSS-netops mailing list submissions to
anss-netops at geohazards.usgs.gov
To subscribe or unsubscribe via the World Wide Web, visit
https://geohazards.usgs.gov/mailman/listinfo/anss-netops
or, via email, send a message with subject or body 'help' to
anss-netops-request at geohazards.usgs.gov
You can reach the person managing the list at
anss-netops-owner at geohazards.usgs.gov
When replying, please edit your Subject line so it is more specific
than "Re: Contents of ANSS-netops digest..."
Today's Topics:
1. Re: Database (David S. Croker)
2. Re: Database (Nathan L Edwards)
3. Re: Database (Robert Busby)
4. Re: Database (David S. Croker)
5. ANSS Netops Database (Kyle Persefield)
----------------------------------------------------------------------
Message: 1
Date: Mon, 09 Feb 2009 08:45:50 -0800
From: "David S. Croker" <croker at usgs.gov>
Subject: Re: [ANSS-netops] Database
To: Mark E Meremonte <meremonte at usgs.gov>, "John H. Parker"
<jhparkr at bellsouth.net>
Cc: anss-netops at geohazards.usgs.gov
Message-ID: <OF1C00C9AF.B179FCB5-ON88257558.005C168B at usgs.gov>
Content-Type: text/plain; charset="iso-8859-1"
John,
We are likewise trying to develop a Microsoft
Access database to keep track of equipment
inventories and site equipment and
documentation. It has been a long road to try to
get a functioning database, and we aren't there
yet. It has helped that we finally have someone
in-house take on the building of it that knows a
lot more about databases than me and the other
technicians. Folks in SoCal are also trying to
wrap their heads around a database and have asked
about what we are doing, but we haven't updated them in too long.
Unfortunety for you, the first stage of the
database will NOT include site visit
tracking. That is a long-term goal. Our
priority at this time is to finally document what
we actually have in the field (and on the
shelf). It will also be a depository for site
photos, maps, diagrams, and whatever
documentation we use to describe a site. I
certainly don't mind sharing notes with you and vice versa.
Dave
At 10:23 AM 2/5/2009, Mark E Meremonte wrote:
>John,
>
>We are using INV
>(<http://www.isti.com/products/inventory-digit>http://www.isti.com/prod
ucts/inventory-digit)
>to manage ANSS_BB equipment and responses (which
>also generates SEED volumes) at our stations and
>our spare equipment in our ANSS-BB depot.
>
>For keeping track of station maintenance/service
>we are using Request Tracker
>(<http://bestpractical.com/rt/>http://bestpractical.com/rt/)
>both for ANSS-BB and GSN networks. We have been
>using officially as of 01-JAN-2009.
>
>Regards,
>Mark
>
>***********************************************************************
*******
>Mark E. Meremonte Geophysicist USGS/ANSS/NEIC
>
>U.S. Geological Survey Work: 303-273-8670 Cell: 303-478-5766
>MS 966, Box 25046 Email: meremonte at usgs.gov
>Denver, CO 80225 Web:
><http://geohazards.cr.usgs.gov/>http://geohazards.cr.usgs.gov
>
>Ship: U.S. Geological Survey, 1711 Illinois St., Golden, CO 80401
>***********************************************************************
*******
>
>
>
>From: "John H. Parker" <jhparkr at bellsouth.net>
>To: anss-netops at geohazards.usgs.gov
>Date: 02/05/2009 18:06
>Subject: [ANSS-netops] Database
>Sent by: anss-netops-bounces at geohazards.usgs.gov
>
>
>
>
>Hello,
>
>I wanted to ask if anyone is using a database to keep track not only of
>their equipment, but their service visits. I have been working on one
in
>Microsoft Access, but if somebody else has already done this please let
>me know.
>
>Thanks,
>John Parker
>Center for Earthquake Research and Information (CERI)
>University of Memphis
>
>--
>Two roads diverged in a wood, and I?
>I took the one less traveled by,
>And that has made all the difference.
>
>Robert Frost - The Road Not Taken
>
>_______________________________________________
>ANSS-netops mailing list
>ANSS-netops at geohazards.usgs.gov
><https://geohazards.usgs.gov/mailman/listinfo/anss-netops>https://geoha
zards.usgs.gov/mailman/listinfo/anss-netops
>
>
>_______________________________________________
>ANSS-netops mailing list
>ANSS-netops at geohazards.usgs.gov
>https://geohazards.usgs.gov/mailman/listinfo/anss-netops
__________________________________________________________
USGS - Earthquake Hazards Team - NCSN Field Operations Manager
David S. Croker office (650)
329-4697
345 Middlefield Rd, MS 977 fax (650) 329-4732
Menlo Park, CA 94025 cell (650) 465-4334
email: croker at usgs.gov
Quake info: http://quake.wr.usgs.gov/
USGS URL: http://www.usgs.gov/
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://geohazards.usgs.gov/pipermail/anss-netops/attachments/20090209/ff
775c19/attachment-0001.html
------------------------------
Message: 2
Date: Mon, 9 Feb 2009 11:17:01 -0800
From: Nathan L Edwards <nedwards at unr.edu>
Subject: Re: [ANSS-netops] Database
To: "John H. Parker" <jhparkr at bellsouth.net>,
<anss-netops at geohazards.usgs.gov>
Message-ID: <BFD8A18AD068BA458DE09B22529E4FE0AA5113 at UNRX1.unr.edu>
Content-Type: text/plain; charset="iso-8859-1"
Hey all,
We are looking to implement something similar. Someone I talked with
said their lab was just using a shared email account (say,
stationvisit at seismo.unr.edu) just for station visits which all techs
have the password for. Visit records are sent to the account from the
tech's account, and records are reviewed from the stationvisit account.
No database to construct, highly portable, and it's easily searchable by
date, tech (from line) and station (subject line), and you can easily
send records to your own email account. As an added bonus, this can
easily be done from a Blackberry in the field (where there's coverage
anyway). We haven't yet decided if this is what we will be doing, but I
thought it might be worth sharing.
-Nathan
Nathan Edwards
Development Technician
Nevada Seismological Laboratory
University of Nevada Reno
Reno, NV 89557-0174
o: 775-784-1106
m: 775-303-5899
-----Original Message-----
From: anss-netops-bounces at geohazards.usgs.gov on behalf of John H.
Parker
Sent: Thu 2/5/2009 9:51 AM
To: anss-netops at geohazards.usgs.gov
Subject: [ANSS-netops] Database
Hello,
I wanted to ask if anyone is using a database to keep track not only of
their equipment, but their service visits. I have been working on one in
Microsoft Access, but if somebody else has already done this please let
me know.
Thanks,
John Parker
Center for Earthquake Research and Information (CERI)
University of Memphis
--
Two roads diverged in a wood, and I-
I took the one less traveled by,
And that has made all the difference.
Robert Frost - The Road Not Taken
_______________________________________________
ANSS-netops mailing list
ANSS-netops at geohazards.usgs.gov
https://geohazards.usgs.gov/mailman/listinfo/anss-netops
------------------------------
Message: 3
Date: Mon, 09 Feb 2009 19:51:28 +0000
From: Robert Busby <busby at iris.edu>
Subject: Re: [ANSS-netops] Database
To: Nathan L Edwards <nedwards at unr.edu>
Cc: anss-netops at geohazards.usgs.gov, "John H. Parker"
<jhparkr at bellsouth.net>
Message-ID: <49908940.9020602 at iris.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Hello netops,
The EarthScope Transportable Array uses a system for station visits
quite similar to that Nathan describes which was based, in turn, on a
similar system at Caltech for the SCSN.
We have text based templates for various types of station visits;
construction, installation, service, removal. The message is sent from
the tech's own mail account to a maillist.
The subject line includes the type of visit and the station code. One
of the recipients in the maillist is a program that archives the email
and puts the message in a preformed station reports web page for that
station.
Other maillist recipients can easily access the messages, sort and
search either within their own mail client or on the web based tool. We
also maintain an equipment inventory database but this
captures details of the specific equipment (purchase date,
modifications, version, other key information) and does not attempt to
describe the station details. The installation and service report
includes
serial numbers of equipment. I can provide some examples if interested.
For us, this email system has scaled very well to activities which might
include 5-8 visit postings a day. Attachments, photos, or unusual messy
details are easily accommodated. A side benefit is that
it builds a bit of a community, as recipients are aware of other
people's efforts (in trying conditions) and the reporting can be
engaging and entertaining, depending on the circumstances.
Even account administrators can use the reports to corroborate invoices
or billed activities-reducing the need to compile and file additional
reports. It serves many purposes.
It is also easy to execute in the field or back at the hotel, but does
not require an online connection-at least while creating a report.
One problem that is solved by the web based archive is its easy review
the history of a station over a few years. One entry we find useful is
to convey items to be done on the next visit.
cheers,
Robert Busby
Nathan L Edwards wrote:
> Hey all,
>
> We are looking to implement something similar. Someone I talked with
said their lab was just using a shared email account (say,
stationvisit at seismo.unr.edu) just for station visits which all techs
have the password for. Visit records are sent to the account from the
tech's account, and records are reviewed from the stationvisit account.
No database to construct, highly portable, and it's easily searchable by
date, tech (from line) and station (subject line), and you can easily
send records to your own email account. As an added bonus, this can
easily be done from a Blackberry in the field (where there's coverage
anyway). We haven't yet decided if this is what we will be doing, but I
thought it might be worth sharing.
>
> -Nathan
>
> Nathan Edwards
> Development Technician
> Nevada Seismological Laboratory
> University of Nevada Reno
> Reno, NV 89557-0174
> o: 775-784-1106
> m: 775-303-5899
>
>
>
>
> -----Original Message-----
> From: anss-netops-bounces at geohazards.usgs.gov on behalf of John H.
Parker
> Sent: Thu 2/5/2009 9:51 AM
> To: anss-netops at geohazards.usgs.gov
> Subject: [ANSS-netops] Database
>
> Hello,
>
> I wanted to ask if anyone is using a database to keep track not only
of
> their equipment, but their service visits. I have been working on one
in
> Microsoft Access, but if somebody else has already done this please
let
> me know.
>
> Thanks,
> John Parker
> Center for Earthquake Research and Information (CERI)
> University of Memphis
>
>
--
============================================================
Robert W. Busby
Transportable Array Manager 508-801-7628
USArray / EarthScope 37 Haynes Avenue
www.earthscope.org/usarray Falmouth MA USA
02540-2312
------------------------------
Message: 4
Date: Mon, 09 Feb 2009 13:36:23 -0800
From: "David S. Croker" <croker at usgs.gov>
Subject: Re: [ANSS-netops] Database
To: anss-netops at geohazards.usgs.gov
Message-ID: <OF56D2CCCE.973A911D-ON88257558.0076B026 at usgs.gov>
Content-Type: text/plain; charset="us-ascii"; format=flowed
Nathan and Bob,
That's a really interesting idea. I can see it's merits. I like the
idea of having the text-based templates too in order to keep things a
bit organized. I would be interested in seeing examples of the
different types you have.
Dave
At 11:51 AM 2/9/2009, Robert Busby wrote:
>Hello netops,
>The EarthScope Transportable Array uses a system for station visits
>quite similar to that Nathan describes which was based, in turn, on a
>similar system at Caltech for the SCSN.
>We have text based templates for various types of station visits;
>construction, installation, service, removal. The message is sent from
>the tech's own mail account to a maillist.
>The subject line includes the type of visit and the station code. One
>of the recipients in the maillist is a program that archives the email
>and puts the message in a preformed station reports web page for that
>station.
>Other maillist recipients can easily access the messages, sort and
>search either within their own mail client or on the web based tool.
We
>also maintain an equipment inventory database but this
>captures details of the specific equipment (purchase date,
>modifications, version, other key information) and does not attempt to
>describe the station details. The installation and service report
includes
>serial numbers of equipment. I can provide some examples if
interested.
>
>For us, this email system has scaled very well to activities which
might
>include 5-8 visit postings a day. Attachments, photos, or unusual
messy
>details are easily accommodated. A side benefit is that
>it builds a bit of a community, as recipients are aware of other
>people's efforts (in trying conditions) and the reporting can be
>engaging and entertaining, depending on the circumstances.
>Even account administrators can use the reports to corroborate invoices
>or billed activities-reducing the need to compile and file additional
>reports. It serves many purposes.
>It is also easy to execute in the field or back at the hotel, but does
>not require an online connection-at least while creating a report.
>
>One problem that is solved by the web based archive is its easy review
>the history of a station over a few years. One entry we find useful is
>to convey items to be done on the next visit.
>cheers,
>Robert Busby
>
>Nathan L Edwards wrote:
> > Hey all,
> >
> > We are looking to implement something similar. Someone I talked
> with said their lab was just using a shared email account (say,
> stationvisit at seismo.unr.edu) just for station visits which all
> techs have the password for. Visit records are sent to the account
> from the tech's account, and records are reviewed from the
> stationvisit account. No database to construct, highly portable,
> and it's easily searchable by date, tech (from line) and station
> (subject line), and you can easily send records to your own email
> account. As an added bonus, this can easily be done from a
> Blackberry in the field (where there's coverage anyway). We
> haven't yet decided if this is what we will be doing, but I thought
> it might be worth sharing.
> >
> > -Nathan
> >
> > Nathan Edwards
> > Development Technician
> > Nevada Seismological Laboratory
> > University of Nevada Reno
> > Reno, NV 89557-0174
> > o: 775-784-1106
> > m: 775-303-5899
> >
> >
> >
> >
> > -----Original Message-----
> > From: anss-netops-bounces at geohazards.usgs.gov on behalf of John H.
Parker
> > Sent: Thu 2/5/2009 9:51 AM
> > To: anss-netops at geohazards.usgs.gov
> > Subject: [ANSS-netops] Database
> >
> > Hello,
> >
> > I wanted to ask if anyone is using a database to keep track not only
of
> > their equipment, but their service visits. I have been working on
one in
> > Microsoft Access, but if somebody else has already done this please
let
> > me know.
> >
> > Thanks,
> > John Parker
> > Center for Earthquake Research and Information (CERI)
> > University of Memphis
> >
> >
>
>--
>============================================================
>Robert W. Busby
>Transportable Array Manager 508-801-7628
>USArray / EarthScope 37 Haynes Avenue
>www.earthscope.org/usarray Falmouth MA USA
02540-2312
>
>_______________________________________________
>ANSS-netops mailing list
>ANSS-netops at geohazards.usgs.gov
>https://geohazards.usgs.gov/mailman/listinfo/anss-netops
------------------------------
Message: 5
Date: Mon, 09 Feb 2009 15:00:16 -0700
From: Kyle Persefield <kpersefield at usgs.gov>
Subject: [ANSS-netops] ANSS Netops Database
To: anss-netops at geohazards.usgs.gov
Message-ID: <OFE93A84D0.9ABC29E8-ON87257558.0078E04B at usgs.gov>
Content-Type: text/plain; charset="us-ascii"; format=flowed
All,
ASL does not have an all-in-one means of tracking all activity
concerning our stations.
We currently use a Access database to track equipment actions. We
may change this database in the future to something PASSCAL like. We
really want to barcode our equipment.
We also have station mailboxes and filters to track E-mail concerning
station problems.
We also use Request-Tracker (Freeware, see
http://www.bestpractical.com/rt/). We use this software to generate
trouble tickets\workorders and it can be linked to the E-mails. Over
time this can develop into quite a powerful tool to for your station
knowledge base, history and trend analysis.
Lastly, we maintain station documents on individual stations on a
common use networked hard drive. Soon this folder will be
reorganized and documents will be linked via a local "Wiki" page and
has a powerful search engine to find things.
If someone comes up with an all-in-one process, we would like to
know. The best product we'd like to try and comes closest to this
all-in-one documents/content management program, is the Microsoft
Sharepoint Server see
http://www.microsoft.com/sharepoint/default.mspx. One of the
features, you can move your Access database to Sharepoint.
That's my two cents worth...!!!
For a sure response send E-Mail to gsnmaint at usgs.gov, or
gsn-xxxx(your station code)@usgs.gov
Regards,
Kyle E. Persefield
Honeywell Int'l HTSI, Field Engineer
USGS Albuquerque Seismological Laboratory Contractor
Observatory "Trouble" Hotline Telephone: 505-846-5646
Tel: 505-846-7597
Fax: 505-846-6973
Web: http://aslwww.cr.usgs.gov/
Mailing Address:
U S Geological Survey
Albuquerque Seismological Laboratory
P O Box 82010
Albuquerque, NM 87198-2010
------------------------------
_______________________________________________
ANSS-netops mailing list
ANSS-netops at geohazards.usgs.gov
https://geohazards.usgs.gov/mailman/listinfo/anss-netops
End of ANSS-netops Digest, Vol 8, Issue 2
*****************************************
More information about the ANSS-netops
mailing list