Serial Numbers in Union Query

[SahilSS]

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??

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
FROM PAY_1
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"));

UNION ALL

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
FROM PAY_2
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"));

UNION ALL

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
FROM REC_1
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"));

UNION ALL 

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
FROM REC_2
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"));

UNION ALL 

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
FROM BT_MainQry;

[Seth Schrock]

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.

[NeoPa]

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.

[SahilSS]

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

[NeoPa]

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.