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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query Edit 1
Compare this month to last month 5
Group By Qtr 1
Characters found at end of sql error-Repost 2

Back
Top