total Query

[Guest]

I’m using a db to track investments. Simplified, there are two tables with a
one to many relationship
Fields included in the first table, tblPrime are:
InvID (primary key)
PurchAmt
FinInst
Security

The second table, tblDetail has following fields:
DateEntry
InvID
InterestAmt
Autonum (primary key)

There will be multiple transactions recorded in the tblDetail that match up
to one record in the tblPrime table by the InvID fields. As interest is
received it will be recorded by DateEntry in the tblDetail.The total worth of
the investment will equal a sum of all the InterestAmt fields added to the
PurchAmt.

I’ve generated a query that will give me the value of all the investments as
of today’s date by first creating a query that sums the InterestAmount
grouped by InvID and then using that query in another with the tblPrime table
joined by InvID. The problem I’m having is that I want to be able to run a
query that will generate a report totaling the worth of the investments on
prior dates. I have a date field to run it against in the tblDetail, but I
can’t figure out then how to sum the InterestAmt results as of that prior
date and join them to InvID in tblPrime. Any help is appreciated.

 

[Guest]

For all investments on which interest has been received on or prior to the
date you should be able to do this with a single query which uses includes a
subquery restricted by a parameter, including the subquery in both the SELECT
and WHERE clauses of the outer query:

PARAMETERS [Enter date:] DATETIME;
SELECT [Enter date:], AS ValueAt, InvID, PurchAmt +
(SELECT SUM(InterestAmt)
FROM tblDetail
WHERE tblDetail.InvID = tblPrime.InvID
AND DateEntry <= [Enter date:]) AS TotalValue
FROM tblPrime
WHERE
(SELECT SUM(InterestAmt)
FROM tblDetail
WHERE tblDetail.InvID = tblPrime.InvID
AND DateEntry <= [Enter date:]) IS NOT NULL;

However, this would exclude any investments entered into on or after the
date parameter but which had not attracted any interest by that date. Such
investments could be returned if there were a column in tblPrime with the
date when the investment was first entered into, but from the information
you've given that doesn't appear to be the case. In the absence of such a
column the best you could do would be to return the value of all investments
by using the NZ function to return zero for those which had not attracted any
interest by that date:

PARAMETERS [Enter date:] DATETIME;
SELECT [Enter date:], AS ValueAt, InvID, PurchAmt +
NZ((SELECT SUM(InterestAmt)
FROM tblDetail
WHERE tblDetail.InvID = tblPrime.InvID
AND DateEntry <= [Enter date:]),0) AS TotalValue
FROM tblPrime;

This, however, would not exclude any investments which might have been
entered into after the date entered as the parameter, for which the value
would be returned as the original purchase amount. If tblPrime does include
a date column (PurchDate say) then the problem is solved because the same
parameter can be applied to the outer query and the subquery:

PARAMETERS [Enter date:] DATETIME;
SELECT [Enter date:], AS ValueAt, InvID, PurchAmt +
NZ((SELECT SUM(InterestAmt)
FROM tblDetail
WHERE tblDetail.InvID = tblPrime.InvID
AND DateEntry <= [Enter date:]),0) AS TotalValue
FROM tblPrime
WHERE PurchDate <= [Enter date:];

Whichever of these you use you can then base another query on the query to
SUM the TotalValue column and give the total value of all investments. Only
the last query of those given above would seem to me to give accurate
figures, however, so it all boils down to there being purchase date column in
tblPrime as I see it.

Ken Sheridan
Stafford, England