Show Data for Previous 6 months without updating report every mont

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

Guest

I'm trying to create a report that will display the past 6months in the
headers withs its corresponding details. For Example: If i were to generate
my report today, i would like show data back through may. In jan i would
like to show data back through june. My source table consist of 3 fields,
"Product", "Perdmth" and "ProjectCount". Where "Perdmth" is the first date
of each month the date my report is for

Thanks
 
I don't understand your "Perdmnth" field.

Tou specify in a query that you want the last six months, you would need to
have a column in the query that would grab the month of the date field
associated with the record. Then in the criteria you would put a statement
to say Between Six months ago and Last month.

You column would be something like...


MonthofRecord: Format([SomeDateField], "mm")



In the criteria, you'd put something like...



Between DateDiff("mm",Date(),-6) and Format(Date(),"mm")


You might need to tweek that DateDiff part. I did not test it. The help
files can give you more guidance.


Rick B
 
Rick,

Thanks, Perdmnth is same as MonthofRecord. Once i have the 6 months, How
would i lay this out in an access report that would show my months horizontal
and i dont have to update my report every month, yet is would still show me
that previous six months. I want to do something like a crosstab query, but
with that i would have to pull in each new month everytime i run the report

Rick B said:
I don't understand your "Perdmnth" field.

Tou specify in a query that you want the last six months, you would need to
have a column in the query that would grab the month of the date field
associated with the record. Then in the criteria you would put a statement
to say Between Six months ago and Last month.

You column would be something like...


MonthofRecord: Format([SomeDateField], "mm")



In the criteria, you'd put something like...



Between DateDiff("mm",Date(),-6) and Format(Date(),"mm")


You might need to tweek that DateDiff part. I did not test it. The help
files can give you more guidance.


Rick B




Fpara said:
I'm trying to create a report that will display the past 6months in the
headers withs its corresponding details. For Example: If i were to generate
my report today, i would like show data back through may. In jan i would
like to show data back through june. My source table consist of 3 fields,
"Product", "Perdmth" and "ProjectCount". Where "Perdmth" is the first date
of each month the date my report is for

Thanks
Click to expand...
Click to expand...
 
Back
Top