Code:
INSERT INTO tracking_log ( Client_Code, Carrier_Code, Mode, Account_Number, Bill_Type, Invoice_Number, Net_Charges, Tracking_Number, Sender_Company_Name, Sender_Name, Sender_Address_1, Sender_Address_2, Sender_City, Sender_State, Sender_Postal, Sender_Country, Receiver_Company_Name, Receiver_Name, Receiver_Address_1, Receiver_Address_2, Receiver_City, Receiver_State, Receiver_Postal, Receiver_Country, Ship_Date, Service, Tracking_Service_Code, Saturday_Delivery, Additional_Handling_Weight, Additional_Handling_Size, Additional_Handling_Longest_Side, Residential, Zone_Code, Package_Type, Ship_Weight, Bill_Weight )
SELECT cca.client_code AS Client_Code, cca.carrier_code AS Carrier_Code, service_codes.mode AS Mode, sd1.account_number AS Account_Number, sd1.bill_option_code AS Bill_Type, sd1.invoice_number AS Invoice_Number, charges.net_charges AS Net_Charges, sd1.tracking_number AS Tracking_Number, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_name,sd1.sender_company_name) AS Sender_Company_Name, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,'',sd1.sender_name) AS Sender_Name, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_address_1,sd1.sender_address_line_1) AS Sender_Address_1, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_address_2,sd1.sender_address_line_2) AS Sender_Address_2, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_city,sd1.sender_city) AS Sender_City, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_state,sd1.sender_state) AS Sender_State, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_zip,sd1.sender_postal) AS Sender_Postal, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_country,sd1.sender_country_territory) AS Sender_Country, sd1.receiver_company_name AS Receiver_Company_Name, sd1.receiver_name AS Receiver_Name, sd1.receiver_address_line_1 AS Receiver_Address_1, sd1.receiver_address_line_2 AS Receiver_Address_2, sd1.receiver_city AS Receiver_City, sd1.receiver_state AS Receiver_State, sd1.receiver_postal AS Receiver_Postal, sd1.receiver_country_territory AS Receiver_Country, Format(sd1.transaction_date,'mm/dd/yyyy') AS Ship_Date, service_codes.universal_description AS Service, service_codes.tracking_service_code AS Tracking_Service_Code, ACC_Flags.sat AS Saturday_Delivery, ACC_Flags.ahw AS Additional_Handling_Weight, ACC_Flags.ahs AS Additional_Handling_Size, ACC_Flags.ahl AS Additional_Handling_Longest_Side, ACC_Flags.res AS Residential, sd1.zone AS Zone_Code, sd1.container_type AS Package_Type, sd1.entered_weight AS Ship_Weight, sd1.billed_weight AS Bill_Weight
FROM (((shipment_details AS sd1 LEFT JOIN service_codes ON sd1.charge_description_code = service_codes.service_code) LEFT JOIN carrier_client_accounts AS cca ON (sd1.Account_Number = cca.ACCOUNT_NUMBER) AND (sd1.Recipient_Number = cca.MASTER_ACCOUNT_NUMBER)) LEFT JOIN sat_ahw_ahs_resi AS ACC_Flags ON (sd1.Invoice_Number = ACC_Flags.invoice_number) AND (sd1.Tracking_Number = ACC_Flags.tracking_number)) LEFT JOIN (SELECT tracking_number,invoice_number,
Sum(net_amount) AS Net_Charges
FROM shipment_details AS xxx
WHERE xxx.charge_classification_code NOT IN ( 'FSC' )
GROUP BY tracking_number,invoice_number) AS charges ON (sd1.Tracking_Number = charges.tracking_number) AND (sd1.Invoice_Number = charges.invoice_number)
WHERE (((sd1.charge_classification_code) Not In ('FSC','INF','ACC','BRK','GOV')) AND ((sd1.charge_category_detail_code) Not In ('IBS','PSUP','HAZ','MISC','FPOD','CADJ','RADJ','SCC','OCA','OCG','DCON','CTGR','AGSR','GSR','FEES','VOID','SVCH','ADC','ADJ','DIN','ZONE','PDP','SDS')));
Would it be possible to have a temp table as an intermediary table, i.e: