Multiply field value of one table with cell value of another table

[Guest]

Is it possible to retrieve a query result which multiply a field value of one
table with a cell value (not field) of another table? I tried and search a
lot but nothing shows me the way to accomplish the task. Any suggestions
would be appreciated!!
Thaks in advance!!

 

[Rick Brandt]

Is it possible to retrieve a query result which multiply a field
value of one table with a cell value (not field) of another table? I
tried and search a lot but nothing shows me the way to accomplish the
task. Any suggestions would be appreciated!!
Thaks in advance!!

Since tables don't have "cells" you will have to clarify what you want. If you
mean you want to multiply by the value of a particular field in a particular row
of a table then you could use DLookup() to retrieve that value, but I would
avoid using DLookup() in a query unless there is no other way. Depending on how
the query is strucytured the DLookup() might be executed only once or for every
row in your other table (which would be slow).

 

[David F Cox]

I did not understand what you meant by "cell" value.

Is this "cell" in an Excel spreadsheet, or are you referring to something in
a datasheet view.

If it something in datasheet view then it is still called a field, whether
it is a field in a table or a field in a report.

In query design window you can put things like:

cost: [table1.quantity] * [table2.price]

 

[Guest]

Thank you Rick and David for the replies...you both r right, but I guess I
should clarify again what I want:

I'm designing a complex database which consists of about 20 basic tables and
each of them with about 14000 records and 10 fields. Using the query
expressions (translating original excel formulas into access queries), so far
I have derived couple of more queries. Now want to derive another query which
would be the result of a basic table and a query. but there is no common
field in them.
here r the table and the query:

Table (ID, Value)
1, 12
2, 14
3, 17
.......
10, 25
etc.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...
1002, 21, 47, 78, ...
1003, 30, 20, 98, ...
etc.

Now I want to build a query expression which will result like:

Query Result (District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25
1002, 21*12, 47*14, 78*25
1003, 30*12, 20*14, 98*25

I hope now you can understand what I want. Is it possible?

Rick---> if it is possible to perform the query result with DLookup()
function, could you please let me know the code/expression?

David--> cost: [table1.quantity] * [table2.price] multiply two fields of two
tables. Is there any simple expression to get above result?

Thanks a lot for your attention!!

Monjur

 

[John Vinson]

Thank you Rick and David for the replies...you both r right, but I guess I
should clarify again what I want:

I'm designing a complex database which consists of about 20 basic tables and
each of them with about 14000 records and 10 fields.

If you have 20 pretty-much identical tables, then your table
normalization is almost certainly wrong. What differentiates these
tables from one another? Is each table a set of data about a
particular District, or a particular Type, or what?

Using the query
expressions (translating original excel formulas into access queries), so far
I have derived couple of more queries. Now want to derive another query which
would be the result of a basic table and a query. but there is no common
field in them.

Then there is NO way - even in principle - to link them. How on Earth
can Access - or you, or me, or anyone - determine which of the 14000
records in Table13 relates to District 1003? Unless you have some
information in the table to provide that link it simply cannot be
done.

here r the table and the query:

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25
etc.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...
1002, 21, 47, 78, ...
1003, 30, 20, 98, ...
etc.

Now I want to build a query expression which will result like:

Query Result (District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25
1002, 21*12, 47*14, 78*25
1003, 30*12, 20*14, 98*25

I hope now you can understand what I want. Is it possible?

No, I do not understand what you want. The description of the tables
appears to have no discernible connection to the result you post.

John W. Vinson[MVP]

 

[David F Cox]

Quite often it is not that we cannot get the answer right, it is that we
cannot get the question right.

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25

This looks like a lookup table. This I like.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...

This looks like a query with fields, still I am happy

Now I want to build a query expression which will result like:

District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25

Now if the last calculation had read 87*17 I would be on my way to ecstacy.
Of course the customer is always right, but, nevertheless, if that is a
typo, and the digit after Typ is the same as the ID in your lookup table we
could have:

SELECT District, [Typ1]*12, AS Typ1val, [Typ2]*14 AS Typ2val, etc

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12
etc

if 87*25 is not a typo you will have to tell us how you know to multiply 85
by the 10th Value in the lookup table.

Thank you Rick and David for the replies...you both r right, but I guess I
should clarify again what I want:

I'm designing a complex database which consists of about 20 basic tables
and
each of them with about 14000 records and 10 fields. Using the query
expressions (translating original excel formulas into access queries), so
far
I have derived couple of more queries. Now want to derive another query
which
would be the result of a basic table and a query. but there is no common
field in them.
here r the table and the query:

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25
etc.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...
1002, 21, 47, 78, ...
1003, 30, 20, 98, ...
etc.

Now I want to build a query expression which will result like:

Query Result (District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25
1002, 21*12, 47*14, 78*25
1003, 30*12, 20*14, 98*25

I hope now you can understand what I want. Is it possible?

Rick---> if it is possible to perform the query result with DLookup()
function, could you please let me know the code/expression?

David--> cost: [table1.quantity] * [table2.price] multiply two fields of
two
tables. Is there any simple expression to get above result?

Thanks a lot for your attention!!

Monjur

 

[Guest]

Thank you again for your reply.
You are absolutely right with the look up table, query and the query result.
I have already done what you have mentioned, ie.,

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12

But the client wants something more dynamic: other than directly putting the
value "12" in the query expression, he wants to refer that value (like in
excel) of the look up table so that if any value is altered in the look up
table, query result would be automatically updated.

Is it possible?

Thanks David, once again!!

Quite often it is not that we cannot get the answer right, it is that we
cannot get the question right.

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25

This looks like a lookup table. This I like.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...

This looks like a query with fields, still I am happy

Now I want to build a query expression which will result like:

District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25

Now if the last calculation had read 87*17 I would be on my way to ecstacy.
Of course the customer is always right, but, nevertheless, if that is a
typo, and the digit after Typ is the same as the ID in your lookup table we
could have:

SELECT District, [Typ1]*12, AS Typ1val, [Typ2]*14 AS Typ2val, etc

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12
etc

if 87*25 is not a typo you will have to tell us how you know to multiply 85
by the 10th Value in the lookup table.

Thank you Rick and David for the replies...you both r right, but I guess I
should clarify again what I want:

I'm designing a complex database which consists of about 20 basic tables
and
each of them with about 14000 records and 10 fields. Using the query
expressions (translating original excel formulas into access queries), so
far
I have derived couple of more queries. Now want to derive another query
which
would be the result of a basic table and a query. but there is no common
field in them.
here r the table and the query:

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25
etc.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...
1002, 21, 47, 78, ...
1003, 30, 20, 98, ...
etc.

Now I want to build a query expression which will result like:

Query Result (District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25
1002, 21*12, 47*14, 78*25
1003, 30*12, 20*14, 98*25

I hope now you can understand what I want. Is it possible?

Rick---> if it is possible to perform the query result with DLookup()
function, could you please let me know the code/expression?

David--> cost: [table1.quantity] * [table2.price] multiply two fields of
two
tables. Is there any simple expression to get above result?

Thanks a lot for your attention!!

Monjur

Is it possible to retrieve a query result which multiply a field value
of
one
table with a cell value (not field) of another table? I tried and
search a
lot but nothing shows me the way to accomplish the task. Any
suggestions
would be appreciated!!
Thaks in advance!!

 

[David F Cox]

This time the customer is right. It is often bad practise to code constants
into queries. The world is not built like that.

Here it does not seem to me to be a case for a lookup table. Field Typ1
seems to be always related to the first number in your "lookup", Typ2 to the
second, etc. You have a one-to-one correspondence between fields.. I have
seen no hint of a lookup value, except the field identifier. correct me if I
am wrong.

If this is true the solution is along the lines of having a Tbl_mutiplier
with each records containing fields Typ1, Typ2, etc

There could just be a single record, or if a history of changes is required
several records, with only one being active. This would allow the system to
develop into having different multiplier tables for different conditions.

The multipliers in this table can be modified via a Form

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12

becomes:

Typ1val: [Typ1]*[multiplier.Typ1] Typ2val: [Typ2]*[multiplier.Typ2]
etc

Thank you again for your reply.
You are absolutely right with the look up table, query and the query
result.
I have already done what you have mentioned, ie.,

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12

But the client wants something more dynamic: other than directly putting
the
value "12" in the query expression, he wants to refer that value (like in
excel) of the look up table so that if any value is altered in the look up
table, query result would be automatically updated.

Is it possible?

Thanks David, once again!!

Quite often it is not that we cannot get the answer right, it is that we
cannot get the question right.

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25

This looks like a lookup table. This I like.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...

This looks like a query with fields, still I am happy

Now I want to build a query expression which will result like:

District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25

Now if the last calculation had read 87*17 I would be on my way to
ecstacy.
Of course the customer is always right, but, nevertheless, if that is a
typo, and the digit after Typ is the same as the ID in your lookup table
we
could have:

SELECT District, [Typ1]*12, AS Typ1val, [Typ2]*14 AS Typ2val, etc

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12
etc

if 87*25 is not a typo you will have to tell us how you know to multiply
85
by the 10th Value in the lookup table.

Thank you Rick and David for the replies...you both r right, but I
guess I
should clarify again what I want:

I'm designing a complex database which consists of about 20 basic
tables
and
each of them with about 14000 records and 10 fields. Using the query
expressions (translating original excel formulas into access queries),
so
far
I have derived couple of more queries. Now want to derive another query
which
would be the result of a basic table and a query. but there is no
common
field in them.
here r the table and the query:

Table (ID, Value)
1, 12
2, 14
3, 17
......
10, 25
etc.

Query (District, Typ1, Typ2, Typ3, .........)
1001, 12, 45, 87, ...
1002, 21, 47, 78, ...
1003, 30, 20, 98, ...
etc.

Now I want to build a query expression which will result like:

Query Result (District, Typ1Val, Typ2Val, Typ3Val ..........)
1001, 12*12, 45*14, 87*25
1002, 21*12, 47*14, 78*25
1003, 30*12, 20*14, 98*25

I hope now you can understand what I want. Is it possible?

Rick---> if it is possible to perform the query result with DLookup()
function, could you please let me know the code/expression?

David--> cost: [table1.quantity] * [table2.price] multiply two fields
of
two
tables. Is there any simple expression to get above result?

Thanks a lot for your attention!!

Monjur


Is it possible to retrieve a query result which multiply a field
value
of
one
table with a cell value (not field) of another table? I tried and
search a
lot but nothing shows me the way to accomplish the task. Any
suggestions
would be appreciated!!
Thaks in advance!!

 

[Guest]

Hi David,

Please find my comments and questions below:

This time the customer is right. It is often bad practise to code constants
into queries. The world is not built like that.

Here it does not seem to me to be a case for a lookup table. Field Typ1
seems to be always related to the first number in your "lookup", Typ2 to the
second, etc. You have a one-to-one correspondence between fields.. I have
seen no hint of a lookup value, except the field identifier. correct me if I
am wrong.

Well, it's not always like this.....TypX is not always related to the ValX.
So, I have to specify in the Query expression which "Typ" would be multiplied
by which "Val".

If this is true the solution is along the lines of having a Tbl_mutiplier
with each records containing fields Typ1, Typ2, etc

There could just be a single record, or if a history of changes is required
several records, with only one being active. This would allow the system to
develop into having different multiplier tables for different conditions.

The multipliers in this table can be modified via a Form

I'm sorry, I could not catch the idea u have mentioned. Should I construct
another table called "Multiplier" with the fields Typ1, Typ2, etc? Could you
please explain a bit.

entered in the field "cell" on the design grid as:
Typ1val: [Typ1]*12

becomes:

Typ1val: [Typ1]*[multiplier.Typ1] Typ2val: [Typ2]*[multiplier.Typ2]
etc

Infact, I am looking for something like this..but I'm afraid how it works!!

I'm sorry to put u into trouble again. ur help is highly appreciated!!

Thanks