Solved

UNION query problem: how to include memo fields

Posted on 2006-07-04
4
262 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
  • 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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

810 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