rowbro

Board Regular
Joined
Dec 16, 2010
Messages
50
Good day all. I’ve got a time series database for my economic data, and it generally works pretty well except I have one issue around data frequency in my queries.

In short, different indicators have different release frequencies, such as daily, monthly, quarterly, annual etc.

I have two specific issues I’d like to address: 1. When extracting data, if you enter the wrong data frequency in the form/query, it finds no data, and thus, I’d like to tweak the form/query to only show a list of available frequencies for the data once the indicator has been selected, not all the frequencies of all the data (from a frequency table). How would I go about this?

2. Where I’m trying to extract data on a less frequent basis than it is released (I.e. I want annual data and the data I have is monthly), I would like the query to either average, select the end of period, or sum (the correct one of these options will depend on the data, and the correct option will be noted in the database) the more frequent data to give the less frequent values. For example, if I have monthly diamond production data and I want to extract annual data, I would like the query to sum all monthly data for the year and show that value. How could this be done?

Thanks in advance!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Summing data using dates and queries

Good day,

I am working on a time-series database and I am trying to make the data extraction as simple as possible. In my retrieve form, users can extract data by selecting time ranges for data, indicators, data frequency etc.

I am, however, trying to find a way to sum data if the user wants to query/extract data on a less frequent basis than it is released (for example the user wants annual diamond production data and I only have it by month). Is there some way to do this using sums in the query?

A sample of the database can be downloaded here: https://goo.gl/CbBJrp
 
Upvote 0
Re: Summing data using dates and queries

1 - use a combo to present the frequency choices based on the indicator (whatever that is). Create a select query that does this, and use it as the combo's row source.
2 - probably you'd have to sum all the data and divide by the count of the frequency portion (week, month). Can't see how you can get monthly data for values that are only input once per year (annual data) which is how I interpret your statements. But you can get the count of days/weeks/months between 2 dates using DateDiff function if that helps you. Sounds to me like a lot of coding is required because of all the variables.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top