Do I need Excel or Access for my database needs?

[Emily]

I am unsure if I need Excel or Access for the database I've described here. I
own Excel and am more familiar with it, but my office will purchase Access if
that is what would be best for this database I need to build. Please advise!
I'm concerned about how to do formulas/calculations in Access. Please read
further for our database objectives:

OBJECTIVE OF DATABASE:
• To maintain detailed, current pricing sheets for multiple Dealers (20+)
o Each Dealer it has its own pricing for multiple vendors (15+), vendor
products, dealer commissions, sales commissions, owner commissions, etc.
o Each Dealers’ pricing sheets will change from time to time but the price
changes should not change the previous records that have used the “oldâ€
pricing

• To use information from invoices/contracts to provide a detailed billing
summary showing us exactly who to pay and what amount, using the Dealer
specific pricing

•To provide a record of contracts & customer contacts

•To provide monthly, yearly, quarterly billing reports

•To maintain record if customer has been mailed “the formâ€

•Form must auto populate using contract & customer info

•Must record and adjust billing, for all sources, if a contract is cancelled


Wordy Example: I enter one invoice & check amount from Dealer Auto – it
contains 30 customer contracts with the company AUF, sold by the sales
person, R.Nelson. Some of the contracts are for 1 product, some 2 or more
products. Our company ABC Auto Services processes all contracts and cuts
checks for the multiple parties of each invoice. We desire a detailed billing
summary showing us exactly who to pay and what amount.
When I enter an invoice with its contracts I would like the database to
automatically find the price sheets specific for Dealer Auto, Dealer Auto/AUF
pricing, Dealer Auto/R.Nelson, Dealer Auto/ABC Auto Services - and find the
correct billing amount for each entity and multiply that with the correct
number from the contract (billing is different for 1 product vs. 2+ products)
– to result in a billing break down. The billing breakdown should answer the
following questions: Of that 1 invoice with 12 contracts, what does Dealer
Auto get paid? Dealer Auto Staff? ABC Auto Services Sales Guy? AUF? ABC Auto
Services? This is the report I am seeking.
As well as the individual invoice billing breakdown, I also need to print
out other billing/earning reports. How many contracts sold in Jan.08 for the
vendor, AUF? How much commission did R.Nelson earn in Jan 08? How much in
2007?
The database also needs to store all individual customer contract
information & the status of their contract, expiration dates etc.

That's about it. Please advise if I should be looking to Excel or Access to
get this created in the best way! Thank you so very very much for your time.

- Emily

 

[FSt1]

hi
after reading your requirements, i am of the opinion that excel is beyond
the scope of your requirements and access might be a better alternative.
if your purchase access, you will probable end up with 2007.
here are 2 sites that might aid you.
product overview....
http://office.microsoft.com/en-us/access/HA101656301033.aspx
getting started....
http://office.microsoft.com/en-us/access/HA100646161033.aspx
having designed 5 access databases, i can say this....
get your tables streight first! you don't have to have data in them now but
know what your are going to put in them. thinking of things to put in the
tables AFTER you have your queries, forms and reports can cause COSTLY delays
and re-dos.
if you get stuck....post in access. been there done that. real helpful.
more helpful sites....
http://www.lebans.com/
http://www.mvps.org/access

regards
FSt1

 

[Conan Kelly]

Emily,

In my opinion, Access is the tool for any database, but Access is a little
more complicated to design. More people are familiar with XL and it can be
used in a DB like manner. That's why many people create DB's in XL.

I am no expert, but from your description, Access is the way to go. What
you are describing MIGHT be possible in XL, but it would take a lot of work
and probably run very slowly.

In Access there are wizards that will help you through the process of
creating reports, forms, queries, etc...

Functions in Access are pretty easy once you learn them and many of the
functions are the same in XL and AC.


Also, just like for XL, there are many newsgroups and other websites out
there to help people with learning AC.

I would definitely suggest AC. I would also suggest taking a course in
AC(or at least getting a text book and going through it yourself).

Keep in mind, if doing this yourself, this isn't going to be an overnight
thing where the DB is up and running tomorrow, in a week, or even a month.
Doing this yourself is going to take some time to learn and develop. You
might be able to get something basic up and running in a couple of weeks/1
month if all of your time is devoted to developing this DB and learning
Access. Then you could add to it bit-by-bit as you learn, refining it,
making it look better, work better, work faster, adding more reports, etc...

So, it all depends on your companies time requirements and budget. If this
is something your company needs right away and money is no object, then you
would be better off hiring a developer to do this for you. If they are okay
with it taking months...even a year or 2 (w/o full time devotion) to
complete and they want to save money, then go ahead and take on this
project. But you also gotta wonder how much money this would save them if
they had it right now. The money factor might cancel each other out no
matter which way you go. It could even cost more, over time, if you decide
to do this on your own. It is hard to say.

HTH,

Conan

 

[Emily]

Thank you BOTH!!

Thank you so much for taking the time to respond to my post. I've decided
that Access is really the correct software to use, since I've been really
struggling with various arrrays and complex formulas to make my data work in
Excel. There are just so many LAYERS to my data....access is desgined to
handle that.

I did desgin a Database to track Falls for a nursing home about 6 years ago
but haven't looked at Access since. I am pretty sure I could figure it out
again. I am most worried about how to get it to do the calculations and
getting the basic design done properly the first time! Thanks so much for
your input. I really appreciate it.

Thanks for the links as well. I'm going to have a lot of learning to do!

- Emily