Solved

UNION query problem: how to include memo fields

Posted on 2006-07-04
4
278 Views
Last Modified: 2006-11-18
I have the following union query:

SELECT tblTrades.TradeID, tblTrades.PairID, tblTrades.StrategyID, tblTrades.StartDateTime AS InteractionTime, tblTrades.LongShort, tblTrades.EndDateTime, tblTrades.Amount, tblTrades.EntryPrice, tblTrades.StopLoss, tblTrades.ExitPrice, tblTrades.ProfitLoss, tblTrades.Target, tblTrades.Duration, tblTrades.RiskReward, tblTrades.ExitReason, null, null, null FROM tblTrades
UNION
SELECT tblNotes.NoteID, tblNotes.PairID, tblNotes.StrategyID, tblNotes.NoteTime AS InteractionTime, null, null, null, null, null, null, null, null, null, null, null, tblNotes.Entry, tblNotes.ChartID, tblNotes.Trend FROM tblNotes
ORDER BY PairID, InteractionTime

Howerver, when I try to run it, I get an error saying "Can't use memo or OLE object field '[fieldname]' in the SELECT clause of a union query. Unfortunately, I have more than one memo field in both SELECT clauses, without which the query is useless. Is there a solution for this problem?

Thanks.
RW Wood
0
Comment
Question by:PstWood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 125 total points
ID: 17038067
Hi PstWood,
You can change Union to Union All to solve the memo problem, but of course that might mean you get duplicates in your results.


Pete
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17038131
have you considered

set up the union query as
SELECT 'T' as Type,tblTrades.TradeID, tblTrades.PairID, tblTrades.StrategyID, tblTrades.StartDateTime AS InteractionTime, tblTrades.LongShort, tblTrades.EndDateTime, tblTrades.Amount, tblTrades.EntryPrice, tblTrades.StopLoss, tblTrades.ExitPrice, tblTrades.ProfitLoss, tblTrades.Target, tblTrades.Duration, tblTrades.RiskReward, tblTrades.ExitReason, null, null, null FROM tblTrades
UNION
SELECT 'N' as Type , tblNotes.NoteID, tblNotes.PairID, tblNotes.StrategyID, tblNotes.NoteTime AS InteractionTime, null, null, null, null, null, null, null, null, null, null, null, tblNotes.Entry, tblNotes.ChartID, tblNotes.Trend FROM tblNotes

(removing all the Memo Columns....)

then use the above query and
left join to the trades and notes tables
and re-add in the memo... columns..

and have the order by in that query..
ORDER BY PairID, InteractionTime

hth
0
 

Author Comment

by:PstWood
ID: 17038435
Thanks Pete. That did it. What would be an example of data that would give duplicates? I don't seem to have any with the data I'm using.

0
 
LVL 77

Expert Comment

by:peter57r
ID: 17038466
I think duplicate records are very unlikely with memo fields involved, but you never know.

Pete
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question