XL97: Advanced Databasing Methodology - ideas? 1

[VBAjedi]

Hi all!

I'm in the conceptual phases of a large project, and I would like input on the methodology I'm considering.

The Objective: provide a tool (in Excel) that allows management to compare sales data, forecast data, and budget data. They need to be able to specify what data to compare by region, by city, by salesman, or any combination of these. The results that are returned need to be broken out into three different product categories.

I have sales data, forecast data, and budget data in similar (though not exactly alike) layouts. All three sources contain Region, City, and Salesperson identifiers on each row, followed by numbers for each of 12 periods. Actual data values are identical across all three sources in terms of syntax and formatting.

My Approach: I plan to use a "summary sheet" with a criteria section at the top. This section will contain drop-down lists that I create from all of the unique data values. When a user specifies criteria, these will be used to simultaneously populate criteria fields on my three data sheets (at this step I can adjust for columns that do not exist on one of the data sheets, are in different orders, etc). Then all three sheets will use these criteria to run an advanced filter on that sheets data.

Now I need to break the filtered results out into the three categories. I plan to use DSUM to do this, but it operates on ALL rows, not just filtered rows. So I am going to "bounce" the filtered results from all three data sheets to three hidden "bounce sheets". These three sheets will contain summary sections at the top that, coupled with three criteria ranges (one for each product category), will use DSUM to return the subtotals for the three categories.

Finally, I will return the values from these three summary sections to my original "summary sheet", where the necessary comparative calculations will be performed to show variances between actual sales, forecasted sales, and budgeted sales for the criteria the user specified.

Obviously this is a large undertaking, so I want the methodology to be as efficient as possible. I have successfully implemented all of the steps in this plan at a test level, but would like input before I proceed.

Any thoughts?
LOL

VBAjedi

 

[xlbo]

Sounds like a pretty well thought out plan - nothing I could really criticise. I'm inferring that the data is to be kept in the workbook itself ??? This is the only area I might change. I do quite a lot of similar stuff myself and have been using variables in SQL statements to return only the data that is needed (this would cut out the messing around with filters) - basically, you would have an SQL statement that brings down the same info each time but you just use the selections on the summary sheet to change the WHERE parameters....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[VBAjedi]

Geoff,

I'm just now learning how to interface with dbases using SQL. While that approach intrigues me, I don't know if I could pull it off. Besides, management keeps their budget numbers confidential and wants to be able to paste them in themselves. Would that make the SQL approach difficult?

Thanks!

VBAjedi

 

[xlbo]

Top tip - record using MSQuery to generate the SQL - then mess around so that you are creating a new querytable each time - that way you don't get the problems with associating dqy files and you do get the flexibility to insert variables into the SQL string

I don't think it would be too much of an issue if you didn't have the budget data there - just bring back the actuals and use keys to identify which bit of budget they go against. I'm also just learning this kinda way of getting data and it can be a bit fiddly so I'd say you're probably better off sticking with what you are currently doing. Just wanted to give another option....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[VBAjedi]

Good advice on using MSQuery - star for that!

Thanks again for your input.

VBAjedi