Union Query Edit

  • Thread starter Thread starter acss
  • Start date Start date
A

acss

I had assitance on creating a union query a while back and now on a new
design i would like to utilize the union query but to edit the fields so it
adapts to the new design. My current query is :

SELECT Customers.CompanyName, Inv.InvoiceNum, Inv.OrderDate, [Service
Details].OrderID, Inv.InvoiceNum, Customers.FROMCC, Customers.BVRACCT,
Customers.RECLASSTOACCT, Customers.RECLASSTOCC, Customers.Country, [Service
Details].Cost, Customers.TYPE
FROM Service INNER JOIN ((Customers INNER JOIN Inv ON Customers.CustomerID =
Inv.CustomerID) INNER JOIN [Service Details] ON Inv.InvID = [Service
Details].OrderID) ON Service.SERVICEID = [Service Details].ServiceID;

And the one i want to edit is
SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, VendorInv.InvDate, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvCode,Vendor.VendorCtry, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![GSPExport2]![cboCity] & "*"))
UNION ALL SELECT Null, Null, Null, Null, Null, Null, Null, Null,Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![GSPExport2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate, Vendor.VendorCtry;

I am getting syntax errors galore and i really would like to utilize the sql
since the form is really worthwhile. Does anyone have suggestions on how to
resolve this or suggestions on tackling this???
 
Not knowing what your syntax errors are, let's put aliases into the second
query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, VendorInv.InvDate, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvCode,Vendor.VendorCtry, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![GSPExport2]![cboCity] & "*"))
UNION ALL SELECT Null AS F1, Null AS F2, Null AS F3, Null AS F4,
Null AS F5, Null AS F6, Null AS F7, Null AS F8, Null AS F9,
Sum(TT.InvAmt) AS SF3, Null AS F11, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![GSPExport2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate, Vendor.VendorCtry;


Do you still get syntax errors?
--

Ken Snell
http://www.accessmvp.com/KDSnell/




acss said:
I had assitance on creating a union query a while back and now on a new
design i would like to utilize the union query but to edit the fields so
it
adapts to the new design. My current query is :

SELECT Customers.CompanyName, Inv.InvoiceNum, Inv.OrderDate, [Service
Details].OrderID, Inv.InvoiceNum, Customers.FROMCC, Customers.BVRACCT,
Customers.RECLASSTOACCT, Customers.RECLASSTOCC, Customers.Country,
[Service
Details].Cost, Customers.TYPE
FROM Service INNER JOIN ((Customers INNER JOIN Inv ON Customers.CustomerID
=
Inv.CustomerID) INNER JOIN [Service Details] ON Inv.InvID = [Service
Details].OrderID) ON Service.SERVICEID = [Service Details].ServiceID;

And the one i want to edit is
SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, VendorInv.InvDate, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvCode,Vendor.VendorCtry, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![GSPExport2]![cboCity] & "*"))
UNION ALL SELECT Null, Null, Null, Null, Null, Null, Null, Null,Null,
Sum(TT.InvAmt) AS SF3, Null, 2 AS SortOrder
FROM Vendor AS T INNER JOIN VendorInv AS TT ON T.VendorID = TT.VendorID
WHERE (((TT.EnterDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![GSPExport2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate, Vendor.VendorCtry;

I am getting syntax errors galore and i really would like to utilize the
sql
since the form is really worthwhile. Does anyone have suggestions on how
to
resolve this or suggestions on tackling this???
Click to expand...
 
Back
Top