Populate query with records for eac number from 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,


But I would work with full date (year, month, day), not just month... it
just make date comparison far more easier.


So, the taxRate (fieldname) to pick up is the one with its BeginAt
(fieldname) less or equal to the BillingDate (argument), but among all
those, it is the one having the maximum BeginAt value.


SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]


returns that date, so

SELECT taxRate
FROM Taxes
WHERE BeginAt= ( SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]
)


supplied you with the taxRate. You do not need to store the "end of period",
since, first, you generally do not know it for the actual tax rate, and,
more importantly, it is clearly UP TO the next BeginAt available in the
table.


Hoping it may help,
Vanderghast, Access MVP

 

[Branko]

Michel,

Thanks for the reply. I need to store "end of period"
too, since rates are entered only for some of the types.
It is not clear where the BillinDate argument comes from.

Branko

-----Original Message-----
Hi,


But I would work with full date (year, month, day), not just month... it
just make date comparison far more easier.


So, the taxRate (fieldname) to pick up is the one with its BeginAt
(fieldname) less or equal to the BillingDate (argument), but among all
those, it is the one having the maximum BeginAt value.


SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]


returns that date, so

SELECT taxRate
FROM Taxes
WHERE BeginAt= ( SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]
)


supplied you with the taxRate. You do not need to store the "end of period",
since, first, you generally do not know it for the actual tax rate, and,
more importantly, it is clearly UP TO the next BeginAt available in the
table.


Hoping it may help,
Vanderghast, Access MVP

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,

Indeed, it is not clear, that is why I make it a "parameter". Basically,
if you need to check/compute something as if the date to consider for that
computation was "BillingDate", then supply BillingDate by that value.
Example, if you want the taxRate as it was the 2001-10-10, then specify
#10-10-2001# for the "billing date" parameter and the query pumps out the
tax rate that was right at that time.


If you need and use a UpTo date, I call the field EndingAt, the query is
even easier:

SELECT taxRate
FROM myTable
WHERE BillingDate BETWEEN BeginAt AND EndingAt


I though the simple queries would convince you that you do NOT need
ANOTHER table... just use the query, instead!



Hoping it may help,
Vanderghast, Access MVP

Michel,

Thanks for the reply. I need to store "end of period"
too, since rates are entered only for some of the types.
It is not clear where the BillinDate argument comes from.

Branko

-----Original Message-----
Hi,


But I would work with full date (year, month, day), not just month... it
just make date comparison far more easier.


So, the taxRate (fieldname) to pick up is the one with its BeginAt
(fieldname) less or equal to the BillingDate (argument), but among all
those, it is the one having the maximum BeginAt value.


SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]


returns that date, so

SELECT taxRate
FROM Taxes
WHERE BeginAt= ( SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]
)


supplied you with the taxRate. You do not need to store the "end of period",
since, first, you generally do not know it for the actual tax rate, and,
more importantly, it is clearly UP TO the next BeginAt available in the
table.


Hoping it may help,
Vanderghast, Access MVP

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.

.

 

[Guest]

Michel,

I feel bad now. You've really been working on this issue.
Thanks a lot.

Unfortunately, it is not that easy. Right now, I have a
table in my database that contains a record for each
[Month] in a [Year] (year can be last, this, next) where
[Rate] for the [Type] differs from 1 (one). That table
looks exactly like this:

Type | Year | Month | Rate
-----------------------------
1 2002 3 1.25
1 2002 4 1.25
1 2002 5 1.25
1 2003 6 1.05
1 2003 7 1.05
2 2002 2 1.3
2 2002 3 1.3
2 2002 4 1.3
....

To avoid entering all this data, I wanted a query that
would be automatically populated like in the table above,
based on the information (periods) given in the table
like this:

Type | Year | From | To | Rate
------------------------------
1 2002 3 5 1.25
1 2003 6 7 1.05
2 2003 2 3 1.3

So I need a record in my query for each number in a given
period ([From] - [To]).

Branko

-----Original Message-----
Hi,

Indeed, it is not clear, that is why I make it a "parameter". Basically,
if you need to check/compute something as if the date to consider for that
computation was "BillingDate", then supply BillingDate by that value.
Example, if you want the taxRate as it was the 2001-10- 10, then specify
#10-10-2001# for the "billing date" parameter and the query pumps out the
tax rate that was right at that time.


If you need and use a UpTo date, I call the field EndingAt, the query is
even easier:

SELECT taxRate
FROM myTable
WHERE BillingDate BETWEEN BeginAt AND EndingAt


I though the simple queries would convince you that you do NOT need
ANOTHER table... just use the query, instead!



Hoping it may help,
Vanderghast, Access MVP

Michel,

Thanks for the reply. I need to store "end of period"
too, since rates are entered only for some of the types.
It is not clear where the BillinDate argument comes from.

Branko

-----Original Message-----
Hi,


But I would work with full date (year, month,

day),
not just month... it

just make date comparison far more easier.


So, the taxRate (fieldname) to pick up is the one with its BeginAt
(fieldname) less or equal to the BillingDate

(argument),
but among all

those, it is the one having the maximum BeginAt value.


SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]


returns that date, so

SELECT taxRate
FROM Taxes
WHERE BeginAt= ( SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]
)


supplied you with the taxRate. You do not need to

store
the "end of period",

since, first, you generally do not know it for the actual tax rate, and,
more importantly, it is clearly UP TO the next BeginAt available in the
table.


Hoping it may help,
Vanderghast, Access MVP



"Branko" <[email protected]> wrote

in
message

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,


My testing table name was Table8 :



SELECT Last(type) AS AboutType,
Min(DateSerial([year],[month],1)) AS Starting,
Max(DateSerial([year],[month],1)) AS Ending,
Last(rate) AS RateForThisPeriod

FROM table8

GROUP BY DCount("*","Table8","DateSerial([year], [month], 1) >=" &
DateSerial([year],[month],1) & " AND type=" & [type] & " AND rate=" &
[rate]);




Hoping it may help,
Vanderghast, Access MVP

Michel,

I feel bad now. You've really been working on this issue.
Thanks a lot.

Unfortunately, it is not that easy. Right now, I have a
table in my database that contains a record for each
[Month] in a [Year] (year can be last, this, next) where
[Rate] for the [Type] differs from 1 (one). That table
looks exactly like this:

Type | Year | Month | Rate
-----------------------------
1 2002 3 1.25
1 2002 4 1.25
1 2002 5 1.25
1 2003 6 1.05
1 2003 7 1.05
2 2002 2 1.3
2 2002 3 1.3
2 2002 4 1.3
...

To avoid entering all this data, I wanted a query that
would be automatically populated like in the table above,
based on the information (periods) given in the table
like this:

Type | Year | From | To | Rate
------------------------------
1 2002 3 5 1.25
1 2003 6 7 1.05
2 2003 2 3 1.3

So I need a record in my query for each number in a given
period ([From] - [To]).

Branko

-----Original Message-----
Hi,

Indeed, it is not clear, that is why I make it a "parameter". Basically,
if you need to check/compute something as if the date to consider for that
computation was "BillingDate", then supply BillingDate by that value.
Example, if you want the taxRate as it was the 2001-10- 10, then specify
#10-10-2001# for the "billing date" parameter and the query pumps out the
tax rate that was right at that time.


If you need and use a UpTo date, I call the field EndingAt, the query is
even easier:

SELECT taxRate
FROM myTable
WHERE BillingDate BETWEEN BeginAt AND EndingAt


I though the simple queries would convince you that you do NOT need
ANOTHER table... just use the query, instead!



Hoping it may help,
Vanderghast, Access MVP

Michel,

Thanks for the reply. I need to store "end of period"
too, since rates are entered only for some of the types.
It is not clear where the BillinDate argument comes from.

Branko

-----Original Message-----
Hi,


But I would work with full date (year, month, day),
not just month... it
just make date comparison far more easier.


So, the taxRate (fieldname) to pick up is the one with
its BeginAt
(fieldname) less or equal to the BillingDate (argument),
but among all
those, it is the one having the maximum BeginAt value.


SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]


returns that date, so

SELECT taxRate
FROM Taxes
WHERE BeginAt= ( SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <=
[BillingDate]
)


supplied you with the taxRate. You do not need to store
the "end of period",
since, first, you generally do not know it for the
actual tax rate, and,
more importantly, it is clearly UP TO the next BeginAt
available in the
table.


Hoping it may help,
Vanderghast, Access MVP



message
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,


Make it, instead:

SELECT type,
Min(DateSerial([year],[month],1)) AS Starting,
Max(DateSerial([year],[month],1)) AS Ending,
rate
FROM table8

GROUP BY type, rate, DCount("*","Table8","DateSerial([year], [month], 1)

=" &

DateSerial([year],[month],1) & " AND type=" & [type] & " AND rate=" &
[rate]);




Hoping it may help,
Vanderghast, Access MVP

Hi,


My testing table name was Table8 :



SELECT Last(type) AS AboutType,
Min(DateSerial([year],[month],1)) AS Starting,
Max(DateSerial([year],[month],1)) AS Ending,
Last(rate) AS RateForThisPeriod

FROM table8

GROUP BY DCount("*","Table8","DateSerial([year], [month], 1) >=" &
DateSerial([year],[month],1) & " AND type=" & [type] & " AND rate=" &
[rate]);




Hoping it may help,
Vanderghast, Access MVP

Michel,

I feel bad now. You've really been working on this issue.
Thanks a lot.

Unfortunately, it is not that easy. Right now, I have a
table in my database that contains a record for each
[Month] in a [Year] (year can be last, this, next) where
[Rate] for the [Type] differs from 1 (one). That table
looks exactly like this:

Type | Year | Month | Rate
-----------------------------
1 2002 3 1.25
1 2002 4 1.25
1 2002 5 1.25
1 2003 6 1.05
1 2003 7 1.05
2 2002 2 1.3
2 2002 3 1.3
2 2002 4 1.3
...

To avoid entering all this data, I wanted a query that
would be automatically populated like in the table above,
based on the information (periods) given in the table
like this:

Type | Year | From | To | Rate
------------------------------
1 2002 3 5 1.25
1 2003 6 7 1.05
2 2003 2 3 1.3

So I need a record in my query for each number in a given
period ([From] - [To]).

Branko

-----Original Message-----
Hi,

Indeed, it is not clear, that is why I make it a "parameter". Basically,
if you need to check/compute something as if the date to consider for that
computation was "BillingDate", then supply BillingDate by that value.
Example, if you want the taxRate as it was the 2001-10- 10, then specify
#10-10-2001# for the "billing date" parameter and the query pumps out the
tax rate that was right at that time.


If you need and use a UpTo date, I call the field EndingAt, the query is
even easier:

SELECT taxRate
FROM myTable
WHERE BillingDate BETWEEN BeginAt AND EndingAt


I though the simple queries would convince you that you do NOT need
ANOTHER table... just use the query, instead!



Hoping it may help,
Vanderghast, Access MVP




Michel,

Thanks for the reply. I need to store "end of period"
too, since rates are entered only for some of the types.
It is not clear where the BillinDate argument comes from.

Branko

-----Original Message-----
Hi,


But I would work with full date (year, month, day),
not just month... it
just make date comparison far more easier.


So, the taxRate (fieldname) to pick up is the one with
its BeginAt
(fieldname) less or equal to the BillingDate (argument),
but among all
those, it is the one having the maximum BeginAt value.


SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <= [BillingDate]


returns that date, so

SELECT taxRate
FROM Taxes
WHERE BeginAt= ( SELECT MAX(BeginAt)
FROM Taxes
WHERE BeginAt <=
[BillingDate]
)


supplied you with the taxRate. You do not need to store
the "end of period",
since, first, you generally do not know it for the
actual tax rate, and,
more importantly, it is clearly UP TO the next BeginAt
available in the
table.


Hoping it may help,
Vanderghast, Access MVP



message
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.



.



.