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

Serial Numbers in Union Query

P: 6

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"));
  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"));
  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"));
  17. UNION ALL 
  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"));
  23. UNION ALL 
  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,951
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

Expert Mod 15k+
P: 31,769
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.

Attached Files
File Type: zip SB1 (795.4 KB, 47 views)
May 28 '15 #4

Expert Mod 15k+
P: 31,769
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.