Excel vs Access

[WLMPilot]

I have a project that I am being asked to develop for work. It will be a
method to QA reports using a grading method for sections within the report.
Initially, I was leaning towards using EXCEL with a userform to provide the
input for the grading of the report.

As I thought more about it, I realize that I can have around 1000 reports
per year per person that will be QA'd. I was going to have a workbook for
each employee. I am now wondering if ACCESS will be better suited do to the
number of reports and use Excel for the boss to collect a summary of the data
from Access.

I was also going to use WORD in the event that an employee receives a
written letter pertaining to his/her performance.

Any thoughts?

Thanks,
Les

 

[Bob Phillips]

Will the 1000 reports be (as I would expect) 1000 instances of the same
report just for different periods/people, or 1000 very different report? If
the former, develop a template which can be loaded with the appropriate
data.

Whether you use another Excel workbook for the data, or an Access database
(or even SQL Server as you can use the free Express edition), you should
definitely separate the code workbook from the data. If you use an Access
database, you have to get the data somehow; you could use MS Query, or as I
do because I feel it gives me more control, use ADO and SQL queries. Sounds
difficult, but not that hard.With a database, you can have a proper
relational structure and data updates are simple as well as querying. If you
use another workbook, you can just open that data, and use that within Excel
VBA, but having relational data is harder (although it can be done with
tables) and querying and updating takes more effort.

 

[FSt1]

hi
i did something similar at my last job.
we used access to input and house the raw data then use excel as a front end
to display/output the data in report form. we used MS query to get the data
from access. many reports were "click a button" in excel for a look/see
report. others use vb in excel to process the data from access a tad further.
Access and excel work well togeather. access is far better suited to house
the data than excel and excel is far better to display the data than access.
that would be the way i would go.

my thoughts.
regards
FSt1

 

[WLMPilot]

This is for a county Emergency Medical Service. The report is the patient
care report that is generated via software (have no control over this
software). The supervisor will review the report to see if, for example the
Paramedic followed the protocol for the particular illness being treated.
There are other criteria that will be graded. Therefore, it will be around
1000 reports per person. I have no way to know an exact number of reports
that a person will generate in a year but that is based on about 8 reports
per shift per person as a guess.

I am new to the terminology dealing with everything you mentioned, ie ADO,
SQL, and MS QUERY. I use to be pretty good with the databases from the 1980s
but have done very little with Access. What I have learned in Excel with
VBA, etc has been from this forum and research. I understand the underlying
goal of using Access and feel that is the way to go. It will be a learning
process for me in Acess just as it was with Excell VBA.

If you could describe the above terms and it use, I would appreciate it.

Thanks,
Les

 

[FSt1]

hi
MS Query is built into excel complete with setup wizard.
on the tool bar>data>import external data>new database Query
follow the wizard.
ADO is a tad different. see this site for more details.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=889
SQL i wouldn't worry about now. MS Query uses SQL but the wizard will write
the SQL language for you.

the purpose of a data base is to store data. what data? you need to make a
list of all of your data bits that you wil be storing ie name, address, city,
state, other, ect.
will name be 1 field or 3? when you have your list, you can build your
tables in access. once you have your tables, access has a wizard that will
create your imput form. but the tables come first. after you have your forms
is a bad time to realize that you need more data fields in your tables.. this
could cause costly delays and redos.
regards
FSt1