Summing totals in a Query

[Guest]

I have a query thats returns specific entries in a table with a few project
and Hour fields. A user is able to choose from a list of projects for each
field. I want to count the amount of hours for each project. From a form,
the user is asked to choose a period for which he want the query to run. A
result a typical query is displayed below.

Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the period
is a chosen date from a form. The user on the form chooses the year and month
and all the entries in that month is displayed in the query result
--------------------------------------------------------------------------------
Projects TotalHours (This is basically what I want in the end)

ProjectA 7
ProjectB 2
--------------------------------------------------------------------------------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours assigned.
The query works fine for generating a complete report, but now I want to sum
the totals of hours worked on each project for the duration spesified from
the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Wolfgang Kais]

Rudi" wrote:
[...]

The query works fine for generating a complete report, but now I want to
sum the totals [... ]

Hello Rudi.
Create a report that is based on your query. Using the report wizard:
Group the data by Name (add a group level for the name column)
and let the report calcuate the sums (see the summaries-button).

And: Don't use "order by" in a query for a report, the report does that
himself.

 

[Guest]

Wolfgang,

Thanks, I think this might work. There is just one aspect I need you to
understand. There is 'n multitude of projects (say about 10). There is 6
project slots and all 6 slots can have any of the 10 projects selected. For
every project slot, there is an Hour slot as well. So now I can choose a
certain project in project slot 1 and choose the amount of hours for that
project in the corresponding Hour slot. In the second slot, I choose another
project and also the amount of hours in the corresponding Hour slot. I have
created a query that displays all the project slots and corresponding hours
worked (and a few other things). If I create a report like you proposed, it
will only sum the totals for each slot. I want to sum the totals (over all
the slots) for all the hours worked for a certain project. Somedays I might
choose a certain project in slot 1 and other days in slot 2. At the end of
the month, I must coint the hours worked for a spesific project, even it was
chosen in different slots.

Rudi" wrote:
[...]

The query works fine for generating a complete report, but now I want to
sum the totals [... ]

Hello Rudi.
Create a report that is based on your query. Using the report wizard:
Group the data by Name (add a group level for the name column)
and let the report calcuate the sums (see the summaries-button).

And: Don't use "order by" in a query for a report, the report does that
himself.

 

[Wolfgang Kais]

Hello Rudi.

I see.quit unusual.
Then, you will have to use a union query that makes the result look like
your table should look like:

SELECT Name, Procets1 AS Projects, Hours1 as Hours FROM yourQuery
UNION ALL
SELECT Name, Procets2, Hours2 FROM yourQuery
UNION ALL
SELECT Name, Procets3, Hours3 FROM yourQuery

Summing will be done with
SELECT Name, Projects, Sum(Hours) AS [Sum of Hours]
FROM queryAbove
GROUP BY Name, Projects

Or even with a crosstab query:
TRANSFORM Sum(Hours) AS [Sum of Hours]
SELECT Name FROM queryAbove GROUP BY Name
PIVOT Projects;

--
Regards,
Wolfgang

Wolfgang,

Thanks, I think this might work. There is just one aspect I need you to
understand. There is 'n multitude of projects (say about 10). There is 6
project slots and all 6 slots can have any of the 10 projects selected.
For
every project slot, there is an Hour slot as well. So now I can choose a
certain project in project slot 1 and choose the amount of hours for that
project in the corresponding Hour slot. In the second slot, I choose
another project and also the amount of hours in the corresponding Hour
slot. I have created a query that displays all the project slots and
corresponding hours worked (and a few other things). If I create a
report like you proposed, it will only sum the totals for each slot.
I want to sum the totals (over all the slots) for all the hours worked
for a certain project. Somedays I might choose a certain project in
slot 1and other days in slot 2. At the end of the month, I must coint
the hours worked for a spesific project, even it was chosen in different
slots.

Hello Rudi.
Create a report that is based on your query. Using the report wizard:
Group the data by Name (add a group level for the name column)
and let the report calcuate the sums (see the summaries-button).

And: Don't use "order by" in a query for a report, the report does that
himself.

Rudi" wrote:
[...]

The query works fine for generating a complete report, but now I want to
sum the totals [... ]