Using DLookup on Record ID?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This seems very basic, but I can't do it:

I have a primary table with an autonumber field [Program ID] and a field
called Net_Savings. I have a sub-table [subtblsav] with a field [Savings]
that can have multiple entries linked to each [Program ID] in the main table.
What I want to do (in VBA) is loop through each record in the main table,
use DSum to add up all the [Savings] entries for the current record's
[Program ID], and compare the sum to [Net_Savings] to ensure they're equal.
If they are not equal, I want to update the value of [Net_Savings] to the sum
of the [Savings] for that record.

Any basic suggestions are greatly appreciated. Can you use the record
number in a DLookup function call?

Thanks,

Eric
 
Okay, this seems to work, but it's EXTREMELY slow across the network. Is
there a more efficient approach? "UAI" is the main table, and "subtblsav" is
the many-to-one sub-table.

Set rstAssigns = CurrentDb.OpenRecordset("UAI")
rstAssigns.MoveFirst
Do While Not rstAssigns.EOF
progID = rstAssigns("[Program ID]")
If (Not IsNull(DSum("[Savings]", "subtblsav", "[Savings] Is Not
Null And [Parent ID]=" & progID))) Then
theSum = _
DSum("[Savings]", "subtblsav", "[Savings] Is Not Null
And [Parent ID]=" & progID)
If (theSum <> rstAssigns("[Net Savings / Cost ($K)]")) Then
rstAssigns("[Net Savings / Cost ($K)]") = theSum
End If
End If
rstAssigns.MoveNext
Loop
 
First, it is usually a bad idea to store aggregated data in a table. You
use an aggregate (or totals) query to get the sum when you need it.

You might try something like the following if you absolutely need to store
the calculated data.

One build a work table that has two fields
ProgramId
TotalSavings

Clear the table with
DELETE * FROM WorkTable

Populate that table with
INSERT Into WorkTable (ProgramID, TotalSavings)
SELECT ParentID, Sum(Savings) as TotalSavings
FROM subTblSav
GROUP BY ParentID

Now, use an update query based on the worktable
UPDATE UAI INNER JOIN WorkTable
ON UAI.[ProgramID] = WorkTable.ProgramID
SET [Net Savings / Cost ($K)] = [WorkTable].[TotalSavings]
WHERE UAI.TotalSavings is Null or
UAI.[Net Savings / Cost ($K)] <> WorkTable.TotalSavings

The only problem is that if there are no records in subTblSav for a
ProgramId (parentid) then there will be no record create in the worktable
and therefore no way to update UAI amount to null (or zero). Also, this
would also not update those UAI.ProgramId records where TotalSavings was
calculated as a null value.

You can guarantee the two will be in synch by running two update queries
UPDATE UAI
SET UAI.[Net Savings / Cost ($K)] = Null
Or set it to zero

Then run
UPDATE UAI INNER JOIN WorkTable
ON UAI.[ProgramID] = WorkTable.ProgramID
SET [Net Savings / Cost ($K)] = [WorkTable].[TotalSavings]

A lot of trouble to go to when you could use an aggregate query to calculate
the values when needed.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

Thanks so much for taking the time to reply and educate me. This is a case
of the cart coming before the horse - in the beginning the aggregate number
was all we had, and later we were asked to break that number down into the
details. We do use an aggregate function on our main form to keep the two
data sets in synch, but there have still been instances where some of the
numbers have diverged. I'm just putting together a utility to check to make
sure there aren't any discrepancies in the future.

Your solution is much more elegant than mine! I'll give it a try.

Thanks,

Eric
"noob Access user"
 
Back
Top