Fix query engine issue.
The sub query, (SELECT Min(Year) FROM tblInsuredInformation AS i1 WHERE i1.RecordID = tblInsuredInformation.RecordID) AS [First Year], in the query below returns various year values that make sense for the data in the database.
The problem is that tblInsuredInformation doesn't have a Year field. It does have a FirstYear field.
tblPRFPolicies does have a Year field but I have verified that the calculated FirstYear column is not returning either tblInsuredInformation.Year or tblPRFPolicies.FirstYear. It could be returning Year in some records and FirstYear in other records.
If just SELECT Min(Year) FROM tblInsuredInformation is run, MS Access prompts for the value of Year, as it should.
Please fix. I hate it when I have to tell a client their database engine is inaccurate.
SELECT tblPRFPolicies.RecordID AS PolicyRecordID, tblInsuredInformation.RecordID AS InsuredsRecordID, tblPRFPolicies.PolicyNumber, tblPRFPolicies.AIP, tblPRFPolicies.Year, tblPRFPolicies.CancelationDate, tblInsuredInformation.AgentRecordID, tblInsuredInformation.Referral, tblPRFPolicies.StateOfPolicy, tblPRFPolicies.County, tblInsuredInformation.Territory, tblInsuredInformation.eDelivery, tblInsuredInformation.EmailAddress, tblInsuredInformation.Insured, tblInsuredInformation.Contact, tblPRFPolicies.TotalPastureAcres, tblPRFPolicies.TotalNonIrrigatedHayAcres, tblPRFPolicies.TotalIrrigatedHayAcres, tblPRFPolicies.TotalProducerPremium, tblPRFPolicies.TotalGrossPremium, tblPRFPolicies.CommissionRate, [TotalGrossPremium]*[CommissionRate] AS ExpectedComm, tblPRFPolicies.Category, tblPRFPolicies.Commission1, tblPRFPolicies.Commission2, (SELECT Min(Year) FROM tblInsuredInformation AS i1 WHERE i1.RecordID = tblInsuredInformation.RecordID) AS [First Year], p1.FirstPolicyYear, tblInsuredInformation.FirstYear AS ActualFirstYear
FROM (tblInsuredInformation INNER JOIN tblPRFPolicies ON tblInsuredInformation.RecordID = tblPRFPolicies.InsuredsRecordID) INNER JOIN (SELECT PolicyNumber, Min(Year) AS FirstPolicyYear FROM tblPRFPolicies GROUP BY PolicyNumber) AS p1 ON tblPRFPolicies.PolicyNumber = p1.PolicyNumber
WHERE (((tblPRFPolicies.CancelationDate) Is Null));
Patrick Headley
shared this idea
2 comments
-
Grover Park George
commented
In addition to the fact that this forum is for suggestions about improving Access, not trouble-shooting assistance, please note that the problem is, in fact, the way wrote the query was originally written. Without the aggregate function Min(), Access MUST select a matching value; the one which coincidentally falls first in the current recordset, not the one you want, but which isn't specified. Min() narrows that down to the one record you want, so that's the proper way to write the query. In other words, not a fault in the query engine.
That said, please take any specific product questions to an appropriate forum run for that purpose.
Good luck with your project.
-
Patrick Honorez
commented
[Year] is a reserved word.
And this is a place to submit ideas.
To debug your messy code I suggest you go to stackoverflow.com