asked on

UNION query problem: how to include memo fields

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

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

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

PstWood

ASKER

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.

peter57r

I think duplicate records are very unlikely with memo fields involved, but you never know.

Pete