Formatting day dates into months on a query for graphing...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have Access 2000 and my records are all by individual day date. I want to
be able to run a pivot table and chart that shows monthly totals but can't
seem to figure out how to write the expression in the query to make this work.

I tried

ThisMonth:Format(Now(),"mmm")

but that only gave me a column that labeled every record as October.

Any help is appreciated,

Joe
 
Create a new query and pull down the field you want monthly totals for into
the first query field.

Enter the following in the second query field:
MonthForTotal:Month([YourDateField])

Next click on the Sigma (looks like E) in the toolbar at the top of the
screen. Under the field you are totalling, change Group By to Sum.

This query will give you the totals for each month.

If your data spans more than one year, add this expression to your query:
YearForTotal:Year([YourDateField])
Leave it as Group By.
 
Thanks, that was the answer I needed!

Joe

PC Datasheet said:
Create a new query and pull down the field you want monthly totals for into
the first query field.

Enter the following in the second query field:
MonthForTotal:Month([YourDateField])

Next click on the Sigma (looks like E) in the toolbar at the top of the
screen. Under the field you are totalling, change Group By to Sum.

This query will give you the totals for each month.

If your data spans more than one year, add this expression to your query:
YearForTotal:Year([YourDateField])
Leave it as Group By.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


JoeAccess381 said:
Hi,

I have Access 2000 and my records are all by individual day date. I want to
be able to run a pivot table and chart that shows monthly totals but can't
seem to figure out how to write the expression in the query to make this work.

I tried

ThisMonth:Format(Now(),"mmm")

but that only gave me a column that labeled every record as October.

Any help is appreciated,

Joe
Click to expand...
Click to expand...
 
Hi,
This helps me in creating a monthly performance graph, I created a querry
and chcange the date into month name and then make a graph on the basis of
this query now only one problem left that if lets say in October there is no
entry in the table it didnt show the month of October, I want that in querry
or in graph it gives all the month names on X-axis and if there is no record
in some month it shows zero, is it possible??
Thanks in advance

JoeAccess381 said:
Thanks, that was the answer I needed!

Joe

PC Datasheet said:
Create a new query and pull down the field you want monthly totals for into
the first query field.

Enter the following in the second query field:
MonthForTotal:Month([YourDateField])

Next click on the Sigma (looks like E) in the toolbar at the top of the
screen. Under the field you are totalling, change Group By to Sum.

This query will give you the totals for each month.

If your data spans more than one year, add this expression to your query:
YearForTotal:Year([YourDateField])
Leave it as Group By.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


JoeAccess381 said:
Hi,

I have Access 2000 and my records are all by individual day date. I want to
be able to run a pivot table and chart that shows monthly totals but can't
seem to figure out how to write the expression in the query to make this work.

I tried

ThisMonth:Format(Now(),"mmm")

but that only gave me a column that labeled every record as October.

Any help is appreciated,

Joe
Click to expand...
Click to expand...
Click to expand...
 
Hi,
This helps me in creating a monthly performance graph, I created a querry
and chcange the date into month name and then make a graph on the basis of
this query now only one problem left that if lets say in October there is no
entry in the table it didnt show the month of October, I want that in querry
or in graph it gives all the month names on X-axis and if there is no record
in some month it shows zero, is it possible??
Thanks in advance

JoeAccess381 said:
Thanks, that was the answer I needed!

Joe

PC Datasheet said:
Create a new query and pull down the field you want monthly totals for into
the first query field.

Enter the following in the second query field:
MonthForTotal:Month([YourDateField])

Next click on the Sigma (looks like E) in the toolbar at the top of the
screen. Under the field you are totalling, change Group By to Sum.

This query will give you the totals for each month.

If your data spans more than one year, add this expression to your query:
YearForTotal:Year([YourDateField])
Leave it as Group By.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


JoeAccess381 said:
Hi,

I have Access 2000 and my records are all by individual day date. I want to
be able to run a pivot table and chart that shows monthly totals but can't
seem to figure out how to write the expression in the query to make this work.

I tried

ThisMonth:Format(Now(),"mmm")

but that only gave me a column that labeled every record as October.

Any help is appreciated,

Joe
Click to expand...
Click to expand...
Click to expand...
 
Hi,
This helps me in creating a monthly performance graph, I created a querry
and chcange the date into month name and then make a graph on the basis of
this query now only one problem left that if lets say in October there is no
entry in the table it didnt show the month of October, I want that in querry
or in graph it gives all the month names on X-axis and if there is no record
in some month it shows zero, is it possible??
Thanks in advance

JoeAccess381 said:
Thanks, that was the answer I needed!

Joe

PC Datasheet said:
Create a new query and pull down the field you want monthly totals for into
the first query field.

Enter the following in the second query field:
MonthForTotal:Month([YourDateField])

Next click on the Sigma (looks like E) in the toolbar at the top of the
screen. Under the field you are totalling, change Group By to Sum.

This query will give you the totals for each month.

If your data spans more than one year, add this expression to your query:
YearForTotal:Year([YourDateField])
Leave it as Group By.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


JoeAccess381 said:
Hi,

I have Access 2000 and my records are all by individual day date. I want to
be able to run a pivot table and chart that shows monthly totals but can't
seem to figure out how to write the expression in the query to make this work.

I tried

ThisMonth:Format(Now(),"mmm")

but that only gave me a column that labeled every record as October.

Any help is appreciated,

Joe
Click to expand...
Click to expand...
Click to expand...
 
Back
Top