Return values from Previous Day

[trock12]

Maybe there is a simple answer, maybe there is not. I have 2 user groups, 1 enters "incoming volume", 1 enters "outgoing volume" both on forms with a hardcoded "input date" for the date they are entering. For the sake of my example, lets call Incoming Volume = X, Outgoing Volume = Y and the Difference = Z.

I have a report from queries created that takes this information as of today's date. So it would show for example X = 300, Y = 250, Z =50 with a date of 1/14/2016. Is there an easy way to duplicate this report to make one for yesterday (aka Today -1), 2 days ago, 3 days ago, etc. so that way it can show the trend of X, Y, Z for the past 5 business days?

[June7]

Easy to produce a report filtered to a particular day. But if you want all 5 days side-by-side ... easy? - I doubt it.

Maybe 5 subreports.

Maybe 5 queries then join those queries.

Maybe CROSSTAB query. http://allenbrowne.com/ser-67.html

Maybe a query that emulates a CROSSTAB. http://datapigtechnologies.com/flashfiles/crosstab.html

[JamesDeckert]

Don't duplicate the report 4 more times. Find a way of using the same report to change the X & Y values.
Does the user select which of the 5 reports to run, or do all 5 run every time?
The query can reference a loaded form value so code can be used to kick off the report based on a query, then change the value in the form and kick off a new report.

[orange]

In general you could make a function that selects the values from a specific date relative to today. Today is Date in Access. So yesterday would be Date - 1...
But it really depends on details of what you are trying to accomplish. A little more info would help readers.

[trock12]

The picture is what I'm trying to do.

[June7]

Options in post 2 still apply.

[trock12]

Options in post 2 still apply.

Thanks June7. The crosstab query works for pulling the X and Y values. The issue I'm having is the Z value isn't stored anywhere. It's strictly a formula being returned using the current information. As X/Y change, then Z changes but isn't actively stored. I guess if I knew how to store it as a value for a particular date that would work. Where somehow, daily, I run a report or form where it will auto-calculate Z and then store that info in a table to be able to query at a later time. Not sure exactly how to accomplish that option, either.

[June7]

Saving calculated data is usually a bad idea, especially aggregate data. And saving aggregate data requires VBA. Review http://allenbrowne.com/AppInventory.html

Why would you need to store the Z value? Just calculate when needed.

[trock12]

"Z" in my situation is "outstanding items". Was looking to calculate and store the Z value for the ability to do future trend analysis of volume/work required.

[June7]

Queries should be able to produce the calculated data you need for analysis. No need to save to table.