Solved

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

Posted on 2014-10-04
12
101 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

15 Experts available now in Live!

Get 1:1 Help Now