Experts Exchange connects you with the people and services you need so you can get back to work.

Query with too many variables

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.

jmerulla

Asked: 2013-01-03

Advertise Here

1 Solution

LVL 61

mbizupCommented: 2013-01-03

Try this:

SELECT * 
FROM History, Temp
WHERE History.Ticker = Temp.Ticker AND History.Quarter = Forms!Front!PreviousQuarter AND History.Category <> Temp.Category

Open in new window

LVL 31

hnasrCommented: 2013-01-03

One way is to select records using INNER JOIN, then apply the where clause.

UPDATE HISTORY INNER JOIN Temp ON History.Ticker = Temp.Ticker
SET HISTORY.fld1 = Temp.fld1, HISTORY.fld2 = Temp.fld2
WHERE History.Quarter = Form!Front!PreviousQuarter AND History.Category <> Temp.Category

Open in new window

LVL 32

awking00Commented: 2013-01-04

Can you provide some sample data for the two tables and what you expect the result of the update to look like?

LVL 2

jmerullaAuthor Commented: 2013-01-05

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.

LVL 61

mbizupCommented: 2013-01-05

A couple of corrections and simplifying the syntax a bit:

SELECT temp.*, hist.*
FROM [Temporary Add to History per Quarter2] temp, [Fund History] hist
WHERE (hist.[Full Name Category] <> temp.[Full Name 
Category]) AND (temp.Ticker = hist.[Ticker]) AND (hist.Quarter = [Forms]![Front]![Previous Qtr])

Open in new window

If that still returns no records, then as requested above please provide some sample data meaning:

- Sample data representative of what you see in both tables
- Expected results based on the data given

(Mask any sensitive data as needed)

Or better yet, upload a sample database (.mdb or accdb file) with the query, tables and data involved (again, masking any sensitive data).

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Query with too many variables

Join & Write a Comment Already a member? Login.

Featured Post