Query Help

[primagic]

I have a query which retrieves a year's worth of data. In one of the fields I have a number which specifies how many month's a client has accrued.

So Client A may have accrued the last 3 months. So I only want to retrieve the last 3 months for him.

Client B may have accrued the last 6 months, so I only want to retrieve the last 6 months for them.

 

[dhookom]

Please share the SQL view of "query which retrieves a year's worth of data".
Also tell us which field "specifies how many month's a client has accrued".
What do you mean by "Last X months"?

Can you provide some sample records and how you want them to appear?

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Here is the SQL for the query:

SELECT tblBeginEndVals.Sedol, CLng([ParentID]) AS PID, tblIssuerParentMap.ParentID, tblStocks.IssuerName, tblStocks.Description, tblBeginEndVals.ClientCode, tblBeginEndVals.ProductCode, tblBeginEndVals.BeginValDate, tblBeginEndVals.YYYYMM, tblBeginEndVals.ExecCode, tblNomineeBeginEndVals.TrailRate, tblBeginEndVals.BeginValue, tblBeginEndVals.EndValue, (tblBeginEndVals.BeginShares*tblBeginEndVals.AvgGBPPrice)+tblBeginEndVals.WeightedCashFlows AS AvgMonthValue, ([AvgMonthValue]*[TrailRate])/12 AS TD, tblBeginEndVals.Nominee, qryAccruals.MonthsAccrued
FROM (tblIssuerParentMap LEFT JOIN qryAccruals ON tblIssuerParentMap.ParentID = qryAccruals.ParentLookupID) INNER JOIN ((tblNomineeBeginEndVals INNER JOIN tblBeginEndVals ON (tblNomineeBeginEndVals.Sedol = tblBeginEndVals.Sedol) AND (tblNomineeBeginEndVals.BeginValDate = tblBeginEndVals.BeginValDate) AND (tblNomineeBeginEndVals.Nominee = tblBeginEndVals.Nominee)) INNER JOIN tblStocks ON tblNomineeBeginEndVals.Sedol = tblStocks.Sedol) ON tblIssuerParentMap.IssuerName = tblStocks.IssuerName
WHERE (((tblBeginEndVals.Nominee)<>"0"))

The field which shows the months accrued is qryAccruals.MonthsAccrued

The last x months I mean displayed last 3 months or 4 months etc

So the records should display like for Client A (last 3 months). The dates in the tblBeginEndVals.BeginValDate are always the last day of the month.

Client A 31/08/2014 x x x
Client A 30/09/2014 x x x
Client A 31/10/2014 x x x
Client B 31/05/2014 x x x
Client B 30/06/2014 x x x
Client B 31/07/2014 x x x
Client B 31/08/2014 x x x
Client B 30/09/2014 x x x
Client B 31/10/2014 x x x

 

[PHV]

A starting point:
WHERE tblBeginEndVals.Nominee<>"0" AND tblBeginEndVals.BeginValDate>DateAdd("m",-qryAccruals.MonthsAccrued,Date())

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

When I add AND tblBeginEndVals.BeginValDate>DateAdd("m",-qryAccruals.MonthsAccrued,Date() this to the criteria, I get datatype mismatch error

 

[dhookom]

Since the error message suggests "datatype mismatch" it would be appropriate for you to provide the data types of all the fields in the expression as well as if there might be NULLs.

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

I managed to get it working by using the following sql code:

SELECT tblBeginEndVals.Sedol, CLng([ParentID]) AS PID, tblIssuerParentMap.ParentID, tblStocks.IssuerName, tblStocks.Description, tblBeginEndVals.ClientCode, tblBeginEndVals.ProductCode, tblBeginEndVals.BeginValDate AS TestDate, tblBeginEndVals.YYYYMM, tblBeginEndVals.ExecCode, tblNomineeBeginEndVals.TrailRate, tblBeginEndVals.BeginValue, tblBeginEndVals.EndValue, (tblBeginEndVals.BeginShares*tblBeginEndVals.AvgGBPPrice)+tblBeginEndVals.WeightedCashFlows AS AvgMonthValue, ([AvgMonthValue]*[TrailRate])/12 AS TD, tblBeginEndVals.Nominee, Nz([MonthsAccrued]+1,0) AS Accrued
FROM (tblIssuerParentMap LEFT JOIN qryAccruals ON tblIssuerParentMap.ParentID = qryAccruals.ParentLookupID) INNER JOIN ((tblNomineeBeginEndVals INNER JOIN tblBeginEndVals ON (tblNomineeBeginEndVals.Sedol = tblBeginEndVals.Sedol) AND (tblNomineeBeginEndVals.BeginValDate = tblBeginEndVals.BeginValDate) AND (tblNomineeBeginEndVals.Nominee = tblBeginEndVals.Nominee)) INNER JOIN tblStocks ON tblNomineeBeginEndVals.Sedol = tblStocks.Sedol) ON tblIssuerParentMap.IssuerName = tblStocks.IssuerName
WHERE (((tblBeginEndVals.BeginValDate) Between (DateAdd("m",-CLng(Nz([qryAccruals].[MonthsAccrued],0)),Date())) And Date()) AND ((tblBeginEndVals.Nominee)<>"0"))

However this is using Date() as criteria which is fine for testing but I need to use the BeginValDate field (which has the criteria) within the criteria. So something like:

WHERE (((tblBeginEndVals.BeginValDate) Between (DateAdd("m",-CLng(Nz([qryAccruals].[MonthsAccrued],0)),tblBeginEndVals.BeginValDate)) And tblBeginEndVals.BeginValDate) AND ((tblBeginEndVals.Nominee)<>"0"))

The query returns results but it returns to many results. Looks like it returns everything.

 

[dhookom]

Your query will return all dates since tblBeginEndVals.BeginValDate will always fall between an earlier date and tblBeginEndVals.BeginValDate.

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Would it?

I thought if the beginvaldate was 31/10/2014 and the months accrued was say 3, then it would only select records the previous 3 months records up until the 31/10/2014. How/why would it select more than 3 months?

 

[dhookom]

It's like stating:

WHERE 10 is between 4 AND 10

Duane
Hook'D on Access
MS Access MVP