How to calculate a field on a non-existent index

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi All,
I want to make a query that will return a list of prices calculated
from a base price and a formula that includes an index, like:

Value = BaseValue + (BaseValue * StepAmount * Index)

Index always runs from 1 to some higher integer value. I thought I
would define a table full of integers, and use that in the query for the
index, but there is no way to link it to the table with the BaseValue and
StepAmount. Also, I figure there has to be a better way than that.

I've been digging around in help, but can find what I need. Can
anyone clue me in?

Thanks, Max
 
Max,

Ok, the Index is an integer being 1 or greater. In any given instance,
how do you decide which Index value to use? If you can tell us how you
decide, someone may be able to help you with persuading Access to agree
with your decision. Or, on the other hand, do you mean that you want
the query to return a list of results for all possible Index values?
 
If I understand correctly, you can use a Cartesian Product (Join) to produce
this. In the Query builder, this is simply a join without the join line.

Assume you have the following tables:
BaseTableValue
ID BaseValue StepAmount
--- ------------ ------------
1 100 2
2 200 3

IndexTable
Indexx
-------
1
2
3
4
5

The following query:
SELECT BaseValueTable.ID, BaseValueTable.BaseValue,
BaseValueTable.StepAmount, [BaseValue]+([BaseValue]*[StepAmount]*[Indexx])
AS [Value]
FROM BaseValueTable, IndexTable
ORDER BY BaseValueTable.ID, IndexTable.Indexx;

Produces the following:
ID BaseValue StepAmount Value
--- ------------ ------------- -------
1 100 2 300
1 100 2 500
1 100 2 700
1 100 2 900
1 100 2 1100
2 200 3 800
2 200 3 1400
2 200 3 2000
2 200 3 2600
2 200 3 3200

(Notice the JOIN has no ON clause. You are simply joining every record in
one table with every record in the other.)
 
If I understand correctly, you can use a Cartesian Product (Join) to
produce this. In the Query builder, this is simply a join without the
join line.
Click to expand...


Hi Steve & Roger,

I am trying to return a list of results for all possible Index
values. Ihave it working with a table of indexes (actually running from 0
to 41). As Roger suggested, I just showed the table with no join line and
it worked.
It just seemed there had to be a way to do this without defining a
table of integer numbers.

On a related subject, I'm trying to limit the list by using the
following expression in the criteria field of the query:

=IIf([tblItems].[OpenEnded]=True,[tblAmountIndexes].[AmountIndex]<42,
[tblAmountIndexes].[AmountIndex]<17)

If I pull this out of the criteria field, the query works, returning
all 42 values. It doesn't return anything with this in. What stupid thing
did I do?

I appreciate the help, Max
 
Max,

Whereas I have seen an IIf() expression used in a query criteria, I have
never tried it myself. I would prefer to put two criteria lines in the
query, the first line with -1 in the criteria for the OpenEnded field,
and in the next line, 0 in the criteria for the OpenEnded field, and <17
in the criteria of the AmountIndex field.
 
It just seemed there had to be a way to do this without defining a
table of integer numbers.
Click to expand...

I find so many uses for such a table that I now routinely put it in
every new database - named Num, one field N, values 1 to 10000.
On a related subject, I'm trying to limit the list by using the
following expression in the criteria field of the query:

=IIf([tblItems].[OpenEnded]=True,[tblAmountIndexes].[AmountIndex]<42,
[tblAmountIndexes].[AmountIndex]<17)

If I pull this out of the criteria field, the query works, returning
all 42 values. It doesn't return anything with this in. What stupid thing
did I do?
Click to expand...

Well, it's not stupid - just not knowing Microsoft's conventions. You
can't pass a full WHERE clause or an operator such as < in a parameter
like this, just a value. Try

[tblAmountIndexes].[AmountIndex] < IIF([OpenEnded], 42, 17)

Note that OpenEnded is already either TRUE or FALSE so you don't need
to compare it to True, and if it's not ambiguous, you don't need the
table qualification either (though it won't hurt to have it).
 
like this, just a value. Try

[tblAmountIndexes].[AmountIndex] < IIF([OpenEnded], 42, 17)

Note that OpenEnded is already either TRUE or FALSE so you don't need
to compare it to True, and if it's not ambiguous, you don't need the
table qualification either (though it won't hurt to have it).
Click to expand...


Thanks, John. I'll give it a whirl. - Max
 
Back
Top