query for Max date and previous date for report

[DJTI via AccessMonster.com]

I am stuck on a subform and need help or guidance. If I am going the wrong
direction, someone help aim me in the right direction.

The report needs the MaxDate and the Date previous to the Max date so that
the Client can see the date and balance from the last transaction, the
activity and balance from the current transaction. The query is based on a
subform.

The SQL for the query is below:

SELECT TOP 5 Max(TransLedgersub.DateTransaction) AS MaxOfDateTransaction,
TransLedgersub.InvDetailID, TransLedgersub.InvestmentID, TransLedgersub.
CurShareValue, TransLedgersub.QDivValue, TransLedgersub.QCashPdValue,
TransLedgersub.RedeembyShare, TransLedgersub.TotalShares, TransLedgersub.
CashPdTotal, TransLedgersub.TotalValue, TransLedgersub.DivEarned,
TransLedgersub.RedeemedAmt
FROM TransLedgersub
GROUP BY TransLedgersub.InvDetailID, TransLedgersub.InvestmentID,
TransLedgersub.CurShareValue, TransLedgersub.QDivValue, TransLedgersub.
QCashPdValue, TransLedgersub.RedeembyShare, TransLedgersub.TotalShares,
TransLedgersub.CashPdTotal, TransLedgersub.TotalValue, TransLedgersub.
DivEarned, TransLedgersub.RedeemedAmt
ORDER BY Max(TransLedgersub.DateTransaction) DESC;

Thanks in advance.
Deb

 

[Guest]

You mention "report", "subform", "Max...Previous", "TOP 5",...

Do you just want to display the most recent 2 records per client in the
report? Are all the desired records included in the report?

 

[DJTI via AccessMonster.com]

Duane,
Thank you for responding! and YES, I just want to display the most recent 2
records per client's investment in the report. The records I need are in the
following query that I worked on earlier for my report. I abandoned that idea
to see if a 'subreport' was my answer, but I think that complicated the
matter.

I just need the most recent 2 records per client's investment for the report.
Please tell me how. I am posting the SQL I was previously working on for my
report.

SQL:
SELECT [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, Investment.InvestmentID, Investment.PurchaseDate, Investment.
InvestmentAmt, Investment.PurShareValue, Investment.TotalInvValue, Investment.
CumuShares, [Investment Details].InvDetailID, [Investment Details].
DateTransaction, [Investment Details].QDivValue, [Investment Details].
QCashPdValue, [Investment Details].CurShareValue, [Investment Details].
RedeembyShare, [CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS
TotalShares, Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal, Round(
[TotalShares]*[CurShareValue],2) AS TotalValue, Round([QDivValue]*[CumuShares]
,2) AS DivEarned, Round([RedeembyShare]*[Investment Details].[CurShareValue],
2) AS RedeemedAmt
FROM [Shareholder Account] INNER JOIN (Investment INNER JOIN [Investment
Details] ON Investment.InvestmentID = [Investment Details].InvestmentID) ON
[Shareholder Account].ShareholderID = Investment.ShareholderID
ORDER BY [Shareholder Account].SLName, [Investment Details].DateTransaction
DESC;

You mention "report", "subform", "Max...Previous", "TOP 5",...

Do you just want to display the most recent 2 records per client in the
report? Are all the desired records included in the report?

I am stuck on a subform and need help or guidance. If I am going the wrong
direction, someone help aim me in the right direction.

[quoted text clipped - 22 lines]

Thanks in advance.
Deb

 

[Guest]

I think you can set your sorting and grouping to Client with header section
and then your date field descending. Add a text box to the detail section:
Name: txtCountClient
Control Source: =1
Running Sum: Over Group
Then add code to the On Format event of the Detail section:
Cancel = Me.txtCountClient >2

--
Duane Hookom
Microsoft Access MVP

Duane,
Thank you for responding! and YES, I just want to display the most recent 2
records per client's investment in the report. The records I need are in the
following query that I worked on earlier for my report. I abandoned that idea
to see if a 'subreport' was my answer, but I think that complicated the
matter.

I just need the most recent 2 records per client's investment for the report.
Please tell me how. I am posting the SQL I was previously working on for my
report.

SQL:
SELECT [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, Investment.InvestmentID, Investment.PurchaseDate, Investment.
InvestmentAmt, Investment.PurShareValue, Investment.TotalInvValue, Investment.
CumuShares, [Investment Details].InvDetailID, [Investment Details].
DateTransaction, [Investment Details].QDivValue, [Investment Details].
QCashPdValue, [Investment Details].CurShareValue, [Investment Details].
RedeembyShare, [CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS
TotalShares, Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal, Round(
[TotalShares]*[CurShareValue],2) AS TotalValue, Round([QDivValue]*[CumuShares]
,2) AS DivEarned, Round([RedeembyShare]*[Investment Details].[CurShareValue],
2) AS RedeemedAmt
FROM [Shareholder Account] INNER JOIN (Investment INNER JOIN [Investment
Details] ON Investment.InvestmentID = [Investment Details].InvestmentID) ON
[Shareholder Account].ShareholderID = Investment.ShareholderID
ORDER BY [Shareholder Account].SLName, [Investment Details].DateTransaction
DESC;

You mention "report", "subform", "Max...Previous", "TOP 5",...

Do you just want to display the most recent 2 records per client in the
report? Are all the desired records included in the report?

I am stuck on a subform and need help or guidance. If I am going the wrong
direction, someone help aim me in the right direction.

[quoted text clipped - 22 lines]

Thanks in advance.
Deb

 

[DJTI via AccessMonster.com]

Duane,
Thanks again. That only gives me the first 2 entries of the first client
(Shareholder) only. Excuse me if I am not explaining this right. This report
is sent to the Shareholders at the end of a quarter. Shareholders may have
many investments, they will get a report for each investment. Here are the
tables.

Tables: DataType:

[Shareholder Account]
ShareholderID Autonumber
ShareholderName text
Address text
Etc.

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

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

I think you can set your sorting and grouping to Client with header section
and then your date field descending. Add a text box to the detail section:
Name: txtCountClient
Control Source: =1
Running Sum: Over Group
Then add code to the On Format event of the Detail section:
Cancel = Me.txtCountClient >2

Duane,
Thank you for responding! and YES, I just want to display the most recent 2

[quoted text clipped - 37 lines]

 

[DJTI via AccessMonster.com]

Duane, I worked on it more and now have the report for all clients. The
problem now is I have 2 reports per client, one report for each of the 2
entries. What I really need is to have One report with stating:
Shares as of: [PreviousDateTransaction] [TotalShares]
Shares as of: [NowDateTransaction] [TotalShares]

Thanks,
Deb

Duane,
Thanks again. That only gives me the first 2 entries of the first client
(Shareholder) only. Excuse me if I am not explaining this right. This report
is sent to the Shareholders at the end of a quarter. Shareholders may have
many investments, they will get a report for each investment. Here are the
tables.

Tables: DataType:

[Shareholder Account]
ShareholderID Autonumber
ShareholderName text
Address text
Etc.

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

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

I think you can set your sorting and grouping to Client with header section
and then your date field descending. Add a text box to the detail section:

[quoted text clipped - 9 lines]

 

[Guest]

I'm not sure what you actually need. You mention Client but there is no
client table or clientId. I don't know if you want the most recent two by
Investment or transaction.

Do you understand that if you create a query with all the records, you can
set the sorting and grouping as I suggested earlier to display only two
records per group?

--
Duane Hookom
Microsoft Access MVP

Duane, I worked on it more and now have the report for all clients. The
problem now is I have 2 reports per client, one report for each of the 2
entries. What I really need is to have One report with stating:
Shares as of: [PreviousDateTransaction] [TotalShares]
Shares as of: [NowDateTransaction] [TotalShares]

Thanks,
Deb

Duane,
Thanks again. That only gives me the first 2 entries of the first client
(Shareholder) only. Excuse me if I am not explaining this right. This report
is sent to the Shareholders at the end of a quarter. Shareholders may have
many investments, they will get a report for each investment. Here are the
tables.

Tables: DataType:

[Shareholder Account]
ShareholderID Autonumber
ShareholderName text
Address text
Etc.

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

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

I think you can set your sorting and grouping to Client with header section
and then your date field descending. Add a text box to the detail section:

[quoted text clipped - 9 lines]

Thanks in advance.
Deb

 

[DJTI via AccessMonster.com]

I'm not sure what you actually need. You mention Client but there is no
client table or clientId. I don't know if you want the most recent two by
Investment or transaction.

The Shareholder is the Client...I am sorry for the misnaming.
I do need the most recent transactions on the investments.

Do you understand that if you create a query with all the records, you can
set the sorting and grouping as I suggested earlier to display only two
records per group?

I did not understand about the sorting and grouping until you suggested it,
am I understanding that the sorting and grouping is done on the report..is
that correct? I now have the last 2 transactions showing for reports...the
problem is that I have 2 pages of the report...page 1 shows the last
transaction entry...page 2 shows the transaction previous...I need them both
listed on the same page.

I want a one page report that lists the Shareholders name and address,
Investment info...and the Investment details (previous date and totalshares)
plus the (most recent date and transactions). I don't know how to show this
on the report.

Shareholder Account Table is One-Many Join to Investments Table
Investments Table is One-Many Join to Investment Details

I thought this would be simple, but I dont have the understanding on how to
get the previous date and TotalShares for the Report.

Thanks,
Deb

Duane, I worked on it more and now have the report for all clients. The
problem now is I have 2 reports per client, one report for each of the 2

[quoted text clipped - 42 lines]

 

[DJTI via AccessMonster.com]

Bear with me Duane...I inherited this db...have done major surgery to
normalize it....and now I am stuck on this report....(the monkey on my back
has become a Gorilla and I can't think straight!)

I'm not sure what you actually need. You mention Client but there is no
client table or clientId. I don't know if you want the most recent two by
Investment or transaction.

Do you understand that if you create a query with all the records, you can
set the sorting and grouping as I suggested earlier to display only two
records per group?

Duane, I worked on it more and now have the report for all clients. The
problem now is I have 2 reports per client, one report for each of the 2

[quoted text clipped - 42 lines]

 

[Guest]

If you have records displaying on two pages then you must have either section
heights or some property settings that push a record to the second page.

Also, how are you storing or calculating TotalShares?

--
Duane Hookom
Microsoft Access MVP

I'm not sure what you actually need. You mention Client but there is no
client table or clientId. I don't know if you want the most recent two by
Investment or transaction.

The Shareholder is the Client...I am sorry for the misnaming.
I do need the most recent transactions on the investments.

Do you understand that if you create a query with all the records, you can
set the sorting and grouping as I suggested earlier to display only two
records per group?

I did not understand about the sorting and grouping until you suggested it,
am I understanding that the sorting and grouping is done on the report..is
that correct? I now have the last 2 transactions showing for reports...the
problem is that I have 2 pages of the report...page 1 shows the last
transaction entry...page 2 shows the transaction previous...I need them both
listed on the same page.

I want a one page report that lists the Shareholders name and address,
Investment info...and the Investment details (previous date and totalshares)
plus the (most recent date and transactions). I don't know how to show this
on the report.

Shareholder Account Table is One-Many Join to Investments Table
Investments Table is One-Many Join to Investment Details

I thought this would be simple, but I dont have the understanding on how to
get the previous date and TotalShares for the Report.

Thanks,
Deb

Duane, I worked on it more and now have the report for all clients. The
problem now is I have 2 reports per client, one report for each of the 2

[quoted text clipped - 42 lines]

Thanks in advance.
Deb

 

[DJTI via AccessMonster.com]

If you have records displaying on two pages then you must have either section
heights or some property settings that push a record to the second page.

How do I adjust the property settings to keep a record on same page? I have
changed the settings with no difference.

Also, how are you storing or calculating TotalShares?

The TotalShares is not stored, I calculated in subform and thought I could do
the same for a report...I am wrong?

I'm not sure what you actually need. You mention Client but there is no
client table or clientId. I don't know if you want the most recent two by [quoted text clipped - 31 lines]
Thanks in advance.
Deb

 

[Guest]

Records don't normally get printed on separate pages. I can't see any of the
items I suggested might cause this.

If you can calculate something in a subform, you can probably do the same in
a subreport or subquery or code or whatever.

--
Duane Hookom
Microsoft Access MVP

If you have records displaying on two pages then you must have either section
heights or some property settings that push a record to the second page.

How do I adjust the property settings to keep a record on same page? I have
changed the settings with no difference.

Also, how are you storing or calculating TotalShares?

The TotalShares is not stored, I calculated in subform and thought I could do
the same for a report...I am wrong?

I'm not sure what you actually need. You mention Client but there is no
client table or clientId. I don't know if you want the most recent two by

[quoted text clipped - 31 lines]

Thanks in advance.
Deb

 

[DJTI via AccessMonster.com]

Hi Duane,

Records don't normally get printed on separate pages. I can't see any of the
items I suggested might cause this.

If you can calculate something in a subform, you can probably do the same in
a subreport or subquery or code or whatever.

How do I select and calculate in a subquery for the [prevdatetransaction] and
[totalshares]? If I can get that in a query, then I can use the values in the
report.

If you have records displaying on two pages then you must have either section
heights or some property settings that push a record to the second page. [quoted text clipped - 11 lines]
Thanks in advance.
Deb

 

[DJTI via AccessMonster.com]

I was able to get what I needed with a subquery. Thanks for helping me.
SQL
SELECT [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, Investment.InvestmentID, Investment.PurchaseDate, Investment.
InvestmentAmt, Investment.PurShareValue, Investment.TotalInvValue, Investment.
CumuShares, [Investment Details].InvDetailID, [Investment Details].
DateTransaction, [Investment Details].QDivValue, [Investment Details].
QCashPdValue, [Investment Details].CurShareValue, [Investment Details].
RedeembyShare, [CumuShares]+nz([DivEarned])-nz([RedeembyShare]) AS
TotalShares, Round([QCashPdValue]*[CumuShares],2) AS CashPdTotal, Round(
[TotalShares]*[CurShareValue],2) AS TotalValue, Round([QDivValue]*[CumuShares]
,2) AS DivEarned, Round([RedeembyShare]*[Investment Details].CurShareValue,2)
AS RedeemedAmt, LastTransactionDateqry.LastTransDate, LastTransactionDateqry.
LastTotalShares
FROM [Shareholder Account] INNER JOIN ((Investment INNER JOIN
LastTransactionDateqry ON Investment.InvestmentID = LastTransactionDateqry.
InvestmentID) INNER JOIN [Investment Details] ON Investment.InvestmentID =
[Investment Details].InvestmentID) ON [Shareholder Account].ShareholderID =
Investment.ShareholderID
WHERE ((([Investment Details].DateTransaction)=[RecConfDate]))
ORDER BY [Shareholder Account].SLName, [Investment Details].DateTransaction
DESC;

Records don't normally get printed on separate pages. I can't see any of the
items I suggested might cause this.

If you can calculate something in a subform, you can probably do the same in
a subreport or subquery or code or whatever.

If you have records displaying on two pages then you must have either section
heights or some property settings that push a record to the second page. [quoted text clipped - 11 lines]
Thanks in advance.
Deb