Solved

UNION query problem: how to include memo fields

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

691 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