Exclude Some Values From Query

[hangbill]

Please see image, dummy data.
I need to sum all of marketvalue but exclude the yellow and orange records- these are InvestmentFK 8, 9, 10,12 from earlier dates.

So need to sum marketvalue for the blue InvestmentFK 8, 9, 10,12 as well as all all unique foreign keys (1,3,4,5 etc)



Hope u can help.
Thanks

[ranman256]

make an inclusion table, via make table of all investFK, then remove those 4.
then join the inclusion table to yours shown above,and only get the ones you want.

or make an exclusion table with the 4 items,
make a query that uses a subquery for INVESTMENTFK, not in (select Investmentfk from tExclude)
(similar to a FIND DUPLICATE query via query designer)

[CJ_London]

to clarify what you are asking - you are looking for the latest valuation for each FK

This requires two queries, the first to determine the latest date for each PK and the second to then get the valuation

The first query is a simple aggregate query I'll call it maxQry

SELECT InvestmentFK, max(MarketValueDate) as MaxDate
FROM myTable
GROUP BY InvestmentFK

You then join this to your table on FK and dates and sum

SELECT myTable.InvestmentFK, myTable.MarketValueDate, sum(myTable.marketValue) as totalMarketValue
FROM myTable INNER JOIN maxQry ON myTable.InvestmentFK=maxQry.InvestmentFK AND myTable.MarketValueDate=maxQry.MaxDate
GROUP BY myTable.InvestmentFK, myTable.MarketValueDate

[hangbill]

Thanks for responding Ranman.
This is a dynamic situation and some of the foreign keys that are unique in the image (eg 1,3,5,7 etc) might not remain unique. This will happen for example when key 1, say, gets a new market value on some future date. So solution needs to account for changing situation. Something like Sum(markeValue but if InvestmentFK not unique then use only the market value from the most recent date)

[hangbill]

OK Ajax, going to try this. Thanks

[hangbill]

Perfect, thanks so much Ajax.
Got it working with a few tweaks. Would not have figured this. Must now try understand the joining sql.
Thanks again

SELECT marketValue.InvestmentFK, Max(marketValue.MarketValueDate) AS MaxDate
FROM marketValue
GROUP BY marketValue.InvestmentFK;

and

SELECT marketValue.InvestmentFK, marketValue.MarketValueDate, sum(marketValue.marketValue) AS totalMarketValue
FROM marketValue INNER JOIN [Max Value Date] ON (marketValue.InvestmentFK=[Max Value Date].InvestmentFK) AND (marketValue.MarketValueDate=[Max Value Date].MaxDate)
GROUP BY marketValue.InvestmentFK, marketValue.MarketValueDate;

[hangbill]

Actually seems it is not summing, but rather returning the most recent values. This is not a prob because I want to Dsum them on a report. But then not sure what the Sum function does in this sql

[CJ_London]

to get a single total, remove the grouping columns

SELECT sum(marketValue.marketValue) AS totalMarketValue
FROM marketValue INNER JOIN [Max Value Date] ON (marketValue.InvestmentFK=[Max Value Date].InvestmentFK) AND (marketValue.MarketValueDate=[Max Value Date].MaxDate)

Note that having spaces in table/query field names is a bad practice to develop. It will come back to bite you at some point