If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using DLookup() to obtain a total from a group of records in a related table
I've got two related tables - tblInvoice and tblDetail which are related by
InvoiceID fields in each table. The Amount field is in tblDetail. I've got a query containing fields from tblInvoice, and I'd like to add a calculated field called "Total" to the query that will display the total of the corresponding Amount fields in the related table. I'm thinking the best way to do this is with the DLookup() function: DLookup("[Amount]", "tblDetails", . . . . . .) but I'm not sure what to put in the third argument of the function. Two questions: 1. Am I right in thinking DLookup is the best way to do this, and 2. can anyone tell me what expression I can put in the third argument above to obtain a Total of the Amount field in tblDetail? Thanks in advance, Paul |
#2
|
|||
|
|||
using DLookup() to obtain a total from a group of records in a related table
You should consider writing a standard Summation query
rather than using DSum (not DLookUp). It would be something along the lines of SELECT I.col1, I.col2, ..... , I.colN, SUM(D.amount) AS total FROM tblInvoice AS I INNER JOIN tblDetail AS D ON I.invoiceId = D.invoiceId GROUP BY I.col1, I.col2, ..... , I.colN I.col1 to I.colN represent the columns that you are taking from the table tblInvoice Hope This Helps Gerald Stanley MCSD -----Original Message----- I've got two related tables - tblInvoice and tblDetail which are related by InvoiceID fields in each table. The Amount field is in tblDetail. I've got a query containing fields from tblInvoice, and I'd like to add a calculated field called "Total" to the query that will display the total of the corresponding Amount fields in the related table. I'm thinking the best way to do this is with the DLookup() function: DLookup("[Amount]", "tblDetails", . . . . . .) but I'm not sure what to put in the third argument of the function. Two questions: 1. Am I right in thinking DLookup is the best way to do this, and 2. can anyone tell me what expression I can put in the third argument above to obtain a Total of the Amount field in tblDetail? Thanks in advance, Paul . |
#3
|
|||
|
|||
using DLookup() to obtain a total from a group of records in a related table
Gerald - you're right - it worked!
Thank you so much. This is another vivid reminder for me of how useful SQL can be, if you understand it. Can you recommend any good sources for learning it - either books or web sites? Paul |
#4
|
|||
|
|||
Oops - a problem - the recordset isn't updateable.
The query works fine for display purposes, but I can't enter any data to the
recordset. I tried changing the Recordset Type property to Dynaset (Inconsistent Updates) but I still can't update the recordset. It would seem that updating the data should be possible, because other than that single calculated field (Total), all the rest of the fields are from a single table, so there's no ambiguity about which record I'm trying to change. Nevertheless, the query won't let me make any changes to the data. Is there anything I can do to make this recordset updateable? If I can't do this with a SQL statement, can anyone tell me what that last argument would be in the DSum() function? (Please refer to my original question for details about the expression, domain and criteria). Thanks again in advance, Paul |
#5
|
|||
|
|||
Oops - a problem - the recordset isn't updateable.
In my experience, JET will not allow a recordset generated
from a summation query to be updateable. Neither can you use a Domain function such as DSum in SQL as they are not recognised by JET. If you were operating this through a form, then the ControlSource for the form would a query based on tblInvoice only or the table itself. You could have a text box on the form to show the total, but to populate it, you would need to put code in the Form's Current eventhandler along the lines of txtTotal.Value = DSum('amount' , tblDetail , invoiceId = " & txtInvoiceId.Value & ")" where txtTotal is the text box to display the total and txtInvoiceId is the textbox bound to the invoiceId column. Hope This Helps Gerald Stanley MCSD -----Original Message----- The query works fine for display purposes, but I can't enter any data to the recordset. I tried changing the Recordset Type property to Dynaset (Inconsistent Updates) but I still can't update the recordset. It would seem that updating the data should be possible, because other than that single calculated field (Total), all the rest of the fields are from a single table, so there's no ambiguity about which record I'm trying to change. Nevertheless, the query won't let me make any changes to the data. Is there anything I can do to make this recordset updateable? If I can't do this with a SQL statement, can anyone tell me what that last argument would be in the DSum() function? (Please refer to my original question for details about the expression, domain and criteria). Thanks again in advance, Paul . |
#6
|
|||
|
|||
Oops - a problem - the recordset isn't updateable.
Gerald - thanks for your recent reply.
In this case I'm not building an expression in a form control, but rather I'm putting it directly in the query. The expression I finally got to work was: DSum("[Amount]","[tblDetail]","Invoice_ID=" & [InvoiceID]) What I had been missing was the need to concatenate the two parts of the criteria expression. I notice that your expression for a form control also uses the concatenation operator. Before I saw your last message, I got the explanation for how to create the expression in a query from Allen Brown's web site at http://members.iinet.net.au/~allenbrowne/casu-07.html On that site, he's got the most lucid explanation I've ever seen of how to construct the syntax of a domain aggregate function. On that page, he uses the particular example of the DLookup() function, but the syntax is of course the same for the the DSum() function. And thanks again for the SQL code for the total query. Although I won't use it in this case since the total queries aren't updateable, I'll be able to use it for other purposes. Paul |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help Needed for Groups Please | Paul Black | General Discussion | 15 | June 21st, 2004 02:54 AM |
Help with table design and relationships | Richard Wright | Database Design | 3 | June 20th, 2004 03:49 PM |
Keeping Records in Linking Table Accurate (Long Post) | Ben Johnson | Running & Setting Up Queries | 0 | June 14th, 2004 08:13 AM |
Show Records Where All Related Records Are False | Cliff | Running & Setting Up Queries | 1 | June 4th, 2004 08:10 PM |
Table design | BillT | New Users | 11 | May 25th, 2004 03:41 PM |