OS = Windows 2000, 5.00.2195, Service Pack 3
Access = 2000, 9.0.4402 sr-1
I have a table of weekly transactions of dollar amounts. Next, I created a query #1 to also show the year for the transaction using the YEAR function and show the last share price for the the year using a subselect (subquery). Query #1 showed the right data. This is the query:
SELECT year(t.date) AS TheYear, t.Date,
iif(t.transaction like "*employee*","Paid",iif(t.transaction like "*fee*","Expense","Appreciation")) AS TransactionType,
t.Transaction, t.Dollar, t.SharePrice, t.Shares, t.TotalShares,
(
SELECT Last(t2.SharePrice)
FROM FranklinTempleton AS t2
WHERE (((Year([t2].[date]))=year(t.date)) AND ((t2.Transaction) Not Like "*fee*"))
) AS LastSharePrice
FROM FranklinTempleton AS t;
A report using query #1 complained with this error message:
Multi-level GROUP BY clause is not allowed in a subquery.
I then created a query #2 which just did a "Select *" from query #1.
I changed the Data Record Source for the report to query #2 and then the report worked, sort of. It didn't show the results of the LastSharePrice field.
Further experimenting showed:
If I changed the report so that group headers and group footers were not shown, then LastShareprice did show up.
If I took query #2 and created a table from it, and used that table as the input for the report then, even with group header/footers turned on, the report worked fine.
I'll try some more experimenting and then see if I want to install the latest Office service pack.
This is a similar problem: http://www.access-programmers.co.uk/forums/showthread.php?t=60752&highlight=subquery
I think there is an Access/Jet bug/feature so I'm really posting this to share my experience. But if any of you folks can think of something I'm doing wrong, let me know.
Access = 2000, 9.0.4402 sr-1
I have a table of weekly transactions of dollar amounts. Next, I created a query #1 to also show the year for the transaction using the YEAR function and show the last share price for the the year using a subselect (subquery). Query #1 showed the right data. This is the query:
SELECT year(t.date) AS TheYear, t.Date,
iif(t.transaction like "*employee*","Paid",iif(t.transaction like "*fee*","Expense","Appreciation")) AS TransactionType,
t.Transaction, t.Dollar, t.SharePrice, t.Shares, t.TotalShares,
(
SELECT Last(t2.SharePrice)
FROM FranklinTempleton AS t2
WHERE (((Year([t2].[date]))=year(t.date)) AND ((t2.Transaction) Not Like "*fee*"))
) AS LastSharePrice
FROM FranklinTempleton AS t;
A report using query #1 complained with this error message:
Multi-level GROUP BY clause is not allowed in a subquery.
I then created a query #2 which just did a "Select *" from query #1.
I changed the Data Record Source for the report to query #2 and then the report worked, sort of. It didn't show the results of the LastSharePrice field.
Further experimenting showed:
If I changed the report so that group headers and group footers were not shown, then LastShareprice did show up.
If I took query #2 and created a table from it, and used that table as the input for the report then, even with group header/footers turned on, the report worked fine.
I'll try some more experimenting and then see if I want to install the latest Office service pack.
This is a similar problem: http://www.access-programmers.co.uk/forums/showthread.php?t=60752&highlight=subquery
I think there is an Access/Jet bug/feature so I'm really posting this to share my experience. But if any of you folks can think of something I'm doing wrong, let me know.