INSERT UNION QUERY

[Regan via AccessMonster.com]

Hi, i'm wondering why my insert query isn't working. if i take away the
'INSERT INTO' bit, it selects the right records to insert. But when i put
INSERT INTO it comes up with
'Characters found after SQL statement' i tried other things like taking away
the ; . but i can't seem to get it to work

here is the SQL

INSERT INTO tblinvexpdetails(InvoiceID, Expense, Charge)

SELECT "11" AS InvoiceID,
tblExpenses.Expense AS Expense,
Sum(tblexpenseLog.Quantity*tblexpenses.chargeout) AS total

FROM tblExpenses INNER JOIN tblExpenseLog ON tblExpenses.ExpenseID =
tblExpenseLog.ExpenseID

WHERE tblexpenselog.fileID=Forms!frmcreateInv!txtFileID
AND tblexpenselog.expensedate>= Forms!frmcreateInv!txtDateFrom
AND tblexpenselog.expensedate<= Forms!frmcreateInv!txtDateTo
GROUP BY tblexpenses.Expense;

UNION

SELECT "11" AS InvoiceID,
"Transport" AS Expense,
Sum(tbltravelLog.km*TTKP.TravelPrice) AS Total

FROM tblTravelLog INNER JOIN TblTravelKmPricing AS TTKP ON tblTravelLog.
TravelPricingID = TTKP.TravelPriceID

WHERE (((tblTravelLog.FileID)= [Forms]![frmcreateInv]![txtFileID])
AND ((tblTravelLog.TravelDate)>= [Forms]![frmcreateInv]![txtDateFrom]
AND (tblTravelLog.TravelDate)<= [Forms]![frmcreateInv]![txtDateTo]));

Thanks for your help in advance
Cheers

 

[John Spencer]

Try dropping the semi-colons after each of the queries that you are
unioning.

 

[Van T. Dinh]

It looks like JET interprets the SQL String to just before the keyword
"Union" is a complete action SQL and then it suddenly found the rest of the
SQL String. There is also the semi-colon just befire Union that may confuse
JET.

1, Try removing the semi-colon just before the Union (unlikely to work???)

2. Try the Union as a SubQuery which is used as the Source from the INSERT
like:

========
INSERT INTO tblinvexpdetails(InvoiceID, Expense, Charge)

SELECT InvoiceID, Expense, [Total]
FROM
(
SELECT "11" AS InvoiceID,
tblExpenses.Expense AS Expense,
Sum(tblexpenseLog.Quantity*tblexpenses.chargeout) AS [Total]

FROM tblExpenses INNER JOIN tblExpenseLog
ON tblExpenses.ExpenseID = tblExpenseLog.ExpenseID

WHERE (tblexpenselog.fileID=Forms!frmcreateInv!txtFileID)
AND (tblexpenselog.expensedate>= Forms!frmcreateInv!txtDateFrom)
AND (tblexpenselog.expensedate<= Forms!frmcreateInv!txtDateTo)
GROUP BY tblexpenses.Expense

UNION

SELECT "11",
"Transport",
Sum(tbltravelLog.km*TTKP.TravelPrice)
FROM tblTravelLog INNER JOIN TblTravelKmPricing AS TTKP
ON tblTravelLog.TravelPricingID = TTKP.TravelPriceID

WHERE (tblTravelLog.FileID= [Forms]![frmcreateInv]![txtFileID])
AND (tblTravelLog.TravelDate>= [Forms]![frmcreateInv]![txtDateFrom])
AND (tblTravelLog.TravelDate<= [Forms]![frmcreateInv]![txtDateTo])
)
========

Notes:
1. Parentheses in the posted SQL were incorrect. If you don't copy and post
the above SQL and modify your SQL, you need to check the parens carefully.

2. Aliases in Selection List in the second part of the Union are ignored by
JET if they are correct and reported as errors if they are incorrectly used.
Thus, there is no point in using Aliases and I have removed them.

--
HTH
Van T. Dinh
MVP (Access)

Hi, i'm wondering why my insert query isn't working. if i take away the
'INSERT INTO' bit, it selects the right records to insert. But when i put
INSERT INTO it comes up with
'Characters found after SQL statement' i tried other things like taking
away
the ; . but i can't seem to get it to work

here is the SQL

INSERT INTO tblinvexpdetails(InvoiceID, Expense, Charge)

SELECT "11" AS InvoiceID,
tblExpenses.Expense AS Expense,
Sum(tblexpenseLog.Quantity*tblexpenses.chargeout) AS total

FROM tblExpenses INNER JOIN tblExpenseLog ON tblExpenses.ExpenseID =
tblExpenseLog.ExpenseID

WHERE tblexpenselog.fileID=Forms!frmcreateInv!txtFileID
AND tblexpenselog.expensedate>= Forms!frmcreateInv!txtDateFrom
AND tblexpenselog.expensedate<= Forms!frmcreateInv!txtDateTo
GROUP BY tblexpenses.Expense;

UNION

SELECT "11" AS InvoiceID,
"Transport" AS Expense,
Sum(tbltravelLog.km*TTKP.TravelPrice) AS Total

FROM tblTravelLog INNER JOIN TblTravelKmPricing AS TTKP ON tblTravelLog.
TravelPricingID = TTKP.TravelPriceID

WHERE (((tblTravelLog.FileID)= [Forms]![frmcreateInv]![txtFileID])
AND ((tblTravelLog.TravelDate)>= [Forms]![frmcreateInv]![txtDateFrom]
AND (tblTravelLog.TravelDate)<= [Forms]![frmcreateInv]![txtDateTo]));

Thanks for your help in advance
Cheers

 

[Regan via AccessMonster.com]

Cheers Van T. Dinh,

Worked a charm, did exactly what i wanted.

Thanks for the notes too, the parentheses i didn't put there. they were
automatically put there.

it's good to know i don't have to do so much writing now with adding aliases
a second time.

Thanks again and have an OrSumOnum Day