question for veteran Access users re: database plan

[Guest]

My question is for you veteran Access users. I’ve never made an Access
database—I was just given this assignment last week and I’ve been teaching
myself how to use the program the past couple of days. I thought I’d run this
by you all to see if my plan sounds like the way to go. A little background
first:

I need to create a database for use by people within my company, some of
whom have the following issues:
1. limited (or currently completely nonexistent) Access skills
2. occasionally limited network access (due to tele-commuting and frequent
business traveling)
3. limited patience with dealing with a new system
4. all of the above

For the most part, the target users don’t care much about using technology
or anything—this info is currently submitted to the home office via fax with
the info handwritten on the originals! Add to that the fact that there is not
a standard form—all the orders look different—we end up with lots of clerical
errors on our end. If you are thinking “That is nuts!†you are right. But
these issues cannot be changed anytime soon, so this project needs to take
all of the issues into consideration.

Ideally, I wish I could do this in Excel because the target users are more
comfortable with it, but I don’t think Excel will do what we need, e.g.
automatically filling in customer address/contact info based only on the
customer #.

The Basic Goal
Users need to be able to easily input new order data and easily print hard
copies of individual orders. Each customer has an unique customer #. The
customer # has address/contact info associated with it. Each customer will
have many orders (and therefore many unique order #s).

I need to make info like the customer address fill in automatically once the
user inputs the customer #. The user will then fill in unique order data
(e.g. product, quantity, price) and print out an order form that shows all
that data. Most business is from repeat customers; very occasionally there is
a new customer, in which case the contact info data will need to be entered
by the user. That is it—we don’t have a need for the many powerful bells &
whistles that Access offers.

The Plan
My plan is to create as simple an Access database as possible with a VERY
simple form for the users to input new data (i.e. customer orders). We do not
have a need for maintaining a complete database with all order data
accessible to everyone, so I would like to create something that each person
can have on his/her laptop, independent of the network, without being so
large a file as to create crashing problems. They’ll each have the same
customer data tables and database design, but the order data tables that are
built over time (each time they add an order) will be unique to each user. My
hope is that the simple data entry form will be user-friendly enough that
they will truly use it, and the menu will have a “print†button that is
idiot-proof.

Does this general plan sound good, given the limitations I have? Am I
missing any key considerations? I believe I have figured out how I need to
set up table relationships and queries and forms, so I should be good to go
on those—I just would like to have a couple veterans’ input on whether this
plan seems sound.

 

[Guest]

Open Access. Click on "File" on top bar, and select "New."

A window should open on the right side. There will be a "Templates" area,
from which you choose "On my computer...."

The new window that opens will have two tabs at the top. Click on the one
labeled "Databases."

Select the icon that is labeled "Order Entry" and then select the "OK" button.

Type in a name that you can remember. This will be the name of your new
database. Then click on the "Create" button.

This is a wizard for creating a database, so a series of windows will walk
you through the details of what you want to have in your database.

Keep clicking the "Next" button to continue through the windows. Some of
the lists fall below the bottom of the window, so be sure to use the slide
bar on the right side to see all of the selections. The final window will
only have a "Finish" button on it.

Once you click on that, your database will be put together. You will then
fill out some simple information about your company. Upon closing that final
window, your database will be ready to use.

The first window you will see is for Order Entry. Try opening and closing
the various windows, and just try typing things in to see how they work. You
can always create another database by creating it the same way. You will be
able to put in customers, employees, and products. There are some simple
reports to see what is happening.

This is a quick and simple way to create a quick and simple database. Hope
it will work for you.

 

[Craig Hornish]

Hi,
Be forwarned my comments are a little sarcastic and hard, but I believe
it is better to be honest about what I think.

Comments are in line and are based upon your whole post - like the first
one...
--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

My question is for you veteran Access users. I've never made an Access
database-I was just given this assignment last week and I've been teaching
myself how to use the program the past couple of days. I thought I'd run
this

Wow, two whole days - my first sarcasm.
Sure, your going to need about 5-6 months (8 hours a day) if you are talking
about having seperate database on each computer and idiot proofing to such a
degree for users that don't like technology. (if they just start hitting
buttons and such) [I am assuming you are totaly new to any type of database
development - if not maybee 3 months]

by you all to see if my plan sounds like the way to go. A little
background
first:

I need to create a database for use by people within my company, some of
whom have the following issues:
1. limited (or currently completely nonexistent) Access skills

Not that big of a problem

2. occasionally limited network access (due to tele-commuting and frequent
business traveling)

This will be a problem if you are putting orders in -and want to keep
things straight -

3. limited patience with dealing with a new system

A problem if they can't spend 1 hour (that really should be all it
takes - with good instructor) learing the application
Give up now if they can't do that - sarcasm but serious

4. all of the above

For the most part, the target users don't care much about using technology
or anything-this info is currently submitted to the home office via fax
with
the info handwritten on the originals! Add to that the fact that there is
not
a standard form-all the orders look different-we end up with lots of
clerical
errors on our end. If you are thinking "That is nuts!" you are right. But
these issues cannot be changed anytime soon, so this project needs to take
all of the issues into consideration.

The big question that will decide the scope of the project will be who
will be putting in the orders.
From this it still sounds like the home office needs the order information
and that will have to be updated.

Ideally, I wish I could do this in Excel because the target users are more
comfortable with it, but I don't think Excel will do what we need, e.g.
automatically filling in customer address/contact info based only on the
customer #.

Because you said that the customers don't change much [Don't know how much
your products change]- You actually could put out a sales order in Exel that
looks nice - if that is all you wanted to do.
But of course you then could not easily to any compilations to the data that
you collect.

The Basic Goal
Users need to be able to easily input new order data and easily print hard
copies of individual orders. Each customer has an unique customer #. The
customer # has address/contact info associated with it. Each customer will
have many orders (and therefore many unique order #s).

I need to make info like the customer address fill in automatically once
the
user inputs the customer #. The user will then fill in unique order data
(e.g. product, quantity, price) and print out an order form that shows all
that data. Most business is from repeat customers; very occasionally there
is
a new customer, in which case the contact info data will need to be
entered
by the user. That is it-we don't have a need for the many powerful bells &
whistles that Access offers.

You have not said anything about a products list - wouldn't it be nice to
have that automatically fill in also - with price.

The Plan
My plan is to create as simple an Access database as possible with a VERY
simple form for the users to input new data (i.e. customer orders). We do
not
have a need for maintaining a complete database with all order data
accessible to everyone, so I would like to create something that each
person
can have on his/her laptop, independent of the network, without being so
large a file as to create crashing problems.

Unless you have a serious amount (thousands if not millions) of orders
each day your database it not going to get very big - and if you are, I want
to know what you are paying for fax machine paper. Space is cheap - and
though it really doesn't matter - you are already talking about making
copies of the Customer data - that takes extra space each time you give each
person their own data.

Crashing depends on alot of things - network, number of users on at the
same time etc.

They'll each have the same

customer data tables and database design, but the order data tables that
are
built over time (each time they add an order) will be unique to each user.

So what happens when "Ajax" changes telephone numbers, address - the new
client etc...

My

hope is that the simple data entry form will be user-friendly enough that
they will truly use it, and the menu will have a "print" button that is
idiot-proof.

Does this general plan sound good, given the limitations I have?

Data entry, printing - yes good
Individual tables for customers - no unless there is some type of
syncronization - Definatly no - if they are in the same office and can
connect to 1 backend database

Am I

missing any key considerations?

Syncronization, other reporting or compilation, Products table.

I believe I have figured out how I need to

set up table relationships and queries and forms, so I should be good to
go
on those-I just would like to have a couple veterans' input on whether
this
plan seems sound.

Unfortunatly I can only guess at the needs of what the home office needs
for any of this data - but you mentioned that they get faxed information -
to me that suggests some type of compilation of this data. To do that
efectivly there should be some sort of consistant data between all parties.

This will be a major factor in deciding how this database should be
developed.

 

[Jeff Boyce]

Easy ... is HARD!

Making an order entry database that has multiple copies and can be taken on
the road and used in disconnected fashion, then re-synchronized with a
"home" copy, all the while being easy to use and fairly user-proof is not an
insignificant undertaking.

I hope you have been given a decent amount of time to accomplish this...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the note. I have been assured that the users "will never want to
consolidate" (famous last words, I bet...). I'm just going to try and do the
task I've been handed.

 

[Guest]

I do appreciate your comments (and sarcasm ). It's not an ideal
situation--I have a small fraction of the time you suggest, so we have to go
with the quick and dirty and live with the imperfections.

Zambi

Hi,
Be forwarned my comments are a little sarcastic and hard, but I believe
it is better to be honest about what I think.

Comments are in line and are based upon your whole post - like the first
one...
--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

My question is for you veteran Access users. I've never made an Access
database-I was just given this assignment last week and I've been teaching
myself how to use the program the past couple of days. I thought I'd run
this

Wow, two whole days - my first sarcasm.
Sure, your going to need about 5-6 months (8 hours a day) if you are talking
about having seperate database on each computer and idiot proofing to such a
degree for users that don't like technology. (if they just start hitting
buttons and such) [I am assuming you are totaly new to any type of database
development - if not maybee 3 months]

by you all to see if my plan sounds like the way to go. A little
background
first:

I need to create a database for use by people within my company, some of
whom have the following issues:
1. limited (or currently completely nonexistent) Access skills

Not that big of a problem

2. occasionally limited network access (due to tele-commuting and frequent
business traveling)

This will be a problem if you are putting orders in -and want to keep
things straight -

3. limited patience with dealing with a new system

A problem if they can't spend 1 hour (that really should be all it
takes - with good instructor) learing the application
Give up now if they can't do that - sarcasm but serious

4. all of the above

For the most part, the target users don't care much about using technology
or anything-this info is currently submitted to the home office via fax
with
the info handwritten on the originals! Add to that the fact that there is
not
a standard form-all the orders look different-we end up with lots of
clerical
errors on our end. If you are thinking "That is nuts!" you are right. But
these issues cannot be changed anytime soon, so this project needs to take
all of the issues into consideration.

The big question that will decide the scope of the project will be who
will be putting in the orders.
From this it still sounds like the home office needs the order information
and that will have to be updated.

Ideally, I wish I could do this in Excel because the target users are more
comfortable with it, but I don't think Excel will do what we need, e.g.
automatically filling in customer address/contact info based only on the
customer #.

Because you said that the customers don't change much [Don't know how much
your products change]- You actually could put out a sales order in Exel that
looks nice - if that is all you wanted to do.
But of course you then could not easily to any compilations to the data that
you collect.

The Basic Goal
Users need to be able to easily input new order data and easily print hard
copies of individual orders. Each customer has an unique customer #. The
customer # has address/contact info associated with it. Each customer will
have many orders (and therefore many unique order #s).

I need to make info like the customer address fill in automatically once
the
user inputs the customer #. The user will then fill in unique order data
(e.g. product, quantity, price) and print out an order form that shows all
that data. Most business is from repeat customers; very occasionally there
is
a new customer, in which case the contact info data will need to be
entered
by the user. That is it-we don't have a need for the many powerful bells &
whistles that Access offers.

You have not said anything about a products list - wouldn't it be nice to
have that automatically fill in also - with price.

The Plan
My plan is to create as simple an Access database as possible with a VERY
simple form for the users to input new data (i.e. customer orders). We do
not
have a need for maintaining a complete database with all order data
accessible to everyone, so I would like to create something that each
person
can have on his/her laptop, independent of the network, without being so
large a file as to create crashing problems.

Unless you have a serious amount (thousands if not millions) of orders
each day your database it not going to get very big - and if you are, I want
to know what you are paying for fax machine paper. Space is cheap - and
though it really doesn't matter - you are already talking about making
copies of the Customer data - that takes extra space each time you give each
person their own data.

Crashing depends on alot of things - network, number of users on at the
same time etc.

They'll each have the same

customer data tables and database design, but the order data tables that
are
built over time (each time they add an order) will be unique to each user.

So what happens when "Ajax" changes telephone numbers, address - the new
client etc...

My

hope is that the simple data entry form will be user-friendly enough that
they will truly use it, and the menu will have a "print" button that is
idiot-proof.

Does this general plan sound good, given the limitations I have?

Data entry, printing - yes good
Individual tables for customers - no unless there is some type of
syncronization - Definatly no - if they are in the same office and can
connect to 1 backend database

Am I

missing any key considerations?

Syncronization, other reporting or compilation, Products table.

I believe I have figured out how I need to

set up table relationships and queries and forms, so I should be good to
go
on those-I just would like to have a couple veterans' input on whether
this
plan seems sound.

Unfortunatly I can only guess at the needs of what the home office needs
for any of this data - but you mentioned that they get faxed information -
to me that suggests some type of compilation of this data. To do that
efectivly there should be some sort of consistant data between all parties.

This will be a major factor in deciding how this database should be
developed.

 

[Guest]

I'm seeing that this is going to be a bang-my-head-against-the-wall project.
I have very little time being allowed to get this done. The good news is that
they don't want it to link up into 1 database... a bunch of different dbs are
fine with the powers that be.

Zambi

 

[Jeff Boyce]

That seems inconsistent with your earlier explanation of being able to
replicate data to all the different copies.

Feel free to offer your "powers that be" my standard (consultant's) line:

You can have it good, you can have it fast, you can have it cheap ... pick
2!

Regards (and best of luck!)

Jeff Boyce
Microsoft Office/Access MVP

 

[jacksonmacd]

Your description of the problem and your envisioned solution runs
counter to the way that the regular "veteran Access users" in this
forum normally approach a database design. The power of Access is
truly realized when you make a single, comprehensive solution. You
have asked for a patchwork solution.

Sounds to me like you would be better off with Excel. Nothing you
described is difficult to do in Excel (for as far as you've described
it!!!).

Of course, if you implement a patchwork system in Excel, and your
managers decide that they really *do* want a comprehensive solution,
then you may end up back at square one! Your choice...

Good luck.

 

[BruceM]

I will start off by acknowledging that I cannot be considered a veteran
Access developer, but I think I have learned enough to offer some thoughts.
Maybe a place to start would be a database based on a template, as
suggested, and a single copy for now. Since orders are being entered from
faxed copies anyhow, sounds like entering the data into a single copy of the
database could be a reasonable transitional step. One of the really
challenging aspects of what you seem to be contemplating is synchronizing
multiple copies.
You may want to see if there is an off-the-shelf solution. There is a lot
of business management software out there.
The biggest headache you may have is getting stubborn users to make the
switch to the twenty-first century unless there is strong support from the
top. Considering that management seems willing to accept multiple
independent copies it sounds as if the understanding and support needed to
make this sort of switch may be lacking. Certainly anybody who expects a
quick database by somebody with little if any experience does not have a
good handle on the potential complexity of what they are asking.
By the way, I believe DLookup in Excel may be able to provide some of what
you seek in terms of entering information automatically, but you would need
to check an Excel group about that. Access is a good program for your
needs, but has a fairly steep learning curve. The good news is that you
will one day discover that you can do a whole lot with the program.

 

[Guest]

The dbs don't need to be linked, but they will start with some similar data
(product details, customer contact info). From there on out, there will be no
overlap.

I love your standard consultants line--I wish I could use it (and keep my
job! )

Thanks,

Zambi

 

[Guest]

I should clarify--if I had the freedom to do this the best way, I would take
the time to do so. I don't have that freedom, hence the patchwork solution. I
haven't used Access before now, but I've used a number of unique databases in
the past. I've never had to do it in a slap-it-together manner until now

I'll explore Excel--I was having difficulty getting fields such as contact
info to fill in with only an input of customer number. I'll look into that
some more.

Thanks,

Zambi

 

[NEC-2008]

Zambi. As a new access user, like me, I can assure you that you will not run
on your first outing. You won't even walk fast. Crawl...slowly... at best.
Best advice? Make a plan. Put to paper what you want this thing to look
like, what information you want in it, and what you want the database to do.
Once you do that, you'll have a roadmap to follow. Follow your plan and
enjoy the ride. I assure you it will be a fun trip.
PS....Work at your pace...not theirs.

 

[Evi]

Why not just use the NorthWind database? It's a customer orders database I
wouldn't normally recommend doing this because it is not easy to adapt and
has its limitations but it looks clever and might fool your Excel
Stick-in-the-muds into thinking that you have a 'Real Program' and impress
them enough so that you won't be spending the rest of your working life
trying to make an Access Database look exactly like a spreadsheet.
You will also save yourself the dental work after hours and hours of work,
of having to hear ad nauseam how 'we never had this problem with Excel' or
'haven't you finished yet? Doris in Accounts knocked up a spreadsheet it 3
days'.
You are correct that Access would be better for the job than Excel but your
firm is unlikely to make full use of it and will treat each new automation
and improvement with suspicion.
Evi