Hi there...I forgot to give this a title the other day, sorry.
I have an invoice that is printed off daily. The invoice is based on the query below. Now if the client actually has an preorder and this information is filled out in the order form, my invoice prints out fine. My problem is, most of the time a blank invoice with only customers address and routes on the invoice r needed. The driver then fills in the rest of the information. So what they do is open up the orders form pick the customer but leave the order detail blank. How do I get my query to bring in both groups of customers, the ones with the pre-ordered info and the blank ones so that the invoice report can be printed in order of route in one group?
Here is what I have...this gives me pre-ordered customers only. Any help would be gr8. Thx. BC
SELECT [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].EmployeeID, [Orders Qry].OrderDate, [Orders Qry].RequiredDate, [Orders Qry].ShippedDate, [Orders Qry].ShipVia, [Orders Qry].[Tax Total], [Orders Qry].Freight, [Orders Qry].ShipName, [Orders Qry].ShipAddress, [Orders Qry].ShipCity, [Orders Qry].ShipRegion, [Orders Qry].ShipPostalCode, [Orders Qry].ShipCountry, [Orders Qry].Phone, [Orders Qry].Cell, [Orders Qry].CompanyName, [Orders Qry].Address, [Orders Qry].City, [Orders Qry].Region, [Orders Qry].PostalCode, [Orders Qry].Country, [Orders Qry].Route, [Orders Qry].RoutePosition, [Orders Qry].[Delivery Notes], [Order Details Extended].ProductID, [Order Details Extended].ProductName, [Order Details Extended].UnitPrice, [Order Details Extended].Quantity, [Order Details Extended].Returns, [Order Details Extended].[Tax Rate], [Order Details Extended].ExtendedPrice, [Order Details Extended].TaxAmount
FROM [Order Details Extended] INNER JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
GROUP BY [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].EmployeeID, [Orders Qry].OrderDate, [Orders Qry].RequiredDate, [Orders Qry].ShippedDate, [Orders Qry].ShipVia, [Orders Qry].[Tax Total], [Orders Qry].Freight, [Orders Qry].ShipName, [Orders Qry].ShipAddress, [Orders Qry].ShipCity, [Orders Qry].ShipRegion, [Orders Qry].ShipPostalCode, [Orders Qry].ShipCountry, [Orders Qry].Phone, [Orders Qry].Cell, [Orders Qry].CompanyName, [Orders Qry].Address, [Orders Qry].City, [Orders Qry].Region, [Orders Qry].PostalCode, [Orders Qry].Country, [Orders Qry].Route, [Orders Qry].RoutePosition, [Orders Qry].[Delivery Notes], [Order Details Extended].ProductID, [Order Details Extended].ProductName, [Order Details Extended].UnitPrice, [Order Details Extended].Quantity, [Order Details Extended].Returns, [Order Details Extended].[Tax Rate], [Order Details Extended].ExtendedPrice, [Order Details Extended].TaxAmount
ORDER BY [Orders Qry].Route, [Orders Qry].RoutePosition;
Thx again...BC
I have an invoice that is printed off daily. The invoice is based on the query below. Now if the client actually has an preorder and this information is filled out in the order form, my invoice prints out fine. My problem is, most of the time a blank invoice with only customers address and routes on the invoice r needed. The driver then fills in the rest of the information. So what they do is open up the orders form pick the customer but leave the order detail blank. How do I get my query to bring in both groups of customers, the ones with the pre-ordered info and the blank ones so that the invoice report can be printed in order of route in one group?
Here is what I have...this gives me pre-ordered customers only. Any help would be gr8. Thx. BC
SELECT [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].EmployeeID, [Orders Qry].OrderDate, [Orders Qry].RequiredDate, [Orders Qry].ShippedDate, [Orders Qry].ShipVia, [Orders Qry].[Tax Total], [Orders Qry].Freight, [Orders Qry].ShipName, [Orders Qry].ShipAddress, [Orders Qry].ShipCity, [Orders Qry].ShipRegion, [Orders Qry].ShipPostalCode, [Orders Qry].ShipCountry, [Orders Qry].Phone, [Orders Qry].Cell, [Orders Qry].CompanyName, [Orders Qry].Address, [Orders Qry].City, [Orders Qry].Region, [Orders Qry].PostalCode, [Orders Qry].Country, [Orders Qry].Route, [Orders Qry].RoutePosition, [Orders Qry].[Delivery Notes], [Order Details Extended].ProductID, [Order Details Extended].ProductName, [Order Details Extended].UnitPrice, [Order Details Extended].Quantity, [Order Details Extended].Returns, [Order Details Extended].[Tax Rate], [Order Details Extended].ExtendedPrice, [Order Details Extended].TaxAmount
FROM [Order Details Extended] INNER JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
GROUP BY [Orders Qry].OrderID, [Orders Qry].CustomerID, [Orders Qry].EmployeeID, [Orders Qry].OrderDate, [Orders Qry].RequiredDate, [Orders Qry].ShippedDate, [Orders Qry].ShipVia, [Orders Qry].[Tax Total], [Orders Qry].Freight, [Orders Qry].ShipName, [Orders Qry].ShipAddress, [Orders Qry].ShipCity, [Orders Qry].ShipRegion, [Orders Qry].ShipPostalCode, [Orders Qry].ShipCountry, [Orders Qry].Phone, [Orders Qry].Cell, [Orders Qry].CompanyName, [Orders Qry].Address, [Orders Qry].City, [Orders Qry].Region, [Orders Qry].PostalCode, [Orders Qry].Country, [Orders Qry].Route, [Orders Qry].RoutePosition, [Orders Qry].[Delivery Notes], [Order Details Extended].ProductID, [Order Details Extended].ProductName, [Order Details Extended].UnitPrice, [Order Details Extended].Quantity, [Order Details Extended].Returns, [Order Details Extended].[Tax Rate], [Order Details Extended].ExtendedPrice, [Order Details Extended].TaxAmount
ORDER BY [Orders Qry].Route, [Orders Qry].RoutePosition;
Thx again...BC