append query via SQL

[dan-cat]

Hello,

I have three tables (tblBookings,tblBookingsWHS,tblBookingsRET) from which I want to append data into one table. At the moment I have three different queries:


INSERT INTO tblSuppEmail2 ( VNo, Type )
SELECT tblBookings.VoucherNo, "AS" AS Type
FROM tblSuppEmail LEFT JOIN tblBookings ON tblSuppEmail.SuppCode = tblBookings.Suppcode
WHERE (((tblBookings.SuppFaxReq)=-1) AND ((tblBookings.SuppFaxSent)=0));

INSERT INTO tblSuppEmail2 ( VNo, Type )
SELECT tblBookingsWHS.VoucherNo, "WHS" AS Type
FROM tblSuppEmail LEFT JOIN tblBookingsWHS ON tblSuppEmail.SuppCode = tblBookingsWHS.Suppcode
WHERE (((tblBookingsWHS.SuppFaxReq)=-1) AND ((tblBookingsWHS.SuppFaxSent)=0));

INSERT INTO tblSuppEmail2 ( VNo, Type )
SELECT tblBookingsRET.VoucherNo, "RET" AS Type
FROM tblSuppEmail LEFT JOIN tblBookingsRET ON tblSuppEmail.SuppCode = tblBookingsRET.Suppcode
WHERE (((tblBookingsRET.SuppFaxReq)=-1) AND ((tblBookingsRET.SuppFaxSent)=0));


Is there anyway I can merge these three append queries into one query?

ThankYou

 

[Hypernoodle]

An option is to create a UNION subquery. For this example, let's name it "qBook"...

SELECT VoucherNo, Suppcode, "AS" AS Type FROM tblBookings WHERE SuppFaxReq=-1 AND SuppFaxSent=0
UNION SELECT VoucherNo, Suppcode, "WHS" AS Type FROM tblBookingsWHS WHERE SuppFaxReq=-1 AND SuppFaxSent=0
UNION SELECT VoucherNo, Suppcode, "RET" AS Type FROM tblBookingsRET WHERE SuppFaxReq=-1 AND SuppFaxSent=0;

Now, reference this subquery in your INSERT query...

INSERT INTO tblSuppEmail2 (VNo, Type)
SELECT qBook.VoucherNo, qBook.Type
FROM tblSuppEmail LEFT JOIN qBook ON tblSuppEmail.SuppCode = qBook.Suppcode;


I hope this helps.