Help with selections based on dates

[Tomss]

Hi everyone,

I'm looking for a solution to a problem that is driving me crazy by now

I need a query that filters specific records from a table based on dates.

Below is a simplified version of two tables in my database. In reality the table contains thousands of rows.

Table [distributions]

distribtion value- field effective date
1 - 01/01/12
2 - 15/01/12
3 - 03/03/12
4 - 06/06/12
(and so on for the whole year)

So, this table contains distribution values which become effective on or after the stated 'effective date'. These values are regulary updated and the new values are then appended to this table. The effecitve date is then set to a newer date (usually the date of the append)

in another table there is a list of dates from 01/01/2012 to 12/31/2012

Now I want create a query that uses the distribution table and the table containing the dates to display a list of dates in the correct order and the distribution that was active on that specific date. Using the example above the result should be:

01/01/2012 - 1
01/02/2012 - 1
and so on..
01/15/2012 - 2
01/16/2012 - 2
and so on..
03/03/2012 - 3
03/04/2012 - 3
03/05/2012 - 3
and so on
06/06/2012 - 4
and so on

I have been trying to get this right for days but it doesn't work. It either displays a row for all distributions or only the last one (in this example 06/06/2012).

I'm trying to do this with a query only. I'm a VB newbie so... if that's how it must be done, bear with me

Thanks for helping out!

 

[jdraw]

Have you tried writing your intended query in plain english to ensure you fully understand WHAT you are trying to do? If you can do that, then putting the query into SQL syntax should be "relatively straight forward".

I would expect that the usual format for effective date would be (effective period) with
EffectiveStartDate and EffectiveEndDate, that is a specific range.