Totals by Person

[netrusher]

I have a table where 4 different people enter their amount of work sheets they complete each day via a form. Each person could have a different total each day. Example:

AJ=5
CH=10
MH=7
CA=3

Each person will have a entry for each work day of the month.

I am creating a query that needs to show each persons total for a month. I know how to get the month but how can I total each persons page count all together and then separately? Example:

All four combined total page count

AJ Page count individually
CH page count individually
MH page count individually
Ca page count individually

Thanks for all help

 

[DotNetGnat]

SELECT Person, Sum(dayTotal)
FROM Mytable
Group BY Person

-DNG

 

[netrusher]

-DNG,

How do I insert that into the query? Sorry, as I am new to this still.

 

[DotNetGnat]

what is your query?

-DNG

 

[netrusher]

My query is person name(OmsWorkerAssigned), page count (OMSDailyPages), and date (CurrentDate). Table is (DailyOmsWorkTbl)

 

[DotNetGnat]

something like this

SELECT OmsWorkerAssigned, Sum(OMSDailyPages)
FROM DailyOmsWorkTbl
WHERE
CurrentDate BETWEEN #08-01-2005# AND #08-31-2005#

-DNG

 

[netrusher]

Is this to be done in SQL?

 

[DotNetGnat]

I guess you are using MS Access...

Create a new query in the design mode...on the top select View from the tool bar and click on SQL...this is the SQL View screen of your query...

Paste the query i suggested in it and run it...

-DNG

 

[netrusher]

I put what you sent into SQL and received the following error:

You tried to execute a query that does not include the specified expression'OmsWorkerAssigned' as part of an aggregate function.

I do not know what that means. Can you explain?

 

[DotNetGnat]

oops...sorry..try this:

SELECT OmsWorkerAssigned, Sum(OMSDailyPages)
FROM DailyOmsWorkTbl
WHERE
CurrentDate BETWEEN #08-01-2005# AND #08-31-2005#
GROUP BY OmsWorkerAssigned

-DNG

 

[netrusher]

Thanks again,

I figured it out. I went to design mode and changed OMS Name to Group by and it worked.

 

[DotNetGnat]

great..glad to be of help...

-DNG

 

[netrusher]

Below is the SQL of my query and it is working just fine.
My question is: How can I compute the total of all of these records. Right now I get each individuals Totals. I would also like to have a Grand Total of each Individuals. Someone told me that compute might work. If so, how would I insert the compute statement into the existing SQL Query to get a Grand total?

SELECT DailyOmsWorkTbl.OmsWorkerAssigned, Sum(DailyOmsWorkTbl.OMSDailyPages) AS SumOfOMSDailyPages
FROM DailyOmsWorkTbl
GROUP BY DailyOmsWorkTbl.OmsWorkerAssigned, Month([CurrentDate])
HAVING (((Month([CurrentDate]))=Month(Now())));

 

[DotNetGnat]

look at this thread...

thread701-949849

-DNG

 

[netrusher]

Everyone:

Below is the soultion to my problem, Thanks.

SELECT Month([CurrentDate]) AS [month], Sum(DailyOmsWorkTbl.OMSDailyPages) AS SumOfOMSDailyPages,
DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abajacks' AND month([currentdate]) =month(now)") AS abajacks,
DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abchill' AND month([currentdate]) =month(now)") AS abchill,
DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abmhende' AND month([currentdate]) =month(now)") AS abmhende,
DSum("OMSDailyPages","DailyOmsWorkTbl","OmsWorkerAssigned='abcallen' AND month([currentdate]) =month(now)") AS abcallen,
DSum("OMSDailyPages","DailyOmsWorkTbl","month([currentdate]) =month(now)") AS total
FROM DailyOmsWorkTbl
GROUP BY Month([CurrentDate]), DSum("OMSDailyPages","DailyOmsWorkTbl","month([currentdate]) =month(now)")
HAVING (((Month([CurrentDate]))=Month(Now())));

 

[DotNetGnat]

great...you got it working in the end...

-DNG