Formula in Access Query 3

[AGoodGuy]

I have a query in Access 2007 and I have this below:

=IF([Master Renewal Analysis].[Latest Renewal Notice Premium])>([Master Renewal Analysis].[Original Written Premium]),"Uprate", IF(([Master Renewal Analysis].[Latest Renewal Notice Premium])<])>([Master Renewal Analysis].[Original Written Premium]),"Downrate","No Change"))

I have this in a formula inmy Excel spreadsheet; how to write this in a query in Access and get the same results as in my spreadsheet that will show which items are Uprate, Downrate or No Change

Please keep in mind that in Excel ([Master Renewal Analysis].[Latest Renewal Notice Premium]) is in Column D and ])>([Master Renewal Analysis].[Original Written Premium]) is Column C

 

[AGoodGuy]

The common field that should match is the Policy fee in the query and the State policy Fees in the table. They both should match.

 

[dhookom]

I understand that part but looking at both the table and the query, which fields are used to link/lookup common records/values?

Duane
Hook'D on Access
MS Access MVP

 

[AGoodGuy]

Policy Fee and State Policy Fee. What I have done is create an unmatch query to pull out any policies with a different policy fee other than what is should be. I match the field name policy fee form the query against the State policy fee from the table and had the fallouts of any policies joined by the policy fee + State policy fee. I hope this clears up any question you had.

 

[dhookom]

I was hoping you would state something like "[State] is the primary key in the table [State Policy Fee Table]. I won't to use this field to compare fee fields based on the [State] field in the query [03Renewal Offere Analysis]."

This would tell us the relationship between the table and query as well as what you wanted to compare. If my assumption is correct, try this SQL:

SELECT [State Policy Fee Table].State, PolicyNumber, [State policy fees], [Policy Fee]
FROM [State Policy Fee Table] JOIN [03Renewal Offere Analysis] ON 
[State Policy Fee Table].State = [03Renewal Offere Analysis].State
WHERE [State policy fees] <> [Policy Fee]

Duane
Hook'D on Access
MS Access MVP