Can a report group/total data based on (variable)different columns?

[kopja]

Hello there.

I have this access database which has a table for
projects, in which there is a staff-name, project-id, project-desc etc
and a table for
project-breakdown, which has
project-id (related 1-1 with project-id on table projects)
and a dt1 column, hrs1-column, dt2-column, hrs2 columnd and so forth
up to dt10, hours10,
The idea is each project can be broken down into daily hours of work,
but the days are different for each project. The users enter them via
an userform

Thus the table project-breakdown looks smth like this (it has more
columns)

PID dt1 hrs1 dt2 hrs2 dt3 hrs3
10 12/26/2006 1 12/27/2006 2 12/28/2006 3
17 12/26/2006 1 12/27/2006 2 12/28/2006 3
21 12/26/2006 1 12/27/2006 2 12/28/2006 3
23 12/26/2006 1 12/26/2006 1 12/26/2006 1
26 12/26/2006 1 12/26/2006 2 12/26/2006 3
36 12/26/2006 1 12/27/2006 2 12/28/2006 3

Now, I am trying to create a report that will show hours only for the
dates I select.

So I created a query that goes
SELECT *
FROM tblProjects INNER JOIN tblBreakdown ON
tblProjects.ProjectID=Breakdown.ProjectID
WHERE tkInArray([staff_name]) And (dtWithinInterval([dt1]) Or
dtWithinInterval([dt2]) Or dtWithinInterval([dt3]) Or dtWithinInterval
([dt4]) Or dtWithinInterval([dt5]));

tkInArray and dtWithinInterval are 2 simple functions I created in
VB. The interval and staff_names are entered via an userform.

And I used the wizard to make a report on top of this query by
grouping the staff, and creating a summary total under each column.

It works fine, except that this gives me totals for each dt-column
(on table Breakdown), and that is not what I want.
Is there a way to create a report that will take data based on value
(and look at all columns) and group/total them under a separate
column?
Example, for the table Above, on the report I would like to display
ONLY a column for each unique date(with the total) and NOT a column
for each Column on table Breakdown.

I use Access 03.
Thanks for your help.

 

[dhookom]

Normalize, normalize, normalize,... Table project-breakdown should have one record per date per project. If you don't understand normalization, do a search of google or find Signatures is LesPaul's or PHV's (?) replies in these news groups.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kopja]

dhookom: Sorry, I just typed in the sample table and it shows the wrong data.
Yes, table project-breakdown only has
1 line(record) per each project AND
dates do no repeat within that record. This is enforced.

My original questions remains, can I still create a report that will sum up by specific dates (which will be on different columns on different records)?

Thanks,
kopja

 

[dhookom]

If you have fields with names like dt1,dt2,... hrs1,hrs2,... then your table structure is not normalized. You can either change your table structure or use a union query.

SELECT PID, dt1 as TheDate, hrs1 as Hrs
FROM [project-breakdown]
WHERE dt1 is not Null
UNION ALL
SELECT PID, dt2, hrs2
FROM [project-breakdown]
WHERE dt2 is not Null
UNION ALL
SELECT PID, dt3, hrs3
FROM [project-breakdown]
WHERE dt3 is not Null
UNION ALL
-- etc --
SELECT PID, dt10, hrs10
FROM [project-breakdown]
WHERE dt10 is not Null;

Combining the union query with your project table should allow you to easily search and total in your query/report.




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]