How do I query for past date and totals?

  • Thread starter Thread starter DJTI via AccessMonster.com
  • Start date Start date
D

DJTI via AccessMonster.com

Thanks in advance for any help or guidance.

I am trying to query for a report and am not sure how to go about getting the
last quarters totals. Some accounts will not have last quarters totals,
instead they would have a date and total between the last quarters totals and
current date. How do I do this?
 
I think you would create a query to pull accounts with activity in last
quarter and since then to date. Left join that query in another to your
data table with criteria for the quarter for totals.

If you need more help then post your table structure - field names and
datatype.
 
Karl,
Thanks for the response. The data need for the report is in a query I have
used for the Transaction Ledger Form (SQL is below), I just don't know how to
arrive at the data on my report.


Tables: DataType:

[ShareholderAccount]
ShareholderID Autonumber
ShareholderName text
Address text
Etc.

[Investment]
InvestmentID autonumber
ShareholderID longinteger
PurchaseDate short date
InvestmentAmt currency
PurShareValue currency
CumuShares currency

[InvestmentDetails]
InvDetailID autonumber
InvestmentID longinteger
DateTransaction short date
QDivValue currency
QCashPdValue currency
CurShareValue currency
RedeembyShare currency

SQL:
SELECT [InvestmentDetails].InvDetailID, [InvestmentDetails].InvestmentID,
[InvestmentDetails].DateTransaction, [InvestmentDetails].CurShareValue,
[InvestmentDetails].QDivValue, [InvestmentDetails].QCashPdValue,
[InvestmentDetails].RedeembyShare, [CumuShares]+nz([DivEarned])-nz(
[RedeembyShare]) AS TotalShares, Round([QCashPdValue]*[CumuShares],2) AS
CashPdTotal, Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS
TotalValue, Round([QDivValue]*[CumuShares],2) AS DivEarned, Round(
[RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM Investment INNER JOIN [InvestmentDetails] ON Investment.InvestmentID =
[InvestmentDetails].InvestmentID
WHERE ((([InvestmentDetails].DateTransaction)>#1/1/2006#))
ORDER BY [InvestmentDetails].DateTransaction DESC;

I appreciate your help.
Deb
 
Try this ---
SELECT ShareholderAccount.ShareholderName, ShareholderAccount.Address,
InvestmentDetails.InvDetailID, InvestmentDetails.InvestmentID,
InvestmentDetails.DateTransaction, InvestmentDetails.CurShareValue,
InvestmentDetails.QDivValue, InvestmentDetails.QCashPdValue,
InvestmentDetails.RedeembyShare,
[CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS TotalShares,
Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal,
Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS TotalValue,
Round([QDivValue]*[CumuShares],2) AS DivEarned,
Round([RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM ShareholderAccount LEFT JOIN (Investment LEFT JOIN InvestmentDetails ON
Investment.InvestmentID = InvestmentDetails.InvestmentID) ON
ShareholderAccount.ShareholderID = Investment.ShareholderID
WHERE (((InvestmentDetails.DateTransaction)>#1/1/2006# Or
(InvestmentDetails.DateTransaction) Is Null))
ORDER BY InvestmentDetails.DateTransaction DESC;


DJTI via AccessMonster.com said:
Karl,
Thanks for the response. The data need for the report is in a query I have
used for the Transaction Ledger Form (SQL is below), I just don't know how to
arrive at the data on my report.


Tables: DataType:

[ShareholderAccount]
ShareholderID Autonumber
ShareholderName text
Address text
Etc.

[Investment]
InvestmentID autonumber
ShareholderID longinteger
PurchaseDate short date
InvestmentAmt currency
PurShareValue currency
CumuShares currency

[InvestmentDetails]
InvDetailID autonumber
InvestmentID longinteger
DateTransaction short date
QDivValue currency
QCashPdValue currency
CurShareValue currency
RedeembyShare currency

SQL:
SELECT [InvestmentDetails].InvDetailID, [InvestmentDetails].InvestmentID,
[InvestmentDetails].DateTransaction, [InvestmentDetails].CurShareValue,
[InvestmentDetails].QDivValue, [InvestmentDetails].QCashPdValue,
[InvestmentDetails].RedeembyShare, [CumuShares]+nz([DivEarned])-nz(
[RedeembyShare]) AS TotalShares, Round([QCashPdValue]*[CumuShares],2) AS
CashPdTotal, Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS
TotalValue, Round([QDivValue]*[CumuShares],2) AS DivEarned, Round(
[RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM Investment INNER JOIN [InvestmentDetails] ON Investment.InvestmentID =
[InvestmentDetails].InvestmentID
WHERE ((([InvestmentDetails].DateTransaction)>#1/1/2006#))
ORDER BY [InvestmentDetails].DateTransaction DESC;

I appreciate your help.
Deb

KARL said:
I think you would create a query to pull accounts with activity in last
quarter and since then to date. Left join that query in another to your
data table with criteria for the quarter for totals.

If you need more help then post your table structure - field names and
datatype.
Click to expand...
Click to expand...
 
Karl,
Thanks again for your help. The SQL didn't work, but I did modify it some to
get closer. Please look at this SQL. The results gives me the same date for
last quarter (or entry) as current. I have a major block on what to do.

SELECT TransLedgersub.InvDetailID, TransLedgersub.DateTransaction,
TransLedgersub.CurShareValue, TransLedgersub.QDivValue, TransLedgersub.
QCashPdValue, TransLedgersub.RedeembyShare, TransLedgersub.TotalShares,
TransLedgersub.CashPdTotal, TransLedgersub.TotalValue, TransLedgersub.
DivEarned, TransLedgersub.RedeemedAmt, [Shareholder Account].ShareholderID,
[Shareholder Account].SLName, [Shareholder Account].ShareholderName,
[Shareholder Account].Address, [Shareholder Account].Address2, [Shareholder
Account].City, [Shareholder Account].State, [Shareholder Account].Country,
[Shareholder Account].PostalCode, [Shareholder Account].BrokerID, Investment.
InvestmentID, Investment.PurchaseDate, Investment.InvestmentAmt,
LastTransactionTotalsqry.DateTransactionLast, LastTransactionTotalsqry.
TotalShares
FROM [Shareholder Account] LEFT JOIN ((Investment LEFT JOIN TransLedgersub ON
Investment.InvestmentID = TransLedgersub.InvestmentID) LEFT JOIN
LastTransactionTotalsqry ON Investment.InvestmentID =
LastTransactionTotalsqry.InvestmentID) ON [Shareholder Account].ShareholderID
= Investment.ShareholderID
ORDER BY TransLedgersub.DateTransaction DESC , [Shareholder Account].SLName;

Thanks,
Debra

KARL said:
Try this ---
SELECT ShareholderAccount.ShareholderName, ShareholderAccount.Address,
InvestmentDetails.InvDetailID, InvestmentDetails.InvestmentID,
InvestmentDetails.DateTransaction, InvestmentDetails.CurShareValue,
InvestmentDetails.QDivValue, InvestmentDetails.QCashPdValue,
InvestmentDetails.RedeembyShare,
[CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS TotalShares,
Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal,
Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS TotalValue,
Round([QDivValue]*[CumuShares],2) AS DivEarned,
Round([RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM ShareholderAccount LEFT JOIN (Investment LEFT JOIN InvestmentDetails ON
Investment.InvestmentID = InvestmentDetails.InvestmentID) ON
ShareholderAccount.ShareholderID = Investment.ShareholderID
WHERE (((InvestmentDetails.DateTransaction)>#1/1/2006# Or
(InvestmentDetails.DateTransaction) Is Null))
ORDER BY InvestmentDetails.DateTransaction DESC;
Karl,
Thanks for the response. The data need for the report is in a query I have
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
The Error message was "Syntax error in join operation"

KARL said:
Try this ---
SELECT ShareholderAccount.ShareholderName, ShareholderAccount.Address,
InvestmentDetails.InvDetailID, InvestmentDetails.InvestmentID,
InvestmentDetails.DateTransaction, InvestmentDetails.CurShareValue,
InvestmentDetails.QDivValue, InvestmentDetails.QCashPdValue,
InvestmentDetails.RedeembyShare,
[CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS TotalShares,
Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal,
Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS TotalValue,
Round([QDivValue]*[CumuShares],2) AS DivEarned,
Round([RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM ShareholderAccount LEFT JOIN (Investment LEFT JOIN InvestmentDetails ON
Investment.InvestmentID = InvestmentDetails.InvestmentID) ON
ShareholderAccount.ShareholderID = Investment.ShareholderID
WHERE (((InvestmentDetails.DateTransaction)>#1/1/2006# Or
(InvestmentDetails.DateTransaction) Is Null))
ORDER BY InvestmentDetails.DateTransaction DESC;
Karl,
Thanks for the response. The data need for the report is in a query I have
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
The user will be inputting end of quarter values, the query will run, and a
report will be produced with Shareholder name and address, DateTransaction,
CurShareValue, PreviousDateTransaction, PreviousTotalShares, DivEarned or
CashDivPd, and TotalValue

I am having problems with PreviousDateTransaction and PreviousTotalShares.
All other information are correct.

Thanks,
Deb

KARL said:
Try this ---
SELECT ShareholderAccount.ShareholderName, ShareholderAccount.Address,
InvestmentDetails.InvDetailID, InvestmentDetails.InvestmentID,
InvestmentDetails.DateTransaction, InvestmentDetails.CurShareValue,
InvestmentDetails.QDivValue, InvestmentDetails.QCashPdValue,
InvestmentDetails.RedeembyShare,
[CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS TotalShares,
Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal,
Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS TotalValue,
Round([QDivValue]*[CumuShares],2) AS DivEarned,
Round([RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM ShareholderAccount LEFT JOIN (Investment LEFT JOIN InvestmentDetails ON
Investment.InvestmentID = InvestmentDetails.InvestmentID) ON
ShareholderAccount.ShareholderID = Investment.ShareholderID
WHERE (((InvestmentDetails.DateTransaction)>#1/1/2006# Or
(InvestmentDetails.DateTransaction) Is Null))
ORDER BY InvestmentDetails.DateTransaction DESC;
Karl,
Thanks for the response. The data need for the report is in a query I have
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
Tables: DataType:

[ShareholderAccount]
ShareholderID Autonumber
ShareholderName text
Address text
Etc.
Click to expand...

FWIW in my jurisdiction (UK), shares may be jointly owned by more than
one legal person (human or corporate body). If this is the case for
your jurisdiction it may be worth fixing the design flaw sooner rather
than later.

Jamie.

--
 
This is not the case for this situation.

Jamie said:
Tables: DataType:
Click to expand...
[quoted text clipped - 3 lines]
Address text
Etc.
Click to expand...

FWIW in my jurisdiction (UK), shares may be jointly owned by more than
one legal person (human or corporate body). If this is the case for
your jurisdiction it may be worth fixing the design flaw sooner rather
than later.

Jamie.

--
Click to expand...
 
Can someone please help me with this.
Here is the SQL statement for the report with some revisions. I need
InvestmentDetails.DateTransaction to indicate the previous date. Then the
report will show the previous date and TotalShares along with the current
reports values.
Thanks,
Deb

SELECT [ShareholderAccount].ShareholderID, [ShareholderAccount].SLName,
[ShareholderAccount].ShareholderName, [ShareholderAccount].Address,
[ShareholderAccount].Address2, [ShareholderAccount].City, [ShareholderAccount]
State, [ShareholderAccount].Country, [ShareholderAccount].PostalCode,
Investment.InvestmentID, Investment.PurchaseDate, Investment.InvestmentAmt,
Investment.PurShareValue, Investment.TotalInvValue, Investment.CumuShares,
[InvestmentDetails].InvDetailID, [InvestmentDetails].DateTransaction,
[InvestmentDetails].QDivValue, [InvestmentDetails].QCashPdValue,
[InvestmentDetails].CurShareValue, [InvestmentDetails].RedeembyShare,
LastTransactionDateqry.RecentDateTransaction, [CumuShares]+nz([DivEarned])-nz
([RedeembyShare]) AS TotalShares, Round([QCashPdValue]*[CumuShares],2) AS
CashPdTotal, Round([TotalShares]*[InvestmentDetails].CurShareValue,2) AS
TotalValue, Round([QDivValue]*[CumuShares],2) AS DivEarned, Round(
[RedeembyShare]*[InvestmentDetails].CurShareValue,2) AS RedeemedAmt
FROM [ShareholderAccount] INNER JOIN ((Investment INNER JOIN
LastTransactionDateqry ON Investment.InvestmentID = LastTransactionDateqry.
InvestmentID) INNER JOIN [InvestmentDetails] ON Investment.InvestmentID =
[InvestmentDetails].InvestmentID) ON [ShareholderAccount].ShareholderID =
Investment.ShareholderID
ORDER BY [InvestmentDetails].DateTransaction DESC;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change Sort Order; Assign Number to Specific Records. 1
Where are Mod's defined in databases 1
Problem query 3
The query cannot be completed. 1
Ave function 2
Can a report automatically update from a crosstab query? 2
Complicated Cross tab query for overtime tracking 2
Change expression from month to quarter 7

Back
Top