Greetings.
Trying to group together some information that comes in from another system, and I have NO CONTROL over the structure. It's sort of a take-it or leave it (the old, "we're doing you a favor…"
.
Here's the structure I get: Project then Task then Hours. There are no null values, either a project has hours for a task or it doesn't.
I need to group, for storage on my database, similar task hours by project. For example, Project ABCDE has research, analysis, requirements building & time estimation as tasks that I need to group and store as Front-End. Then project ABCDE has environment building, detailed design, coding, test-case building and unit-testing that I group and store as Coding. And there are others in a similar vein.
I can’t seem to get a crosstab query to work because the tasks are different; and even playing with it some, I still don't know how to turn the crosstab into a make-table that way and find no help on that.
My (awful) solution to date is to create a temporary table for each task and then add-together in another make-table query. That worked (sorta) when there were only 12 tasks listed, but with the addition of some other business, the tasks list is being expanded to 25. Maintenance will be a nightmare with this solution!
So I'm wondering if any of y'all might have some thoughts on this. Remember that I can't request a different structure for the project/task/hours I'm getting as input, but I can do whatever I need to with the data once I receive it.
Thanks for any thoughts!
LJ
Trying to group together some information that comes in from another system, and I have NO CONTROL over the structure. It's sort of a take-it or leave it (the old, "we're doing you a favor…"
.Here's the structure I get: Project then Task then Hours. There are no null values, either a project has hours for a task or it doesn't.
I need to group, for storage on my database, similar task hours by project. For example, Project ABCDE has research, analysis, requirements building & time estimation as tasks that I need to group and store as Front-End. Then project ABCDE has environment building, detailed design, coding, test-case building and unit-testing that I group and store as Coding. And there are others in a similar vein.
I can’t seem to get a crosstab query to work because the tasks are different; and even playing with it some, I still don't know how to turn the crosstab into a make-table that way and find no help on that.
My (awful) solution to date is to create a temporary table for each task and then add-together in another make-table query. That worked (sorta) when there were only 12 tasks listed, but with the addition of some other business, the tasks list is being expanded to 25. Maintenance will be a nightmare with this solution!
So I'm wondering if any of y'all might have some thoughts on this. Remember that I can't request a different structure for the project/task/hours I'm getting as input, but I can do whatever I need to with the data once I receive it.
Thanks for any thoughts!
LJ
