Sequential Date Field (1 Viewer)

Hi all,

bit of an odd one...

I have 2 tables in a query. One has dates, which aren't always sequential, the other has the default values required where the table with dates doesn't have a matching record...

I need to create a field that generates a record for every date between 2 dates. I will then have a calculated field which will be set to a specified value which is in the table containing a matching date, failing that, it will match another field within the second table.

I'm fine with the calculated field, but I'm struggling with how to force a record for every day between the from and to date, other than creating a single table with a bunch of sequential numbers!

Any suggestions/help appreciated.

IvanM said:

.... creating a single table with a bunch of sequential numbers!

Click to expand...

That's the way I'd go. It is simple and takes seconds to set up. Ten years of dates is only a few thousand records so don't worry about space.

It might be worth asking what the purpose of the 2 tables is, as it sounds like you may have some redundancy in your data, that might mean you don't need to do what you are describing ?

Stopher:
It's a last resort as it just seems a bodgy workaround.

Minty:
There' no redundancy, the issue is -

Table 1: Default values - Contains the default values which apply to the relevant DAY of the week, Mon-Sun.

Table 2: Date Specific table - Contains values that are date specific deviations from the default values.

So the idea is I need to generate a query that has, say for example, the next 14 days sequentially, defaulting to the DAY value from Table 1, unless a matching date exists in Table 2, in which case it uses this non-default value.

I hope that explains it better!

The calculation to obtain the relevant value from the relevant table isn't a problem, it's the forced sequential dates that I need...