Help with Basic Starting Point

[CB_DFW]

I have been tasked with creating a report to basically match what I am showing in the Excel Spreadsheet image below. This is a spreadsheet built by someone on another team and I have to create a process to update the information using Access.

The spreadsheet below is using all vlookups to populate the fields. The data is provided by our group and the user that created this spreadsheet from our data to create this.

The spreadsheet brings together several reports that we produce. The usergroup just created this to get a dashboard/overall prospective by putting it all together like a snap shot.

The data only changes each month.

The user of the end result will still want it in an excel format but I plan to provide them the data without having formulas and calculations in the spreadsheet itself.

I am pretty familiar with getting the data in table but calculating the column and row totals is beyond my current knowledge base.

Can you guys/gals give me some things to think about or ideas of how to get started in the right direction on this?

Below is only a piece of the spreadsheet but the other parts are very similiar. Note: I have done several Access projects where I use the macro function, TransferSpreadsheet and export data to the Excel files to the exact place I want it.

Another difficulty for me is that the names along the left side and MXXXX numbers change month to month.

http://www.foleytx.com/images/misc/excel1.gif

Thanks so much for any help you can provide.

 

[wiklendt]

well, as a very basic thing, you need to think about what data you are collecting and what data types they will be (e.g., a phone number is NOT going to be data type "number", it's going to be "text" - a simple way to remember whether to use "number" for a data type is to think whether you can do mathematics with that number - my phone number x boss' phone number != my supervisors phone number).

then, you need to think about what tables you need, remembering to have all your data normalised. let us know if you don't know what this means, and we can point you in the right direction.

the tables could be tricky - are you going to be IMPORTING data form excel perpetually (this might mean thinking about temporary tables and extra code and extra work), or will you all switch to the new access DB when it's ready?

then you want to think about how to draw out some of the data using queries - this is where the data starts looking more like a spreadsheet, in some cases. you'll need to know how to make the queries so that you can feed forms for data entry (tables are for data STORAGE only).

then finally you exporting code and/or reports (which will also, generally, be based off queries), if you want. if you are only presenting the data, and don't need the end-user to fiddle with it, you could even try skipping the export and just present in a report which, say, you have converted to PDF if you need it distributed electronically OR printed, if you need to post/handout the summaries that way.

just my 2c.