Solved

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

Posted on 2014-10-04
12
94 Views
Last Modified: 2014-10-23
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.
0
Comment
Question by:dur2348
12 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40361779
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
 
LVL 12

Expert Comment

by:James Elliott
ID: 40361932
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
 

Author Comment

by:dur2348
ID: 40363689
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40364163
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
 

Author Comment

by:dur2348
ID: 40364297
Do not know VBA.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40364375
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:dur2348
ID: 40364516
Happy to try.  Report looks good.  Will need column for Year To Date as the months go.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40364609
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
 

Author Comment

by:dur2348
ID: 40364743
I have to leave my computer now.  I will return in the am.  I am very interested in how you created report.
0
 

Author Comment

by:dur2348
ID: 40376813
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40381221
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40399815
Did it all work well for you?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now