The primary keys are:
tblInvoice - strOrderNumber
tblInvoiceDetail - strOrderNumber
tblCustomerShipTo - strShipID & strCustomerID
tblWarehouse - strWarehouseID
Here is the SQL from the query.
SELECT tblInvoice.strOrderNumber, tblInvoice.strCustomerID,
tblInvoice.strShipToCompany, tblInvoice.strShipToAddress,
[tblInvoice.strShipToCity] & " " & [tblInvoice.strShipToState] & " " &
[tblInvoice.strShipToZip] AS CityAdd, tblInvoice.strShipVia,
Sum(IIf(([tblInvoiceDetail.strProductID]) Like
"14790L",[dblQtyShipped]/12,IIf([tblInvoiceDetail.strProductID] Like
"GTS","1"*"1",[dblQtyShipped]))) AS PkgCt, tblInvoice.dtmDateShipped,
tblInvoice.strTerms, FormatCurrency(IIf(([tblInvoice.strTerms]) Like
"*c.o.d.",[curOrderTotal],0)) AS OrderTotal,
IIf(([tblInvoice.strShipToPhoneNumber]) Is
Null,[strBillToPhoneNumber],[tblInvoice.strShipToPhoneNumber]) AS Phone,
Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0))) AS Hazard,
tblInvoice.strCustomerID, IIf(([tblCustomerShipTo.Notes]) Is
Null,"",([tblCustomerShipTo.Notes])) AS [Ship Notes], IIf([strBillToCountry]
Is Null,"USA",([strBillToCountry])) AS Country, tblInvoice.strCustomerPO AS
Purchase, tblWarehouse.strWarehouseID, tblWarehouse.strAddress AS WareAdd,
[tblWarehouse.strCity] & " " & [tblWarehouse.strState] & " " &
[tblWarehouse.strZip] AS WareCityAdd
FROM (tblInvoice LEFT JOIN tblCustomerShipTo ON (tblInvoice.strCustomerID =
tblCustomerShipTo.strCustomerID) AND (tblInvoice.strShipID =
tblCustomerShipTo.strShipID)) LEFT JOIN (tblWarehouse RIGHT JOIN
tblInvoiceDetail ON tblWarehouse.strWarehouseID =
tblInvoiceDetail.strWarehouseID) ON tblInvoice.strOrderNumber =
tblInvoiceDetail.strOrderNumber
GROUP BY tblInvoice.strOrderNumber, tblInvoice.strCustomerID,
tblInvoice.strShipToCompany, tblInvoice.strShipToAddress,
[tblInvoice.strShipToCity] & " " & [tblInvoice.strShipToState] & " " &
[tblInvoice.strShipToZip], tblInvoice.strShipVia, tblInvoice.dtmDateShipped,
tblInvoice.strTerms, FormatCurrency(IIf(([tblInvoice.strTerms]) Like
"*c.o.d.",[curOrderTotal],0)), IIf(([tblInvoice.strShipToPhoneNumber]) Is
Null,[strBillToPhoneNumber],[tblInvoice.strShipToPhoneNumber]),
tblInvoice.strCustomerID, IIf(([tblCustomerShipTo.Notes]) Is
Null,"",([tblCustomerShipTo.Notes])), IIf([strBillToCountry] Is
Null,"USA",([strBillToCountry])), tblInvoice.strCustomerPO,
tblWarehouse.strWarehouseID, tblWarehouse.strAddress, [tblWarehouse.strCity]
& " " & [tblWarehouse.strState] & " " & [tblWarehouse.strZip]
HAVING (((tblInvoice.strOrderNumber) Like "*" & [Enter Invoice Number]) AND
((tblInvoice.strShipVia) Not In ("UPS Ground","Fed Ex Ground","UPS
Collect","UPS 2ND DAY","Fed Ex 2Nd Day","UPS Standard","UPS Next Day","UPS 3
Day","U.S. Mail","Federal Express","FED EX OVERNIGHT","Fed Ex 3Rd Day","FED
EX NEXT DAY","Parcel Post","Priority Mail")))
ORDER BY tblInvoice.dtmDateShipped DESC;
Thanks
Jim
Jerry Whittle said:
What you posted can't add up to 3 therefore be your problem. You need to
post
the entire SQL statement. If the SQL statement is based on another query,
we
need that too. I'm betting that you have a totals query summing up the 1s
and
2s.
A nice touch would be to include the names of the Primary Keys on any
tables
used in the query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Jim said:
Here you go.
Hazard: Sum(IIf([tblInvoiceDetail.strProductID] Like "14790L" Or
[tblInvoiceDetail.strProductID] Like
"*sbg*",1,IIf([tblInvoiceDetail.strProductID] Like "5gwb" Or
[tblInvoiceDetail.strProductID] Like "gpc",2,0)))
Interesting problem, but please copy and paste the SQL.
Thanks,
Sam
Jim wrote:
Hello,
I have an Access 2003 query that changes descriptions for a bill of
lading
based on the hazardous content. For example "0 " is not, "1" is
hazardous
and "2" is hazardous, but with a different description. The problem I'm
having is that if I have two different items on the bill that are
hazardous;
instead of writing the description for each, it groups them together
then
adds them. What I need is:
Product One "1" hazardous description
Product Two "2" hazardous description
What I get is:
Product One "3" no description
Product Two "3" no description
Thanks for any help.
Jim
