Clicky

I am trying to multiply two fields from two different tables.  What would the syntax structure be?
Thanks!

asked 12/15/2011 02:06

submarinerssbn731's gravatar image

submarinerssbn731 ♦♦


27 Answers:
I am working in design view.
link

answered

submarinerssbn731's gravatar image

submarinerssbn731

select [t1].[f1] * [t2].[f2]
from t1 inner join t2
on t1.id=t2.id
link

answered 2011-12-15 at 10:08:37

capricorn1's gravatar image

capricorn1

do you only have one record in each of those tables, or have a particular field that you can use to relate which record to get the data from?

You could do something like:

=DLOOKUP("Field1", "table1", "DetailID = 23") * DLOOKUP("Field2", "table2", "DetailID = 23")

but if either of those lookups fails to find a record with DetailID = 23, it will return NULL and the product of NULL * anything = NULL.  If that is the case, you might want to use something like:

=NZ(DLOOKUP("Field1", "table1", "DetailID = 23"),0) * NZ(DLOOKUP("Field2", "table2", "DetailID = 23"), 0)

link

answered 2011-12-15 at 10:11:49

fyed's gravatar image

fyed

So in design view where do I put the syntax?
link

answered 2011-12-15 at 10:12:20

submarinerssbn731's gravatar image

submarinerssbn731

Where do you want the results? If in a textbox, select the textbox, open the properties window and enter that in the ControlSource propert (including the equal sign).

But if you want to base this calculation on the current record, then your best bet would be to use the Current Event of the form, so that you can capture the values of the "DetailID" or whatever field you are using, and include that in the computation.  In that case, the code might look like:

Private Sub Form_Current

    me.txtProduct = NZ(DLOOKUP("Field1", "table1", "DetailID = " & me.txtDetailID),0) _
                           * NZ(DLOOKUP("Field2", "table2", "DetailID = " & me.txtDetailID), 0)

End Sub
link

answered 2011-12-15 at 10:16:07

fyed's gravatar image

fyed

in a query grid
link

answered 2011-12-15 at 10:20:51

capricorn1's gravatar image

capricorn1

When I attempt to save the Expression it says I need some parens.  Any ideas?
link

answered 2011-12-15 at 10:22:26

submarinerssbn731's gravatar image

submarinerssbn731

sub,

To whom are you replying? and which post?
link

answered 2011-12-15 at 10:29:57

fyed's gravatar image

fyed

Sorry Capricorn1
link

answered 2011-12-15 at 10:36:19

submarinerssbn731's gravatar image

submarinerssbn731

can we see what you are trying to save?

better if you upload  a copy of the db..
link

answered 2011-12-15 at 10:37:28

capricorn1's gravatar image

capricorn1

Not sure we ever got an answer to who are you planning on using this product?  Will this be a single value (one number times another), or do you want a recordset (multiply corresponding values in two fields in different tables and return all records).
link

answered 2011-12-15 at 10:39:53

fyed's gravatar image

fyed

Might be kind of difficult.  One of the DB's involved is 700 Meg.
link

answered 2011-12-15 at 10:40:52

submarinerssbn731's gravatar image

submarinerssbn731

I am working with linked Access databases.
link

answered 2011-12-15 at 10:41:31

submarinerssbn731's gravatar image

submarinerssbn731

again, I ask.  How do you want to use the product of these two values?

It might be better if you would explain what this portion of your application is designed to do, and then let us propose a solution.
link

answered 2011-12-15 at 10:41:52

fyed's gravatar image

fyed

Okay the two fields are Quantity and Price.  I just want to multiply the two fields.
link

answered 2011-12-15 at 10:44:34

submarinerssbn731's gravatar image

submarinerssbn731

just create a sample db with partial info from your linked tables (make all tables local)
link

answered 2011-12-15 at 10:45:56

capricorn1's gravatar image

capricorn1

Here is the expression I am trying to use PSI_ORDERS_DETAILS_TABLE.[Quantity] * BOM.[PRICE]
link

answered 2011-12-15 at 10:46:29

submarinerssbn731's gravatar image

submarinerssbn731

Access excepts the expression but when I run the query it populates all of the rows with PSI_ORDERS_DETAILS_TABLE.[Quantity] * BOM.[PRICE]
link

answered 2011-12-15 at 10:46:51

submarinerssbn731's gravatar image

submarinerssbn731

Not the value.
link

answered 2011-12-15 at 10:47:37

submarinerssbn731's gravatar image

submarinerssbn731

in the query i posted
change t1 and t2 with the name of the tables
f1 with quantity
f2 with Price


link

answered 2011-12-15 at 10:47:57

capricorn1's gravatar image

capricorn1

post the whole thing where you got this
<PSI_ORDERS_DETAILS_TABLE.[Quantity] * BOM.[PRICE] >
link

answered 2011-12-15 at 10:48:04

capricorn1's gravatar image

capricorn1

I tried that capricorn1 but it stated I needed parens for the sub query.
link

answered 2011-12-15 at 10:49:41

submarinerssbn731's gravatar image

submarinerssbn731

Expr1: "PSI_ORDERS_DETAILS_TABLE.[Quantity] * BOM.[PRICE]"
link

answered 2011-12-15 at 10:49:50

submarinerssbn731's gravatar image

submarinerssbn731

remove the " "

Expr1: PSI_ORDERS_DETAILS_TABLE.[Quantity] * BOM.[PRICE]
link

answered 2011-12-15 at 10:50:34

capricorn1's gravatar image

capricorn1

Beautifil!!!:)  Now instead of saying Expr1 in my top row is there a way to name it something else more meaningful?
link

answered 2011-12-15 at 10:53:09

submarinerssbn731's gravatar image

submarinerssbn731

you can replace  "expr1" with anything you want to call it, just overtype the "expr1"
link

answered 2011-12-15 at 11:18:17

capricorn1's gravatar image

capricorn1

Great Answers!  Thanks!
link

answered 2011-12-15 at 11:25:57

submarinerssbn731's gravatar image

submarinerssbn731

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/15/2011 02:06

Seen: 204 times

Last updated: 12/15/2011 03:53