retrieving the right amount of records

LuukVaessen

New member
Local time
Today, 18:08
Joined
Nov 14, 2005
Messages
5
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).

How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.

So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.

Thanks for helping me out!

Luuk
 
Hi Luuk -

Whew, doesn't sound like an easy one to me.

If you want to do this in a query, then the only thing I can think of is to do this in two stages. First, set up a query with ten columns to check for each of the preceeding months. Then if all ten are present, set a flag that indicates the record should be included. Next, use a query that checks for the flag and gives you the correct records. [Note: you might find this easier if your date field is based on the first of the month, or at least you have a calculated field that returns the first of the month. Then you can at least test for a previous month with the DateAdd function.)

For something like this, VBA might be a better solution than a query. However it still would require some thought in getting your record collection right.

Sorry if that's not much help.

- g
 
Thanks for the reply!

I was planning on creating a query that counts the number of records in a timeframe that i specify to be 10 months, i would then insert a calcuated field which counts the number of records in the specified 10 month timeframe. The criteria for this calculated field is then set at 10. Then i can change the timeframe by hand (rolling window), and append the results to a table.

It requires some more work, but i think it gets the required results. Do you perhaps know a faster approach to this problem? Since this requires me to change the timeframe by hand for about 100 times.

Thanks!

Luuk
 
Hi Luuk -

My approach for this sort of problem would be to write some VBA code to accomplish this. I think that it would be a better approach for any future work (for example, to be able to change the minimum required timeframe).

It depends on your VBA skills, though and you may find it easier (for now anyway) to use the query.

- g
 
That would be the ideal indeed. However, i am no software programmer, so i think i will have to settle for good old manual work :)

Thanks

Luuk
 

Users who are viewing this thread

Back
Top Bottom