Is Access right for me? Sales Projection history

[cbelle3]

One of my main responsibilities is to create a Worldwide Sales Projection reports (We have 13 main offices) on the 15th and 30th of each month. This is has been done for quite a few years but the data is not organized at all.
I look at how far sold we are on the the year/budget vs. how sold on the year/actuals we were at the same time last year - which involves me going back to the PDF of last year's report.

I've come to terms that I may need to manually enter all the data but I want to have it organized in a way to be able to create a more automated report so that I can just refer to certain report period (ie. Mar 15th and be able to pick Mar 15th 2008 or 2007 and add 2009 when it comes around).

I'm thinking I should it do it by client, but I'm not sure.

Each office has clients (non exclusive) and each client has a monthly SOLD amount and a monthly budget and then monthly actuals come into play.

I combine Jan Actuals+Feb Actuals+Mar-Dec SOLD (project status Sold) and compare it to 2009 Budget and then compare that to SOLD at this time last year vs Actuals last year 2008.

Please help or point me in the right direction.. THANK YOU

 

[Will_B]

Hi. When I plan a database, I try to record the data at the smallest unit (most granular) level. For example, I might record sales by sales person ID number. I'd then create a separate table that would record only sales person ID and their sales territory and department. I'd then create yet another table where I'd record which sales territories comprise each larger sales region. As a result of this planning, I could then report on sales by any of several levels of granularity all by simply linking my sales table to other tables.

For budget info, I'd create a separate Budget table that would consist of Department ID, Fiscal Year, and Budget Amount. I'd put this budget info in a separate table because every year there would be a new budget amount and, for historical reporting, I'd always want to know what past budget amounts were.

As for the various report questions you need to answer (totalling and reporting sales in various ways), that can handled by Access' querying and reporting features. As mentioned before, it is better to store data in at its smallest, lowest level (e.g., daily sales rather monthly or quarterly) because you then have the flexibility to report in different ways. You don't want to store data by month because you will invariably be asked to report on weekly totals or determine average sales volume by day of week, etc.

From the sound of your immediate reporting needs, it sounds as though you need a separate table for sales data and another table for budget and yet another just to hold client info. This breaking-out of data into separate tables is called "normalization" (in case you come across the term in your reading).

The website GeekGirls has tutorials for designing and creating your first database: http://www.geekgirls.com/menu_databases.htm

As for the having to hand-enter PDF data into your database, there might be a much easier way. In my current job, I sometimes have to extract tabluar (table) data from large PDF documents and put them into a database. Depending on the PDF document, I've been able to use different methods all of which are better than manually entering LOTS of data. Let me know when you are ready to look into that.

You might consider meeting with the consumers of your reports and asking what other kinds of questions they might ask. Getting those info needs up front will often save you from having to reorganize the database later.

I hope this helps get you started.

 

[cbelle3]

THANK YOU SO MUCH! much appreciated.

I'm going to spend some time planning it this weekend, I'll let you know how I make out.

Have a good weekend!

Claire

 

[cbelle3]

Hi Will

I just got the full version of Adobe installed by my IT guy.

I can't seem to figure out to export the tabular data correctly - any tips? I've tried most of the format options.

Thanks,
Claire

 

[Will_B]

Hi, Claire. Since extracting from PDFs is off-topic for this Message Board, I'll contact you by private email. You'll get a message from "MrExcel" shortly.

 

[Will_B]

I've just sent my "private message" with the instructions. I'm not certain if my two attempts worked. If you do not get my message within the next hour, post here to let me know.

 

[modterry]

This may be too late but I came across this thread.

Once, you have your data tables set up, Is your firm on line?

If so - why not make it web enabled - have some create an on line application to have the different offices input the data into on line forms. The data is entered once, quit using the PDF's and re entering the data.

If you are familiar - you could use Cold Fusion ( costly ) , PHP, or lastly ASP as your application - may save you alot of man hours and it is up to date instantly

You can then make your reports and save them on line.

Terry