Structure to replace multiple spreadsheet copies

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently use a spreadsheet with 100 machines on which I update 3
fields/machine each month with analysis notes. I begin by making a copy of
last months sheet, paste in the same workbook, rename the sheet for the
current month then update fields with current data (notes). Most fields stay
the same but about 20% of them change each month. Two of the fields are text
and one is a priority ranking 1-10. The text fields are notes which could be
unique for each machine each month but as it turns out, many machines share
common entries over time.
It is printed each month in spreadsheet form as part of a larger report and
for use on-site in the following months testing. It is also used to glance
at prior history 2 - 24 months back when analyzing raw data.
What is the most efficient structure in a database to accomplish the same
end as described above with the spreadsheet?
 
well, a VERY rough idea would be a table listing all machines, as

tblMachines
MachineID (primary key)
<any other fields that describe a specific machine - and nothing else!>

tblData
DataID (primary key, Autonumber)
MachineID (foreign key from tblMachines)
DataDate (you could use just a text field here, for month, but in the long
run i think a date field will be more useful to you. for one thing, it will
include the year, so you won't need to store that elsewhere. also, Access
offers many ways to manipulate date fields so you can easily analyze data by
month, quarter, year, etc.)
FirstTextField (whatever name is appropriate)
SecondTextField (ditto)
DataPriority (Number field; if the ranking values will never go higher than
255, set the field size to Byte)

so, one record will be the text, text, and priority rank for one machine for
one month. if you have 100 machines, you'll enter one hundred records every
month into this table - one record for each machine.

as i said at the beginning, this is VERY rough, since the information you
provided is very sketchy. i strongly recommend that you do *not* rely on the
above suggested design, but instead read up on relational design principles.
coming from Excel, you are very vulnerable to making many common mistakes in
imposing a spreadsheet-like design on Access. and that is a recipe for major
problems, if not outright disaster. also, you'll need to learn to separate
the idea of data storage and data display. in Excel they're pretty much the
same thing, but in Access they're completely separate - the needs of one
will not dictate the requirements of the other.

for more information, and to get started, suggest you go to
http://home.att.net/~california.db/tips.html.
once you learn to use Access and leverage its' power, i think you're going
to be amazed at how much easier your work will become. Access is very much a
"set it up once and use it forever" program, so the kind of tedious setup
you described going through over and over again, month after month after
month, will be a thing of the past. keep that in mind, because the initial
setup will probably require a chunk of time and effort. but if you're a fan
of "set it up once, and make sure it's right, and then setup is done -
period", then you're going to love Access!

hth
 
For years I have been an end user of a program in Access someone else wrote
and am now writing my own. Have been reading up, taking MS online free
course and have convinced myself there must be a more elegant way to
accomplish what the spreadsheet did for me in the past. This is actually
just a fraction of a larger scope of reports for the machines I test.
I conceive a form which through queries, filters, whatever, will display
what the spreadsheet once did and behind it and any printed report would be
the tables you describe. Since my notes typically only change by one numeral
or a couple of words each month as I analyze, I do not want to rewrite the
entire note or copy & paste each entry then make the small change. As
before, 80% of the records will not change each month; 80% of last months
entries still need to display or report as valid for this month as well -
perhaps a check box which would initiate this months date for last months
entry. Or conversely, by default, have last months notes automatically
tagged with this month's date and those 20% which need to be edited could be
done directly in the form.
I'm getting a better picture as I write but it gets overwhelming for this
beginner. I believe the details will come together as I build the basic
tables, etc. And I am a believer in investing the time up front.
Thank you for taking the time to consider my problem. I will use some of
the specific suggestions re: field type.
 
Back
Top