Access sql Query problem

Question

I'm not getting proper result from this query.

SELECT Qty as op, 
       0 as secunit 
  FROM tbl_master 
 WHERE tb_sno = 1 
UNION
SELECT main_qty as op,
       main_unit as secunit 
  FROM purchase 
 WHERE tb_sno = 1 
   AND TRN_DATE < #2011/05/14# 
   AND trn_sno2 <> 0

This show less one record from actual. is there any way to get actual result

Have you tried using UNION ALL? Perhaps there is a duplicate record in one of those recordsets. — user174624
– user174624, Commented May 14, 2011 at 6:16
@fortheworld: With only two columns like this there doesn't seem to be much sense in keeping the duplicates in the result set. — Andriy M
– Andriy M, Commented May 15, 2011 at 9:22

user174624 · Accepted Answer · 2011-05-14 08:23:28Z

1

Have you tried using UNION ALL? Perhaps there is a duplicate record in one of those recordsets. (making my comment an answer)

answered May 14, 2011 at 8:23

user174624

Sign up to request clarification or add additional context in comments.

Comments

Brett Rossier · Accepted Answer · 2011-05-16 19:43:08Z

0

It looks like both of your queries set the exact same criteria on the tb_sno field (WHERE tb_sno = 1). Because of that, if op and sec_unit have the same resulting values in each query, a UNION will eliminate one of the duplicate rows. A UNION ALL will keep all duplicates, as fortheworld mentioned.

answered May 16, 2011 at 19:43

Brett Rossier

3,4923 gold badges31 silver badges38 bronze badges

Collectives™ on Stack Overflow

Access sql Query problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related