Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Help

Status
Not open for further replies.

primagic

IS-IT--Management
Jul 24, 2008
476
GB
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.

 
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
 
Here is the SQL for the query:

Code:
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


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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
When I add AND tblBeginEndVals.BeginValDate>DateAdd("m",-qryAccruals.MonthsAccrued,Date() this to the criteria, I get datatype mismatch error
 
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
 
I managed to get it working by using the following sql code:

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:

Code:
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.
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top