By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Serial Numbers in Union Query

P: 6
Hello,

How to create serial numbers in Union Query??

i have merged 5 querys on union query as per Code Tag. Just i need serial numbers as per data stored in union query.

i have searched some of Row_Number Topics & tryhing that but can't workout. Could you please replay anyone how to do this??


Expand|Select|Wrap|Line Numbers
  1. SELECT PAY_1.ID, Format(PAY_1.PayNo,  "\P-B0000") As PayNo, PAY_1.PayDate, PAY_1.R_Mode, PAY_1.ChqDDNo, Null AS ChqPassRej, PAY_1.Amount AS Withdraw, Null AS Deposit
  2. FROM PAY_1
  3. WHERE (((PAY_1.R_Mode) Like "NEFT" Or (PAY_1.R_Mode) Like "Advance Draft" Or (PAY_1.R_Mode) Like "RTGS" Or (PAY_1.R_Mode) Like "Cheque" Or (PAY_1.R_Mode) Like "DD"));
  4.  
  5. UNION ALL
  6.  
  7. SELECT PAY_2.ID, Format(PAY_2.PayNo,  "\P-A0000") As PayNo, PAY_2.PayDate, PAY_2.R_Mode, PAY_2.ChqDDNo, Null AS ChqPassRej, PAY_2.Amount AS Withdraw, Null AS Deposit
  8. FROM PAY_2
  9. WHERE (((PAY_2.R_Mode) Like "NEFT" Or (PAY_2.R_Mode) Like "Advance Draft" Or (PAY_2.R_Mode) Like "RTGS" Or (PAY_2.R_Mode) Like "Cheque" Or (PAY_2.R_Mode) Like "DD"));
  10.  
  11. UNION ALL
  12.  
  13. SELECT REC_1.ID, Format(REC_1.RecNo,  "\R-A0000") As RecNo, REC_1.RecDate, REC_1.R_Mode, REC_1.ChqDDNo, Null AS ChqPassRej, Null AS Withdraw, REC_1.Amount AS Deposit
  14. FROM REC_1
  15. WHERE (((REC_1.R_Mode) Like "NEFT" Or (REC_1.R_Mode) Like "Advance Draft" Or (REC_1.R_Mode) Like "RTGS" Or (REC_1.R_Mode) Like "Cheque" Or (REC_1.R_Mode) Like "DD"));
  16.  
  17. UNION ALL 
  18.  
  19. SELECT REC_2.ID, Format(REC_2.RecNo,  "\R-B0000") As RecNo, REC_2.RecDate, REC_2.R_Mode, REC_2.ChqDDNo, Null AS ChqPassRej, Null AS Withdraw, REC_2.Amount AS Deposit
  20. FROM REC_2
  21. WHERE (((REC_2.R_Mode) Like "NEFT" Or (REC_2.R_Mode) Like "Advance Draft" Or (REC_2.R_Mode) Like "RTGS" Or (REC_2.R_Mode) Like "Cheque" Or (REC_2.R_Mode) Like "DD"));
  22.  
  23. UNION ALL 
  24.  
  25. SELECT BT_MainQry.ID, Format(BT_MainQry.TrNo,  "\BT0000") As TrNo, BT_MainQry.TrDate, BT_MainQry.Particulars, BT_MainQry.ChqTNo, BT_MainQry.ChqPassdRej, BT_MainQry.Withdraws, BT_MainQry.Deposits
  26. FROM BT_MainQry;
May 27 '15 #1
Share this Question
Share on Google+
4 Replies


Seth Schrock
Expert 2.5K+
P: 2,939
If you are trying to have the count run through the whole query, then you will have to have your current query run as a subquery and have the record numbering happen in the main query.
May 27 '15 #2

NeoPa
Expert Mod 15k+
P: 31,429
This isn't something you can easily do with a query as quries especially, and databases generally, work around Set Theory. The results are a set of items. The order of those items is not determinable until after the items are returned.

There are probably ways you can encourage the system to create incrementing numbers for you, but that all depends on what you're doing.
May 27 '15 #3

P: 6
Yes, i have tried a Subquery with DCount Condition. its showing total counting numbers only can't run the serial number wise... :( :( :(

i have attached the dB file Can you please see once and go to Query: BT_Qry1 this query source from Query: BT_UnionQry1.
Just i want to add Serial Numbers in the above both of querys BT_Qry1 or BT_UnionQry1 as per data storing wise.

Thanks
Attached Files
File Type: zip SB1 BT.zip (795.4 KB, 41 views)
May 28 '15 #4

NeoPa
Expert Mod 15k+
P: 31,429
I'm afraid you won't find many people here incautious enough to download a db that hasn't even been asked for.

As a general rule you are expected to explain your situation in words where possible. Clearly and succinctly. A great benefit of working in Access is that it's very rarely not possible as queries are all stored as SQL, which is text.
May 29 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.