Comparing Historical Data with User Defined Date Ranges

[dy408]

Hello ... I'm trying to create a report that compares historical data between user specified dates. A dialog box has worked for making reports based on user defined dates, however I'm having trouble comparing periods (ex. a report comparing Oct. 15 to Jan 15 of this year and all previous years).

Also, since the date ranges can cross a calendar year I haven't found a way to auto-group the data.

The table is very simple and is comprised of the following fields: Date, location & quantity

Thanks!
Dave

 

[hneal98]

Are you trying to compare the 15th of each month?

 

[dy408]

No, I just used the 15th as an example.

However, if it is only possible to use either the 1st or 15th of the month as cutoff points that will work providing I am able to specify which time periods to include.

This could be Dec 1 - March 1, Jan 15 - August 15, etc.

With these periods defined (by users), the goal is then to have the report show data from all previous periods (with the same dates), which in this case is the past 3 years.

Thanks

 

[hneal98]

No, what I am trying to get at is are you trying to compare the same date for each month? Is this monthly or some other time period?

 

[hneal98]

Or I guess, now that I re-read your original post, it looks like you are just trying to set up the report so it can compare two different dates depending on what the user selected. Is this correct?

 

[hneal98]

You should be able to run a query that pulls data for both periods. One for the first date and one for the other date. You may have to do them in two or more queries however and then add the data to the same table.

I would create a table that has fields for both sets of data. Kind of like this:

date1_data1
date1_data2
date1_data3
date1_data4
date2_data1
date2_data2
date2_data3
dat32_data4

This should work for putting the data into a single report for the user to see together. This is provided that the user is only selecting two dates to compare at any given time.

create your table with the fields in it. Then you would create one update query to update the date1 set of fields and then create another update query to update the date2 set of fields.

Let me know if this does not make sense or if I am totally missing the mark.

 

[dy408]

Thanks for the help, but it is not comparing 2 dates.

It is choosing a starting & ending date and then summing the quantities (for each location) during this choosen time span and then comparing it to the same time spans in previous years.

An example would be:

10/1/05 thru 2/1/06 10/1/04 thru 2/1/05
Italy 560 units 500 units
England 640 units 340 units
France 538 units 743 units

For this example data set the user would have selected 10/1 and 2/1 as the starting and ending dates to compare.

The report would automatically continue back for all previous time spans for which the database has data.

 

[hneal98]

Ok. I get it. It appears you would need to find the min and max dates in your data base and then based on the date ranges that the user selects, you would need to find how many of these equivelant time spans exist.

In other words, if the user selects 01/01/2005 - 05/31/2005, and your data went back to 2000, you would need to run 6 iterations of history to get what you want.

I don't know what your expertise is in Access, but you would need to run a For loop in VBA to come up with the data sets that you could ultimately add together in one table for your report.

This code would be kicked off once the user selects the final date in the time period.

Let me know what your skill level is.

 

[dy408]

Thanks - that sounds like what I'm looking for!!!

I'm pretty good with Access, but unfortunately my VBA skill level is low. I have used some very basic codes, but am not sure how to write this.

If you could possibly write an example or point me in the right direction I'd really appreciate it.

 

[hneal98]

It is a bit hard to come up with a sample without knowing what your table structure looks like. I will see what I can do.

 

[dy408]

Thanks again for helping!

The table structure is just 2 tables (tblTracking & tblOrigin (which just has the country names). The fields are as followed:


tblTracking:
ArrivalDate
OriginID > tblOrigin: OriginID, Origin
Quantity

The quantity field refers to number of units & the ArrivalDate field is the one that the code will need to group.