access and excel

[Ross]

Hi,
I have created an excel workbook in Excel (office 2000) with multiple
sheets that the staff at my agency (AURORA of CNY -- not for profit serving
the blind, visually impaired, deaf and hard of hearing of Central New York
State) use to keep track of all aspects of our clientelle -- case notes,
reports, and other required forms. The way I have it designed, a workbook
is saved for each client. This works fine, but it sure uses up a lot of
space on the hard drive. What I would like to know: Is it possible to use
this spreadsheet in conjunction with Microsoft access? Using the workbook I
created, can I export/import all of the fields I created to/from an access
database? I have tried to figure out how access works, but I do not find it
as intuitive as excel.
Ross E. Deforrest
AURORA of CNY
518 James st,
Syracuse, NY 13203

 

[Keith Wilby]

Hi,
I have created an excel workbook in Excel (office 2000) with multiple
sheets that the staff at my agency (AURORA of CNY -- not for profit
serving the blind, visually impaired, deaf and hard of hearing of Central
New York State) use to keep track of all aspects of our clientelle -- case
notes, reports, and other required forms. The way I have it designed, a
workbook is saved for each client. This works fine, but it sure uses up a
lot of space on the hard drive. What I would like to know: Is it
possible to use this spreadsheet in conjunction with Microsoft access?
Using the workbook I created, can I export/import all of the fields I
created to/from an access database? I have tried to figure out how access
works, but I do not find it as intuitive as excel.
Ross E. Deforrest
AURORA of CNY
518 James st,
Syracuse, NY 13203

Why do you want to use Access instead of Excel? They're two completely
different animals - one's a spreadsheet and the other enables you to build
relational database applications. Access won't make a "database" for you
out of the box, you have to design the functionality yourself.

If you want to convert your spreadsheets into a relational database then
there's a long haul ahead of you since, from the tone of your posting,
you're fairly new database design. The first thing to do IMO is to sketch
out all of your entities and the relationships between them (eg a "client"
is an entity with attributes such as name, address etc) on paper. This
would then form the basis of your design.

Just a little food for thought

Keith.
www.keithwilby.com

 

[Ross]

Thanks Keith
The reason why I want to use the forms I created in Excel is because I
was able to t create them in excel. How does one go about creating forms in
access? I mean the actual fields that you fill in on the screen, that look
the same as they do on the screen that they will when they are printed. The
forms I created in Excel work great just as they are, but as I said, they
are very wasteful of space -- each workbook has about 13 sheets in it. The
reason I thought the marriage between Ecel and Access might work, is that I
had already created the same set of forms using Adobe Acrobat writer -- this
form over 100 pages long, and information could be entered into it and then
exported to a small data file, that could be re-mported into the form as
needed. The problem with this is that everyone at my agency would have to
have the adobe Writer program on their machine and my agency could not
afford that, so that is why I went with Excel. Can one somehow design forms
in access, that will end up looking just like printed pages on the screen?
Anyway Keith, I went to your web page and saw your email there, so I will
attach a copy of that workbook, so you can see what I have done.
Thanks again
Ross

 

[Keith Wilby]

Thanks Keith
The reason why I want to use the forms I created in Excel is because I
was able to t create them in excel. How does one go about creating forms
in access? I mean the actual fields that you fill in on the screen, that
look the same as they do on the screen that they will when they are
printed. The forms I created in Excel work great just as they are, but as
I said, they are very wasteful of space -- each workbook has about 13
sheets in it. The reason I thought the marriage between Ecel and Access
might work, is that I had already created the same set of forms using
Adobe Acrobat writer -- this form over 100 pages long, and information
could be entered into it and then exported to a small data file, that
could be re-mported into the form as needed. The problem with this is
that everyone at my agency would have to have the adobe Writer program on
their machine and my agency could not afford that, so that is why I went
with Excel. Can one somehow design forms in access, that will end up
looking just like printed pages on the screen? Anyway Keith, I went to
your web page and saw your email there, so I will attach a copy of that
workbook, so you can see what I have done.
Thanks again
Ross

No offence Ross but you're trying to run before you can walk. If you need
to ask the question "Can one somehow design forms in access, that will end
up looking just like printed pages on the screen?" then you need to start
doing some research into Access. The answer BTW is "Yes" but why would you
want it to? Forms are a means of manipulating data, they are not for
printing (even though Access will allow it). You print data using *reports*
which you can format to your heart's content.

You can make your forms look like an Excel spreadsheet if your design is
fairly flat but if you're going to get the most out of Access you need to
lose the "spreadsheet" blinkers. Again I'm not being rude just realistic


My advice is to get Access opened up and have a play around. Have a look at
the sample database "Northwind" that ships with Access. It's no great work
of art and many what I would term "standards" are not adhered to but it will
get you started.

Regards,
Keith.

 

[Ross]

Thanks Keith,
Just want to give you a little background here: When I came to my current
job, everyone was using paper -- you know chicken-scratching with archaic
writing implements -- to fill out the forms that I created with excel so
they could leave the ice-age and join the 21st century. When I created the
system we use, I had to make those forms EXACTLY like the paper forms -- a
very tedious process with Excel -- in order for them to be accepted by the
state. My question is: If I go to the trouble to learn access and then
create the database, will I be able to create reports that when printed,
will look EXACTLY like the forms you see in the excel workbook I sent you --
every field, every box, every check? Also, I don't know what a worksheet
blinker is, so it would be difficult to lose one. Also don't know what you
mean by a "flat" design.
Ross

 

[Keith Wilby]

Thanks Keith,
Just want to give you a little background here: When I came to my
current
job, everyone was using paper -- you know chicken-scratching with archaic
writing implements -- to fill out the forms that I created with excel so
they could leave the ice-age and join the 21st century. When I created
the
system we use, I had to make those forms EXACTLY like the paper forms -- a
very tedious process with Excel -- in order for them to be accepted by the
state.

Been there, got the T-shirt ;-)

My question is: If I go to the trouble to learn access and then
create the database, will I be able to create reports that when printed,
will look EXACTLY like the forms you see in the excel workbook I sent
you --
every field, every box, every check?

I haven't had chance to have a look at what you sent yet but I've put
together some pretty complex reports myself, I wouldn't worry about it but
I'll take a look tonight just to be sure.

Also, I don't know what a worksheet
blinker is, so it would be difficult to lose one.

Actually I meant spreadsheet - a lot of people come to Access still thinking
in terms of spreadsheets, they think Access is a beefed up version of
Excel - it isn't. That was all I was commenting on really.

Also don't know what you
mean by a "flat" design.

By flat I mean 2 or 3 related tables (for example) - you could perhaps make
a "spreadsheet view" out of them, but anything more complex and you'd be
looking at forms and subforms which look nothing like a spreadsheet .