Reports

[deadlyhunk]

Hi,

I have a table which contains the fields Empid, Empname, Amount, Date_check_rcvd . Now my question I want to generate a report which gives the sum of amount by month and by year thru the date of check recieved. I have a problem in writing the query where if I say sum(amount) and execute the query it gives an error saying " you tried to execute the query that does not include a specified expression Empid as part of aggregate function". I have very little experience in writing the queries. I just don't know what is wrong??can anyone help me out????

[MMcCarthy]

Hi,

I have a table which contains the fields Empid, Empname, Amount, Date_check_rcvd . Now my question I want to generate a report which gives the sum of amount by month and by year thru the date of check recieved. I have a problem in writing the query where if I say sum(amount) and execute the query it gives an error saying " you tried to execute the query that does not include a specified expression Empid as part of aggregate function". I have very little experience in writing the queries. I just don't know what is wrong??can anyone help me out????

When using an aggregate query (query with things like Sum, Count, Avg, etc.) you must group by all fields not being aggregated. You can't do both Month and Year in the same query.

For instance...

For the Month:

SELECT Empid, Empname, Sum(Amount), Month(Date_check_rcvd)
FROM TableName
GROUP BY Empid, Empname, Month(Date_check_rcvd);

For the Year:

SELECT Empid, Empname, Sum(Amount), Year(Date_check_rcvd)
FROM TableName
GROUP BY Empid, Empname, Year(Date_check_rcvd);

Mary

[ADezii]

Hi,

I have a table which contains the fields Empid, Empname, Amount, Date_check_rcvd . Now my question I want to generate a report which gives the sum of amount by month and by year thru the date of check recieved. I have a problem in writing the query where if I say sum(amount) and execute the query it gives an error saying " you tried to execute the query that does not include a specified expression Empid as part of aggregate function". I have very little experience in writing the queries. I just don't know what is wrong??can anyone help me out????

Assumptions:
__1 Table Name = Table1
__2 [Empid] AutoNumber (Primary Key)
__3 [Empname] (TEXT 50)
__4 [Amount] (CURRENCY)
__5 [Date_check_rcvd] (DATE/TIME)

TRANSFORM Sum(Table1.Amount) AS SumOfAmount
SELECT Table1.Empname AS Employee, Sum(Table1.Amount) AS [Total Amount], Year([Date_check_rcvd]) AS Year
FROM Table1
GROUP BY Table1.Empname, Year([Date_check_rcvd])
PIVOT Format([Date_check_rcvd],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

[deadlyhunk]

Thank you for the reply.

I am sorry in my previous post I forgot to mention other field i.e., Memosent which of data type date/time. Now when I run the query you have given me in the post it prompts me the message saying " enter the parameter value Memosent". When I click ok on the message then the report opens.

[deadlyhunk]

My reply is for the post given by mmccarthy

[MMcCarthy]

Thank you for the reply.

I am sorry in my previous post I forgot to mention other field i.e., Memosent which of data type date/time. Now when I run the query you have given me in the post it prompts me the message saying " enter the parameter value Memosent". When I click ok on the message then the report opens.

What is this field and why do you need to include it. ADezii's example is better than mine BTW.

Mary

[deadlyhunk]

I even tried the code given by ADezii but it prompts the same msg saying "enter the parameter value date_check_rcvd " and also when I run the report it asks me to enter the amount,date_che ck_rcvd

[deadlyhunk]

The field is nothing but the date when the check was sent.

[MMcCarthy]

The field is nothing but the date when the check was sent.

Can you leave it out as the date_check_rcvd is the date being used for the calculation. Another date like the memosent date will mess up all the figures.

Mary

[deadlyhunk]

Can you leave it out as the date_check_rcvd is the date being used for the calculation. Another date like the memosent date will mess up all the figures.

Mary

I don't think I can do that because I want all the fields in the report.

[MMcCarthy]

Try this revised version of ADezii's code.

TRANSFORM Sum(amount) AS TotalAmount
SELECT Empname AS Employee, Sum(amount) AS TotalAmount, Year([date_check_rcvd]) AS Year
FROM TableName
GROUP BY Empname, Year([date_check_rcvd])
PIVOT Format([date_check_rcvd],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Mary

[MMcCarthy]

I don't think I can do that because I want all the fields in the report.

To explain ...

We are totaling the Amount per Employee based on the Month and Year of the date the check was received. If another date is introduced then you can't calculate the figure as it will be broken down over the other date.

Mary

[deadlyhunk]

Try this revised version of ADezii's code.

TRANSFORM Sum(amount) AS TotalAmount
SELECT Empname AS Employee, Sum(amount) AS TotalAmount, Year([date_check_rcvd]) AS Year
FROM TableName
GROUP BY Empname, Year([date_check_rcvd])
PIVOT Format([date_check_rcvd],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Mary

when I run the query it gives an error saying duplicate output alias "TotalAmoun t"

[deadlyhunk]

To explain ...

We are totaling the Amount per Employee based on the Month and Year of the date the check was received. If another date is introduced then you can't calculate the figure as it will be broken down over the other date.

Mary

So, Do I need to remove the field in the query?

[MMcCarthy]

So, Do I need to remove the field in the query?

Yes.

And the error on ADezii's was my fault. Try this...

TRANSFORM Sum(amount) AS SumOfAmount
SELECT Empname AS Employee, Sum(amount) AS TotalAmount, Year([date_check_rcvd]) AS Year
FROM TableName
GROUP BY Empname, Year([date_check_rcvd])
PIVOT Format([date_check_rcvd],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Mary