Join Query Sort

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was generously assisted by Ken to create the following Join query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL SELECT 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]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

The problem is with some DB changes the query prompts me for
VendorInv.InvoiceCode ......something happened to the sort order and i can
not figure it out. Can someone see what happened?
 
It would appear that the field 'InvoiceCode' no longer exists or has been
renamed in your VendorInv table or query.

Steve
 
Thanks Steve. I can officially state taht my mind is fried from going back
and forth with redesigns. If you could assist me in one question and that is
if i need to enter additional fields like blue, black or white how would edit
this query so it would work the same?

SteveM said:
It would appear that the field 'InvoiceCode' no longer exists or has been
renamed in your VendorInv table or query.

Steve

acss said:
I was generously assisted by Ken to create the following Join query:

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, Vendor.VendorCtry, Vendor.VendorCostCenter,
VendorInv.PerDate, VendorInv.InvDesc, VendorInv.InvAmt,
VendorInv.InvoiceCode, 1 As SortOrder
FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID
WHERE (((VendorInv.EnterDate) Between
[Forms]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((Vendor.VendorCtry)
Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
UNION ALL SELECT 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]![fdlgAskForDatesAndCity2]![txtStartDate] And
[Forms]![fdlgAskForDatesAndCity2]![txtEndDate]) AND ((T.VendorCtry) Like
[Forms]![fdlgAskForDatesAndCity2]![cboCity] & "*"))
ORDER BY SortOrder, VendorInv.EnterDate;

The problem is with some DB changes the query prompts me for
VendorInv.InvoiceCode ......something happened to the sort order and i can
not figure it out. Can someone see what happened?
Click to expand...
Click to expand...
 
Back
Top