Is Access what I need for this database?

[Emily]

I am unsure if I need Excel or Access for this database 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

 

[Golfinray]

If you are familier with Excel you might want to stick with doing it in
Excel. Access will do what you want and do it very well but if you don't know
it the learning curve it pretty steep unless you had time to take some
classes.

 

[Jeff Boyce]

Emily

I'll echo Golfinray's comments...

Access is not a spreadsheet on steroids. To make good use of the
relationally-oriented features and functions Access offers, you'll need to
get up over THREE learning curves...

First, you'll need to learn "normalization" and relational database design
Next, you'll need to un-learn Excel/spreadsheet design
Finally, you'll need to learn to use Access as a development tool. You
really REALLY don't want your users muckin' about in the raw Access toolset.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Linson]

Finally, you'll need to learn to use Access as a
development tool. You really REALLY don't
want your users muckin' about in the raw
Access toolset.

As critical as this information appears to be to the business, I wouldn't
want the users muckin' about in the raw Excel toolset either. And, in my
experience, you can't create as solid, stable, and secure an application
with Excel as you can with Access, even if you are an experienced Office
developer -- the tools and power are just not there as they are in Access.

My suggestion would be for the Original Poster's company to locate and
contract with a competent, experienced Access developer to work with them to
gather and validate requirements, and to design and create the initial
application, with their own personnel working alongside and learning as they
go. Then, they would have knowledgeable on-site staff for maintenance,
error correction, and enhancements building on a solid, well-designed base.

Larry Linson
Microsoft Office Access MVP

 

[rumkus]

Surely can be done by both excel and access.So I'd suggest
-Let them buy ms access
-Start your project in excel
-Start learning access in your spare time
-Move your project to access even if you find it difficult at the
beginning.

rgds

 

[Emily]

Everyone,
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 Fall Database 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.

- Emily

 

[Larry Linson]

I did desgin a Fall Database 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.

Feel free to drop back in and ask questions when you hit stumblingblocks.
There's good advice on effective use of newsgroups (not specifically this
one) at http://www.mvps.org/access/netiquette.htm. My observation is that
the best answers are usually in response to questions that are both precise
(giving necessary detail) and concise.

Best of luck with your project, and we're looking forward to being able to
assist when you need a "hand up".

Larry Linson
Microsoft Office Access MVP