A record for each item in a range

[Branko]

In order to avoid entering rates for each month
individually, I would like to have a table with the
following structure:

RevType | FromMonth | ToMonth | Rate
----------------------------------
1 3 5 1.25
1 6 7 1.05
2 2 4 1.3
etc.

It basically defines that the rate for Revenue number one
for months March - May is 1.25, June and July is 1.05, ...

Need a query that will have rate for each month as a new
recod:

RevType | Month | Rate
1 3 1.25
1 4 1.25
1 5 1.25
1 6 1.05
1 7 1.05
2 2 1.3
2 3 1.3
2 4 1.3

Any ideas apreciated. Thanks.

 

[Michel Walsh]

Hi,


See your first post.


Vanderghast, Access MVP

 

[Van T. Dinh]

Since you didn't mention how you differentiate the same month of different
year, I am going to ignore the year also.

* Create a Table tblMonth with the Field [MonthNo] (use the same numeric
type as your Fields [FromMonth] and [ToMonth]) as the PK.

* Populate this Table with 12 Records with [MonthNo] from 1 to 12.

* Try the SQL String something like:

****Untested****
SELECT YT.RevType, MT.MonthNo, YT.Rate
FROM [YourTable] AS YT
INNER JOIN [tblMonth] AS MT
ON (YT.FromMonth <= MT.MonthNo)
AND (YT.ToMonth >= MT.MonthNo)
ORDER BY YT.RevType, MT.MonthNo
********

 

[Guest]

Van,

Thanks for the reply. The things are getting more
complicated. I wanted to make it simple, but in fact I
will need another field for the year. So my table would
look like:

RevType | Year | FromMonth | ToMonth | Rate

Again, in this table I want to have records only for
those types/periods where the rate value differs from 1.

Any ideas would be apreciated.

Thanks again.

Branko

-----Original Message-----
Since you didn't mention how you differentiate the same month of different
year, I am going to ignore the year also.

* Create a Table tblMonth with the Field [MonthNo] (use the same numeric
type as your Fields [FromMonth] and [ToMonth]) as the PK.

* Populate this Table with 12 Records with [MonthNo] from 1 to 12.

* Try the SQL String something like:

****Untested****
SELECT YT.RevType, MT.MonthNo, YT.Rate
FROM [YourTable] AS YT
INNER JOIN [tblMonth] AS MT
ON (YT.FromMonth <= MT.MonthNo)
AND (YT.ToMonth >= MT.MonthNo)
ORDER BY YT.RevType, MT.MonthNo
********

--
HTH
Van T. Dinh
MVP (Access)

In order to avoid entering rates for each month
individually, I would like to have a table with the
following structure:

RevType | FromMonth | ToMonth | Rate
----------------------------------
1 3 5 1.25
1 6 7 1.05
2 2 4 1.3
etc.

It basically defines that the rate for Revenue number one
for months March - May is 1.25, June and July is 1.05, ...

Need a query that will have rate for each month as a new
recod:

RevType | Month | Rate
1 3 1.25
1 4 1.25
1 5 1.25
1 6 1.05
1 7 1.05
2 2 1.3
2 3 1.3
2 4 1.3

Any ideas apreciated. Thanks.

.

 

[Van T. Dinh]

If each Record in your Table doesn't span more than 1 year, i.e. the
FromMonth and ToMonth in each Record always refer to the same year, you can
do the same.

Try:

****Untested****
SELECT YT.RevType, YT.[Year], MT.MonthNo, YT.Rate
FROM [YourTable] AS YT
INNER JOIN [tblMonth] AS MT
ON (YT.FromMonth <= MT.MonthNo)
AND (YT.ToMonth >= MT.MonthNo)
ORDER BY YT.RevType, MT.MonthNo
********

 

[Branko]

Wow, I guess that's it. Thanks a lot. It will take me at
least a week to understand what these six lines of code
do .

How do I make sure that Type+Year+MonthNo are unique for
each record? In other words, if periods (with different
rates) in the original table overlap, there will be more
than one record with the same Year, Month, and Type, but
different rate.

Branko

-----Original Message-----
If each Record in your Table doesn't span more than 1 year, i.e. the
FromMonth and ToMonth in each Record always refer to the same year, you can
do the same.

Try:

****Untested****
SELECT YT.RevType, YT.[Year], MT.MonthNo, YT.Rate
FROM [YourTable] AS YT
INNER JOIN [tblMonth] AS MT
ON (YT.FromMonth <= MT.MonthNo)
AND (YT.ToMonth >= MT.MonthNo)
ORDER BY YT.RevType, MT.MonthNo
********

--
HTH
Van T. Dinh
MVP (Access)

Van,

Thanks for the reply. The things are getting more
complicated. I wanted to make it simple, but in fact I
will need another field for the year. So my table would
look like:

RevType | Year | FromMonth | ToMonth | Rate

Again, in this table I want to have records only for
those types/periods where the rate value differs from 1.

Any ideas would be apreciated.

Thanks again.

Branko

.

 

[Van T. Dinh]

Then it depends on which rate you want to use and structure the SQL
accordingly. You will probably need a set of Queries or a Query with a few
SubQueries. See The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

 

[Branko]

Thanks a lot!

Branko

-----Original Message-----
Then it depends on which rate you want to use and structure the SQL
accordingly. You will probably need a set of Queries or a Query with a few
SubQueries. See The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

--
HTH
Van T. Dinh
MVP (Access)







.