How do I compare receipts for the same month in different years in Microsoft access

I have a table with three fields post_prd, post_yr,  and Receipts.  I would like a report that compares the receipts for each post_prd against the Receipts for the previous post_prd showing the variance between the two post_prd's both in dollars and percentage so that one column shows year 1 second column shows year two and third column shows variance in dollars fourth column shows variance in percentage.  At the bottom of each column, I would like a total for each year.  I would like it to be presented in landscape spreadsheet format.  I would like to do this with a Microsoft Access report.  Any help would be appreciated.
dur2348Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
What do you mean when you say "receipts"?

Is it like received date 1/1/2014 or True/False, etc. Please redo your question with some sample data, table names, query names, SQLs, etc. Or prepare a watered down version of your application to upload.

Thanks,

Mike
0
James ElliottManaging DirectorCommented:
Why are there so many constraints in your question? Is this an assignment?

I only ask because I personally would write a simple query which groups by year, month and aggregates receipts, and then pull it into Excel and do a simple pivot table.

Rgds
0
dur2348Author Commented:
This is a report for a business.  It will be distributed to others as attachment to email.  It should show the period (month), year and receipts in dollars.  It should show the variance (difference) in receipts between the month this year and the same month last year.  It should show the year to date difference as well.  It should show the historical data also (for example, difference from 2007 to 2008 and 2008 to 2009 etc.).  To this point, I have created a crosstab query in Access and exported the data to Excel.  I can then insert columns to calculate variances etc.  Each month of the year the data will be updated in the table and the report or spreadsheet will have to be updated.  I am looking for solution that will allow update of table with new data and report or spreadsheet to update automatically.  I have attached a small sample of data.
SampleData.xls
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
How good are you with VBA?
Getting data from one year, and a previous year via a query is a real pain in the neck.
Two recordsets in VBA and putting the values into unbound textboxes on a report is child's play -- if you know VBA.
If your life is code-free, well then it's hard.

I take it that the report will only have 12 lines, one for month 1 through 12?
0
dur2348Author Commented:
Do not know VBA.
0
Nick67Commented:
Willing to learn?
Here's more data extrapolated from your sheet and an mdb sample.
Open the mdb and then the report
SampleData.xls
PreviousYear.mdb
0
dur2348Author Commented:
Happy to try.  Report looks good.  Will need column for Year To Date as the months go.
0
Nick67Commented:
That , too is just math.
Finding a data value, storing it in a public variable and carrying it forward to the next detail section.

Sample attached
PreviousYear-v1.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dur2348Author Commented:
I have to leave my computer now.  I will return in the am.  I am very interested in how you created report.
0
dur2348Author Commented:
Sorry for the delay in responding.  Am not sure of syntax to make this change

When you generalize this you'll want "WHERE Post_yr between " & SomePreviousYear & " and " SomeThisYear
'instead of the hardcoded 1995 and 1996 I have now
0
Nick67Commented:
In the sample, there is only data from two years.
There wasn't any call to get fancy with the recordsource of the report, which was just
SELECT DISTINCT sumReceiptsByPeriod.Post_prd
FROM sumReceiptsByPeriod;

because the only thing the report is really bound to is the months (1-12)

In the Detail_Format event, where all the heavy lifting takes place, the SQL was hard-coded like so
SQL = "select * from sumReceiptsByPeriod where Post_prd = " & Me.Post_prd & " and Post_yr between 1995 and 1996 order by Post_yr"

I assume that in production, you'll have more than just 1995 and 1996 data, so you'll need some method of selecting the present year and the previous year and getting them into the syntax.  Usually this is done through a form.  The form has controls to hold the values that need to be passed in, and a command button to open the report(s).

I added such a form to the sample and called it frmReports.  I added two textboxes, txtPrevious and txtPresent.  Now the syntax for the heavy lifting is
SQL = "select * from sumReceiptsByPeriod where Post_prd = " & Me.Post_prd & " and Post_yr between " & Forms!frmReports!txtPrevious & "  and " & Forms!frmReports!txtPresent & " order by Post_yr"
If there were more years' worth of data, you then could select what was desired.
PreviousYear-v2.mdb
0
Nick67Commented:
Did it all work well for you?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.