Totalling data by MTD and YTD

[herbc0704]

I have a database that complies transactional data from months over the past two years. Currently, I export this information excel and then create a report from a pivot tables that gives me inventory line items comparing sales for current MTD, YTD, Last Year Current Month, and Last Year to date.

Is there an easy way in access to use a parameter that I'd would enter the month I would want to run the report for and have a report give me the MTD, YTD, LMTD and LYTD sales for each inventory item?

 

[boblarson]

Criteria for

MTD -
Between DateSerial(Year(Date()), Month(Date()), 1) And Date()

YTD -
Between DateSerial(Year(Date()), 1, 1) And Date()

LMTD -
Between DateSerial(Year(Date())-1, Month(Date()), 1) And DateSerial(Year(Date())-1, Month(Date()), Day(Date()))

LYTD -
Between DateSerial(Year(Date())-1, Month(Date()), 1) And DateSerial(Year(Date())-1, Month(Date()), Day(Date()))

Now for any of the parts with Month(Date()) just replace it with [Enter Month] if you wish to be able to enter a specific month.

 

[herbc0704]

Thanks Bob again....

I have a question.

Does this mean I have to create a separate query for year time value and then create a crosstab query limking the four together?

 

[boblarson]

Thanks Bob again....

I have a question.

Does this mean I have to create a separate query for year time value and then create a crosstab query limking the four together?

You might not but I can't think of a way at the moment to do so.

You will want to have the final query have the original table with the date field linked to the other queries as an OUTER JOIN so that the dates will all show up and only the values that fit for any of the four will show up. You probably will need to include the date criteria on the field in that query too. Remember to rename your date field in each query so that you don't have to try to specify the query name in with the field. But I'm not 100% sure what data you are going to be pulling so you will have to modify this to fit your needs.