using DLookup() to obtain a total from a group of records in a related table

[Paul James]

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

 

[Gerald Stanley]

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

 

[Paul James]

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

 

[Paul James]

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

 

[Gerald Stanley]

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

 

[Paul James]

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