Join Query Sort

A

acss

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?
 
S

SteveM

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

Steve
 
A

acss

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?
 

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


Top