Access vs. Excel: data entry

W

Walter Donavan

Whether 'tis better to enter volumes of simple data in an Accesse form, or
by grappling with the two-headed beast, to enter it in Excelle, where it is
more seemly, and more easily filtered (And thus more easily
validity-chequed), and then export it via fastest trireme to ye wildes of
Accesse?

And if do I choose the two-headed beast, what to do about ye pesky
AutoNumber Primary Keys, which Excelle loveth to call Numbers and Accesse
loveth to reject from Excelle?

My Lordes, please respondeth soonest.

Yr. Obdt. Svt,

Walter Donavan
www.revelation7stages.com
www.1stbooks.com/bookview/15479
 
M

M.L. Sco Scofield

Why Access of courseth! (What would you expect in an Access group? Da. :)

First question is what are the data enterers comfortable with? It's *very*
easy to make a form look like a spreadsheet using datasheet view. This gives
you all of the data validation capabilities of a form right there in Access.
Then you don't need to do a lot of post-entry validation. And if you still
do, you have all of Access's filtering and sorting capabilities. Which are
every bit as good as Excel's once you learn them. Not to mention being able
to make validation queries, which Excel can't do.

As far as the "pesky" AutoNumber goes, yes it is. If you stay in Access,
*no* problem. If you're importing from Excel or a text file, you do not
import into the AutoNumber field. Access will automatically assign it during
the import. If you need to assign the number during data entry, then you
shouldn't be using an AutoNumber field to begin with.

I've had data entry people do 10s of 1,000s of records in Access with no
problems.

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
J

John Vinson

Whether 'tis better to enter volumes of simple data in an Accesse form, or
by grappling with the two-headed beast, to enter it in Excelle, where it is
more seemly, and more easily filtered (And thus more easily
validity-chequed), and then export it via fastest trireme to ye wildes of
Accesse?

And if do I choose the two-headed beast, what to do about ye pesky
AutoNumber Primary Keys, which Excelle loveth to call Numbers and Accesse
loveth to reject from Excelle?

My Lordes, please respondeth soonest.
Click to expand...

LOL!!!

I won't even TRY to reply in style, much as I'd love to.

Access is VERY capable of filtering and validity-checking data; and an
Access form can be made to look very like a spreadsheet if that view
is appealing (I dislike it myself but de gustibus non disputandum
est). And it is NOT obligatory to have autonumbers in an Access table;
if your data contains a field (or a group of up to ten fields) which
uniquely identify a record, that field (or those fields) can be made
the primary key.

You can (if you are bold and undeterred by programming complexities)
use Excel spreadsheets directly linked to Access tables or vice versa.
Export is but one way of linking data betwixt the two realms.
 
W

Walter Donavan

It looketh like verily I must to learn more Accesse. Or is that Excesse?

Heartiest thankes to ye both.

I've been using all major Office components for years--except Access. I
decided to learn Access, and I, supposedly a computer guru for 40 years
(yup, since the early 1960's), now find myself in the novice seat. Don't
like it. Frustrating. I want to learn Access in a month, and it's not
happening, despite three books and a self-imposed database project (track
hundreds of backup and program CDs). However, I am learning it, and you kind
people in the ngs are helping a lot.
 
J

John Vinson

I've been using all major Office components for years--except Access. I
decided to learn Access, and I, supposedly a computer guru for 40 years
(yup, since the early 1960's), now find myself in the novice seat. Don't
like it. Frustrating. I want to learn Access in a month, and it's not
happening, despite three books and a self-imposed database project (track
hundreds of backup and program CDs). However, I am learning it, and you kind
people in the ngs are helping a lot.
--
Click to expand...

<chuckle> Comrade! Brother! (CDC 3600 at the Michigan State University
Computer Center in 1963 was my first exposure...)

Yep. The learning curve for Access is MUCH steeper than other Office
apps, due to the fact that - while it's very logical, elegant, and
powerful - relational database design is not a self-evident or trivial
concept.

Hesitate not to come hither for instruction, correction, and friendly
cameraderie.
 
W

Walter Donavan

IBM 1420 (1620?), Fortran, with punched cards, University of Miami (Coral
Gables), c. 1963-64.

Thanks for your kindness, John. And don't worry, I have plenty of questions!
 
A

Albert D. Kallal

(And thus more easily
validity-chequed)
Click to expand...

You mean checked by humans..right?

Excel is a great little tool, but there has a been a saying in the computer
industry for as long as I can remember:

Garbage in = Garbage out

The worst problem with Excel is that data entry has NO validation options at
all. In ms-access, if you have a Title field, you can restrict the field to:

Mr, Mrs., Doctor etc. (you built this custom list of ALLOWABLE entries)

Now, why is input validation so important? Lets say your boss comes along
and asks for a list of all the doctors in your mailing list.

In Excel you will likely have:


Doc, Doctor, Doctor., Doc.

Who knows how many variations of the doctor has been typed in. Even just
being in-consistent as to a "." being entered will cause any search, or
condition to fail (Doc is NOT the same as Doc. (with a period). Well, you
now had all this data entry done, but just created one HUGE MESS a data
system. Since there is NO validly checking in your data input when using
excel, then all of a sudden, a simple trivial problem like print a list of
doctors becomes a huge night mare because no data checking occurs at input
time. The boss will look at you, and then bring in some consultant to figure
out what the heck happened, and why such incompetence exists for such a
simple request! ;-)

Further, by using a table with a list of possible titles (salutations), you
can extend the design of the application to do some really cool things. Lets
assume we have our two tables:

tblCustomers: id, Firstname, LastName, City, title(relaton to tblTitles),
etc. etc. etc.

tblTitles:id, Title

So, our Title field might have a few records like:

id Title
32 Mr.
12 Mrs.
13 Doctor.
etc. etc. etc

Now, lets say we need to generate form letters to the customers, but for
each type of title, we want a correct salutation.

We can simply add a new column to our tblTitles like:

tblTitles:id, Title, Salutation:

We now have:
id Title Salutation
32 Mr. Dear Mr.
12 Mrs. Dear Mrs.
13 Doctor. etc .et c.etc

So, not only during data entry will users be forced to select from the list
of legitimate title types, but that also means we have perfect data checking
during entry. Further, the data entry is easier then Excel as we have a nice
drop down list of choices.

Further, we might even want to add additional columns for the salutation in
different languages. After all, I don't know of any application that does
not get extended, and new features are to be added.

Stuff like force the correct import for postal codes is another great
example. In my County all poster codes are:

letter, number, letter - number, letter, number

You use excel for data entry, and your users can type anything into the
postal code field. With ms-access, you can FORCE the input to be the above
format. In this case, the postal code might not be correct, but at least the
data is correctly entered. Once again, if you search for a postal code, but
the users are NOT consistent in how they enter the data (some might use a
space, some might put a "-" between letters etc. Any variation in the data
input once again means the data is not searchable, or of use.


I am being very kind when I say that Excel is not even close, or on the same
planet when it comes to having the ability to manage and validate data
entry. Without question the largest part of the computing industry is data
management and database systems rule this terrain, not spreadsheets. The
above is barely a pin prick as to why Excel is a horrible database system.

However, the problem with data processing is that to correctly set things
up, it takes a lot of skills.

Data Design and relational theory
Normalisation of data
SQL (structured query language)

You then have the product ms-access, which is really a software development
system. Hence, you have to learn the complexes of the product called
ms-access. After that, you can go even further and start writing code.
Ms-access code is visual basic, but with a different forms model then VB.
So, there is a lot of stairs you can climb with ms-access.

You need some books here...as trial and error will not work like it did for
other office programs.
 
D

Douglas J. Steele

Albert D. Kallal said:
You mean checked by humans..right?

Excel is a great little tool, but there has a been a saying in the computer
industry for as long as I can remember:

Garbage in = Garbage out

The worst problem with Excel is that data entry has NO validation options at
all. In ms-access, if you have a Title field, you can restrict the field to:

Mr, Mrs., Doctor etc. (you built this custom list of ALLOWABLE entries)
Click to expand...

No offence, Albert, but that capability exists in Excel as well. Under the
Data menu, there's a Validation option. It may be that few know how to
implement it.
 
A

Albert D. Kallal

Douglas J. Steele said:
The worst problem with Excel is that data entry has NO validation options
at

No offence, Albert, but that capability exists in Excel as well. Under the
Data menu, there's a Validation option. It may be that few know how to
implement it.
Click to expand...

Sure, that is most fair to point out here. Excel people have feelings too!

However, I would note that the "list" of allowable options in Excel can't be
another table (or a row/list from a sheet). And, as my per my example
additional information can not be associated with that "list". So, while
Excel does have some validation stuff it is not that great. It is kind of
like a hug without the kiss.

;-)

Ultimately, Excel does also have the use of VBA. This issue of VBA can
really cloud things further as one could add some cell verification stuff
that way.
 
W

Walter Donavan

Under the Data menu, there's a Validation option.

By golly, Douglas, there sure is. That means I just learned a new trick in
Excel as well. I particularly like that "Custom" choice.

Thanks to you too.

I just finished the Microsoft books, Excel 2002 Step by Step and Access 2002
Step by Step. Amazing how much more I know now than before I did them. But
even more amazing how *little* I know after I did them.:)
 
W

Walter Donavan

Albert,

Thank you for your long and thoughtful reply. I saved it in my Access Tech
Notes.

I have three Access books and I am wearing them out. But although they are
necessary, I learn more in a minute or two with a kind and knowledgeable
person like you than in two hours with a book.

Thank you again.
 
Top