455,658 Members | 1,636 Online
Need help? Post your question and get tips & solutions from a community of 455,658 IT Pros & Developers. It's quick & easy.

# Help with formula combining query and table

 100+ P: 418 I have a subform that is based on a query. I want a text box in this form which will give me a calculated figure. what I want to say is this take the FedRatio from tblGrants and multiply Amount here. The query is based on tables other than tblGrants. Is it possible to do this? I know the simple calcualtion should be =[FedRatio] * [Amount] but how do I incorporate FedRatio from tblGrants? Any help is appreciated. Thanks. May 7 '09 #1
8 Replies

 100+ P: 579 What is the record source for the subform? There might be a better way to do this, and of course that depends on what your answer to my question is, but here's what I think you need to do. If the record source for the subform is tblGrants, add an invisible textbox that has the control source of FedRatio and then use that in your expression. May 7 '09 #2

 100+ P: 418 The record source for the sub form is a query that's based on several table but NOT the tblGrants. And tblGrant is the one that has the FedRatio. I am thinking since I need the calculation on a report, I may use tblGrant as one of the source plus the query. Let's see if it works. Thanks. May 7 '09 #3

 Expert 100+ P: 1,356 Which form are we talking about? Is the main form or the subform based on a query/table that has the GrantID in it? If so then you can add the Grant table in that query and pull the results that you are looking for. You could also use the Dlookup("FedRatio ","tblGrants","GrantID=" & me!cboGrants) However it would be better if you were able to use the first of the two methods since Dlookup can slow things down. May 7 '09 #4

 Expert Mod 15k+ P: 31,712 As Den says M, if there is a logical link between the query data and the particular record you need in tblGrants, then you surely need a query that includes tblGrants as a JOINed table in the FROM clause. Domain Aggregate (DLookup() etc) functions can be used instead, but as Den so rightly says again, this is not to be advised unless unavoidable. May 7 '09 #5

 100+ P: 418 Denburt: Happy Monday! What I tried to do is to create a report instead. This report is based on a query and a tblGrants. But I get no data - meaning blank page. When I delete tblGrants, it works. But tblGrants is the table which contains the FedRatio that I will need to calculate the amount to charge. What are my choices to resolve this matter. Thanks. May 11 '09 #6

 Expert 100+ P: 1,356 We are still where we were when I asked about the logical link between the query and tblGrants what tables are in the query? Can you include tblGrants in that query (as Neo suggested)? Then you can create the form or report. Oh n Happy Monday to you too. May 11 '09 #7

 100+ P: 418 Denburt: If you can believe it - somehow it's working now without my doing anything...Go figure. Oh well, your patience is much appreciated. Until my next question. Thanks again. May 11 '09 #8

 Expert 100+ P: 1,356 That's great that it is working now. Glad we could be of help. May 11 '09 #9