Kunudrum in Logic

[jofarrell]

Sorry if this confuses anyone,

I am doing time sheets where data is taken in through ASP and stored in an Access database. There is then a VB app which takes the data from access manipulates it and puts it in a templated Excel Spreadsheet.


My problem is in exporting to Excel I have a list of people horizontally and the projects down the left side vertically, both in alphanumeric order (yeah right!)

Is the best way to do this to print both the stationary vertical and horizontal columns and then figure out the totals in between from a variable assinged to the position ... or as I am filling in the people total as I go and for first person fill in projects column. (Not ever person works on same projects and some may not work on any projects but have admin time and they are entered in no particular order in the database (datewise but it doesnt really help)

Any thoughts would be greatly appreciated as I am having a hard time getting my head around the complete picture and in adding new parts I break an existing one (basically I get names across top and then cant get the projects to not show up duplicates or totals come out wrong, flipside I get projects down the side and the people come out in duplicates)

Joanne

 

[stoolpigeon]

I would try to break this down into smaller parts.

First develope the structure of the spreadsheet. Just query to find the projects you need and add them, as well as the admin part.

Then you could query by person, filling in their hours as you add each person.

I always try to break my problem into smaller, simpler parts.

 

[MichaelRed]

Being a BIG (Anti) Fan of Excel (and spreadsheets in general), my first suggestion to simplify te issue is to TRASH the EXCEL part. Having seperated the "Chaff" from the "Wheat", procede to "Grind" it out any way you want.

With a "NICE" table structure, create an aggregate query to Sum the columns, grouping by some useful field, givinng this the alias name of SumOfXXX, and aliasing the column names back to their original names.

Do a Union query of the table and the aggregate query.

Do a crosstab of the Union Query.

Should have both columns and rows with "proper' names as well as totals for Rows and Columns.

Next, I'll bet this is not well understood.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jofarrell]

Michael,

Well the "NICE" Excel sheet is the whole purpose for both the ASP and VB programs I am writing sooooooo I can't really scrap it and no, it was understood what you are trying to convey but my hands are tied when it comes to what I need to output so I have to work within my limits to give them what they want .. especially when THEY are head office.


Joanne

 

[MichaelRed]

Well, if you understand what I'm 'proposing', just do 'it' and export the results to Excel for the "Great Them", then (WELL after they do the Mikee trick{He LIKEs IT!!}) ACCIDENTLY show them that it really is all in a db format ...

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over