What advantage ACCESS over EXCEL?

[CLR]

Hi All.........

I have a new Database to create that will get all of it's data by manual
input, all taken from one hand written form that is filled out daily by
Machine Operators. There will be no reference to any other Databases. It's
a Part-History record, bearing Machine number, part number, setup hours and
run hours, and a few other details like that. All in all about 25 columns
or less and 2-3000 rows. It will be sorted and filtered to analyze
efficiencies. I would do it in Excel, because that is where I am
experienced, but it has been suggested to be done in Access. The suggestors
stated their reasons as: 1- All large databases should be done in Access as
Excel takes too much room.....2- Access will provide better reporting. Is
either of these reasons valid? I don't consider this one to be large
enough to be "restricted" by Excel. Can neater things be done in Access in
this circumstance than in Excel? I cant imagine anything they might want
done with this simple database that I can't do in Excel. Anyone have any
thoughts on this, please?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Douglas J. Steele]

Access certainly can produce far better reports than Excel. Overall, though,
I'd expect the Access database to be larger than the Excel spreadsheet
containing the exact same data.

A warning, though. You can't just take your spreadsheet and dump it into
Access and expect it to be an efficient database. You need to work at your
table design: it's extremely unlikely that the correct database would have a
single table with 25 columns.

 

[Edward G]

Hi Chuck,

Hmmm. Well, the last place I worked we made machines used in the
pharmaceutical industry. Had a partmaster with about 20,000 sku's and a
machine shop with 4 guys in it. Not that I didn't already have plenty to do
but they decided they wanted me to start recording labor time in the machine
shop so they could get a better idea of how much the machines cost to build.
A worthy goal certainly, and we had an MRP program built on a Microsoft
Access platform called Alliance MRP to store the info. Off the shelf
probably about $25,000 I guess.
Now this meant issuing work orders with routers and my guess is you will end
up with about a dozen tables trying to build this yourself. Off the top of
my head I could see you needing Partmaster, Employee, JobMaster, BOM, Work
Order, WorkOrderDetails, Routing, Labor Codes, LaborHours depending on what
kind of operation we're talking about.
The advantage of using Access? If information is going to repeat itself a
lot, Access can spare you excess typing. For instance, let's say part number
015244 has a description that reads "flange, upper, pivoting arm with
recess, delrin, black,
Now, a relational database like Access can store that information once as an
attribute of the part number. When you do your data entry all you enter is
your part number. But your queries and reports will call up the description
from the PartMaster table. So, you enter that info once and never again.
Same holds true for your employees and your machines. Can Excel do that for
you?
I have to say, I have very little experience with Excel and I could be
underestimating its capabilites, but the sort of project you describe I
think would best be handled with a relational database like Access.
Just my 2 cents worth

Edward J

 

[CLR]

Thank you Sir, I appreciate your response.

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks Edward, you pretty much hit the nail on the head with your analysis
of my situation. They do already have all the info they need in their MRP
system, except the Operation Number and the Quantity of the task........I
told them by just entering these two items (instead of the whole Daily
Operation Sheet),they could get the MRP system to return whatever data they
need and I could do anything they wanted to do with it in Excel. Their
answer was "we dont trust the Standard times on the Routers", and the VP
would "beat us up if we tried to change anything at MIS", and we "prefer
ACCESS because someone told us that all large Databases should be on ACCESS
instead of EXCEL...............so, it's apparently an emotional
issue.........who knows, they may someday want to make relational calls to
other databases that I don't know about now.........maybe thats where the
dozen tables will come in, but they sure don't need them for their stated
goals right now.

The 25 columns is actually just 19, with 9 of them being a "select one"
array for excuses for not meeting goal. Only 10 actual columns to describe
the Date, Machine, Shift, PartNumber, Operation, StandardSetupTime,
ActualSetupTime, StandardRunTime, ActualRunTime, and Scrap. The Standard
times could come from another database based on the PartNumber, true, but
they want to use the "new" Standard times that are posted on the
machines.........but in EXCEL a simple VLOOKUP table would do the job as
well...........

Thanks for your explanation of ACCESS, that's pretty much like I figured,
but just wanted to hear it from someone else. As for what "can" be done in
EXCEL, well, if it's within EXCEL's size limits, then just about anything
one might want to do can be done in EXCEL........especially for one with VBA
savvy.

Again, thanks for your time and comments.........

Vaya con Dios,
Chuck, CABGx3

 

[Edward G]

Chuck,

I used to work for a place that made high voltage power supplies.
They had a huge MRP database on a mainframe system. The reference manuals
for
the different modules of the database took up three full shelves of a
bookcase. The PartMaster
table alone had close to 200 fields.
For some reason, folks didn't feel this was enough. The stockroom had a
personal computer
with a separate database, the production dept had a personal computer with
another database, the
encapsulation dept had a personal computer with yet another database, and so
on, and so on.
It took a new Operations Mgr to come in and wean everybody off their
individual databases and get
everybody back using the MRP system to get that place in order.

<<<and the VP would "beat us up if we tried to change anything at MIS">>>

The VP should shoot anyone who even suggests that the answer is to build an
external database.

My 2 cents.

Ed

 

[CLR]

Hear Hear Edward!!!!!!!!!!!

Once again, you've "confirmed suspicions"........maybe if the MRP systems
were more user-friendly and maybe if the MIS's weren't trying to keep
everybody out...........oh well,

Many thanks,
Vaya con Dios,
Chuck, CABGx3