looking for direction... Access or Excel

[jorgesalazar via AccessMonster.com]

Very much a newb with access and have the need to improve upon a db created
by another that has grown in popularity by the big guy (boss)... looking at
it i thought excel was a better choice but seeing the value of queries i am
rethinknig this. I am an intern going through a career change and have a real
interest in leaving something valuable behind or make my self look like a
real bargain and worth a full time gig.

the issue is storage of test tube vials in a freezer. There are 26 totes A
thru Z, each tote hold 6 boxes, and each box holds 81 vials. Rows A thru I
and col 1 thru 9. The details of the vials is critical and we need the
ability to search for a particular batch, lot and process. Excel made sense
to me since there can be only one vial in one spot and once the vial is
pulled the space is empty and ready to be refilled when needed. Setting up
the fixed box positions its 12,636 positions. The other beauty of excel is
that the vials go in as batches of 8 and we can add as many as five or six
batches in a day depending on volume. So it makes sense to use the pull down
and copy and paste features on the spread sheet. The big problem is
searching for the specific sets of vials and not just a sheet of vials to
look at. Laying out the sheet and page set up its 350 plus sheets of paper to
veiw the entire inventory. Not practical on a regular basis.

Now the access part. It's already in use and the big guy likes it because he
can search what he needs us to pull and then he adds empty in the key lot
field and leaves it for done. My concern is the total number if key figures
is now up to 14,500 plus and will continue to grow. When he adds " empty"
that satisfies the no duplicate rule but it concerns me the overall db will
grow huge and lend itself to corruption down the road.

i have created a better form for data entry with pull down lists to avoid bad
data entry and to make it faster than keystroking all the data... select and
tab works well on my form and i have it down to 10 steps from 15 entries.

i hope that gives you an idea of the big picture... should i make a querry
that deleted the data once he pulls the report which he then gives us so we
can pull the vials or make him another querry to create a table to delete the
results once per month? or scrap it all and go to excel and work with it? i
was able to dump the data file to excel and back to Access so that is one
less thing to worry about but i don't know where to spend the time? any
suggestions are appreciated.

 

[Jerry Whittle]

As a rule of thumb, it you are mostly doing the math, Excel is the tool. If
you are storing and retrieving data, you want a database. Sounds like you are
doing data.

You are worried about 14,500 and growing. If that is the number of rows in a
spreadsheet, you are much closer to having problems with Excel. Some very
older versions of Excel could only handle 16K rows in a spreadsheet. Excel
2003 is up to 64K while Excel 2007 can handle a million rows. In constrast,
Access can handle many millions of records in a table.

Also if you get many rows in Excel, searching can start taking a lot of
time. Whereas a properly normalized and indexed database can return data
quickly using queries, forms, and reports.

As far as deleting the data once a month, this is usually a bad idea. What
if you discover a problem with the report after the data is deleted? What if
you need to compare and contrast various months? Access, with the ability to
hold multiple records, may be a better choice.

The size of a database has little to do with corruption. Also a good
database administrator should do frequent backups to other media. If a hard
drive crashes, it doesn't matter if the lost file was a .mdb or .xls.

 

[Klatuu]

What Jerry said.

In addition, don't be trapped into "I know Excel, so I will go with it".
You are, after all, trying to please the boss and stretch your horizons. The
end result for everything you want will be better and you will have added to
your skill set.

If you need help as you go forward, this is the best place to get it.

 

[jorgesalazar via AccessMonster.com]

Thanks for the feedback. I should have included that the software is all MS
2007 and that the back up issue has been addressed by the big guy. He trusts
no one with it not even the IT guys...lol

I spent the part of the day filtering with excel and seeing some limitations
I spent the other part playing with querries and can really see the power.

If leaving the key count grow means nothing bad provided the critical data is
out there should never be a duplicate or a confusion as to what is in the
actual spot even if its been used 20 times. Then this seems to be the way to
go... access it is...

now i created a split form to enter data, next project is to see how to copy
that form and the tables that feed the lists into the old database... stay
tuned... i may be back with some how to's on that...

thanks again!

What Jerry said.

In addition, don't be trapped into "I know Excel, so I will go with it".
You are, after all, trying to please the boss and stretch your horizons. The
end result for everything you want will be better and you will have added to
your skill set.

If you need help as you go forward, this is the best place to get it.

Very much a newb with access and have the need to improve upon a db created
by another that has grown in popularity by the big guy (boss)... looking at

[quoted text clipped - 35 lines]

less thing to worry about but i don't know where to spend the time? any
suggestions are appreciated.