How to do Combine the Two Different Queries in UNION Qry

[sandya]

Can anyone help on my difficult problem

Am trying many times in UNION query but it’s not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.

They Quay’s details:
1.Customer Credit Transaction1
2.Customer Credit Transaction2

Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.

So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.

The both query details:

Customer Credit Transaction Final
SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount]
FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID
WHERE (((BillBook1.BillMode)="Credit"));
UNION ALL
SELECT BillBook2.TID, BillBook2.BILLNo, BillBook2.BILLDate, BillBook2.Customer, BillBook2.BillMode, [BillBook2 Vat Details].[TOTAL Rs] AS [CC Amount]
FROM BillBook2 INNER JOIN [BillBook2 Vat Details] ON BillBook2.TID = [BillBook2 Vat Details].TID
WHERE (((BillBook2.BillMode)="Credit"));

ReceiptformQry
SELECT Receiptform.ID, Receiptform.RecNo, Receiptform.RecDate, Receiptform.Receivedfrom, Receiptform.Paymentfor, Receiptform.ReceiptMode, Receiptform.[Cheque/DDNo], Receiptform.Rupees
FROM Receiptform;

I need to combine above both query details of (Customer Credit Transaction Final & ReceiptformQry) and need to merge the some fields only and remaining fields are needed to show separately.

BillBook2.TID = Receiptform.ID
BillBook2.BILLNo = Receiptform.RecNo
Receiptform.RecDate = BillBook2.BILLDate
BillBook2.Customer = Receiptform.Receivedfrom
BillBook2.BillMode = Receiptform.ReceiptMode

Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately. i have attached the Project file can anyone help on my problem plzzz plzzz plzzz

Thanks in Advance
Sandhya.

 

[JHB]

The amount of fields need to be the same.
You can create some "Dummy" fields, like "0 as DummyFieldNo1" or "Null as DummyFieldNo1"

 

[sandya]

Customer Credit Transaction Final - CC Amount
ReceiptformQry - Rupees

Both CC Amount, Rupees amounts are need to show separately.
-------------------------------------------------------------
Ex: 1. Customer Credit Transaction Final

TID - BILLNo - BILLDate - Customer - BillMode - CC Amount
----------------------------------------------------------------------------
1 - 0002 - 1-Jan-13 - David - Credit - Rs. 25,000
2 - 0003 - 3-Jan-13 - Jimmy - Credit - Rs. 32,000
3 - 0004 - 5-Jan-13 - Johan - Credit - Rs. 28,000

Ex: 2. ReceiptformQry

ID - RecNo - RecDate - Receivedfrom - ReceiptMode - Rupees
-------------------------------------------------------------------------
1 - 0001 - 10-Feb-13 - David - Cash - Rs. 5,000
2 - 0002 - 11-Feb-13 - Jimmy - Cheque - Rs. 5,500
3 - 0003 - 12-Feb-13 - Johan - Cash - Rs. 6,500
--------------------------------------------------------------------------

above both details are club in one query below as my format.

TID - BILLNo - BILLDate - Customer - BillMode - CC Amount - Rupees
------------------------------------------------------------------------------------
1 ---- 0002 - ---1-Jan-13 --- David ---- Credit - ---Rs. 25,000
2 ---- 0003 ---- 3-Jan-13 --- Jimmy ---- Credit - ---Rs. 32,000
3 - ---0004 ---- 5-Jan-13 --- Johan ---- -Credit ---- Rs. 28,000
1 - ---0001 ----10-Feb-13 ---David - --- Cash - ----------------Rs. 5,000
2 ---- 0002 ---- 11-Feb-13 -- Jimmy ---- Cheque -------------- Rs. 5,500
3 - ---0003 ---- 12-Feb-13 -- Johan -----Cash - ----------------Rs. 6,500
--------------------------------------------------------------------------------------

i need above format so how to do diz. actually i don't have more knowledge in ms access Qry's. can you please help on this problem how to do changes.

 

[namliam]

What you need to do is make both query's look the same in order for them to be Uniun-able.

I.e. have the same number of columns with the same types....
The only problem you have is CC Amount vs Rupess which you want to go into different columns... So add in your first query a column value, Null or 0 named Rupees
In your second query insert a column called CC Amount with the dummy value of Null or 0 and you should be done.

 

[sandya]

Yes the main problem is CC Amount vs Rupees. So you mean add the columns in both tables or queries?

 

[MSAccessRookie]

Yes the main problem is CC Amount vs Rupees. So you mean add the columns in both tables or queries?

As namliam pointed out, a union Query must have the same number of Fields in each section, and each Field must be the same type as the corresponding Field in each other section.

One more thing to consider is that if the Fields have different Names in the Queries, the Names used will be the ones from the First section of the Union Query. One way to avoid any confusion would be to use an Alias for each Field. That way all related Fields will have the same name.

-- Rookie

 

[sandya]

Thank you very much every one its working perfectly ......