I am trying to write an update query but I can't get the initial select query right. I am hoping I am making this more difficult than it needs to be but I am stuck.
I have two tables - Temp (shortened for here) and History. They have all the same fields (I perform calculations in temp before appending it to history) and the link is ticker. I need to write a query to return values where ticker in the two tables is equal, quarter from history references the field on a form, and category from one table does not equal category from the other.
In other words, I want the values where
History.Ticker
History.Quarter (Form!Front!PreviousQuarter)
History.Category
is compared to
Temp.Ticker
Temp.Category
and values where the category is different will be returned.
Microsoft OfficeMicrosoft AccessSQL
Last Comment
mbizup
8/22/2022 - Mon
mbizup
Try this:
SELECT * FROM History, TempWHERE History.Ticker = Temp.Ticker AND History.Quarter = Forms!Front!PreviousQuarter AND History.Category <> Temp.Category
Here is what I have and the query doesn't return anything -
SELECT [Temporary Add to History per Quarter2].*, *
FROM [Temporary Add to History per Quarter2], [Fund History]
WHERE ((([Fund History].Quarter)=[Forms]![Front]![Previous Qtr]) AND (([Fund
History].[Full Name Category])<>[Temporary Add to History per Quarter2]![Full Name
Category]) AND (([Temporary Add to History per Quarter2].Ticker)=[Temporary Add to
History per Quarter2]![Ticker]));
The temp file is there and the field on the form is correct.
In the end, I want it to return a table like this -
Ticker Quarter Recommendation Full Name Category
AOGIX 2012-9-30 1 Aggressive Allocation
So I can make an update query to change recommendation from 1 to 2.
Open in new window