Display data for all months in a year

[sushmitha]

I have a table which has Date, Total Invoice, Customer region etc

I need to calculate total invoices generated for each month in a year

Out put should be displayed group by each month name

Ex: Total Jan Feb Mar Apr May etc
100 30 40 50 80 200 etc

Please let me know how I can get this

 

[JohnLee]

Hi,

create a crosstab query based on your table using the query wizard, which will walk you through the process. You should get your desired results.

John

 

[sushmitha]

Hi,

create a crosstab query based on your table using the query wizard, which will walk you through the process. You should get your desired results.

John

I need to create a query. But how to get all months for a year where I have only date column in my table

Can you send me the syntax or formula which gives me the desired result

I am new to Access db.

 

[Brianwarnock]

Month(date)

Brian

 

[sushmitha]

Notworking

Month(date)

Brian

I am using the following formula to get all data by each month in a year

SELECT Sum(X.Totalinvoice) AS [total], X.Month
FROM [select
idate, Totalinvoice,
switch(Month(vDate)=1,Jan,
Month(vDate)=2,Feb,
Month(vDate)=3,Mar,
Month(vDate)=4,Apr,
Month(vDate)=5,May,
Month(vDate)=6,Jun,
Month(vDate)=7,Jul,
Month(vDate)=8,Aug,
Month(vDate)=9,Sep,
Month(vDate)=10,Oct,
Month(vDate)=11,Nov,
Month(vDate)=12,Dec,
) as Month
from
Table
where month(vDate) = month(date()
and Year(vDate)=Year(vDate))
]. AS X
GROUP BY X.Month;

Its is not displaying any data and prompting me to enter month number

 

[JohnLee]

Hi,

Did you create a crosstab query or a select query? Did you use the query wizard as suggested?

If you used the query wizard it walks you through the setting up the query of your choice in the case a crosstab query [you need to have selected crosstab query when the wizard is running], prompting you for your parameters/criteria etc.

can you confirm you did this.

John

 

[sushmitha]

Hi,

Did you create a crosstab query or a select query? Did you use the query wizard as suggested?

If you used the query wizard it walks you through the setting up the query of your choice in the case a crosstab query [you need to have selected crosstab query when the wizard is running], prompting you for your parameters/criteria etc.

can you confirm you did this.

John

I created a query in design view. Now everything is working fine..But we have data only for Jun, Jul, Aug. But I need the output should show for thre rest of months total invoice as zero. Bcoz the output file is used by another application where they want to see all months

How can I do this ??

 

[Brianwarnock]

You are going to need to create a table of months which you will join to the output from your current query on month selecting all records from the new table and only those where the fields are equal from your query ie an outer join, and use NZ on the total field to generate 0.

Note that NZ creates a string so if no arithmetic is done you need to use Int(Nz(... to create your number, whereas
Sum(Nz(X.totalinvoice,0)) would be ok, if you can work the join into your current query.

Brian