Inconsequent results after two simple queries

[Scaniafan]

OK, I'm probably making the most rookie mistake, but I can't get a grip on where it goes wrong.

I've got the following query, which basically shows the shipments where I've got a reason code being LD**** or Null.

SELECT QRY_Gross_Performance_4.[Shipment Number], QRY_Gross_Performance_4.[Delivery Gross], IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",[TBL_Master_Reason_Code_Per_Shipment]![Reasoncode]) AS [Reasoncode - Delivery], IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",[TBL_Source_Reason_Codes]![Reason code - resp party]) AS [Responsible Party - Delivery], IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",[TBL_Source_Reason_Codes]![Reason code - standardized error]) AS [Error - Delivery], IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",IIf([QRY_Gross_Performance_4]![Delivery Gross]<>"Delivery On Time",IIf([TBL_Source_Reason_Codes]![Reason code - resp party]="Carrier / Forwarder","Accountable","Non-accountable"),"Non-accountable")) AS [Delivery Net Forwarder], IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",IIf([QRY_Gross_Performance_4]![Delivery Gross]<>"Delivery On Time",IIf([TBL_Source_Reason_Codes]![Reason code - resp party]="Office","Accountable","Non-accountable"),"Non-accountable")) AS [Delivery Net Office], IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",IIf([QRY_Gross_Performance_4]![Delivery Gross]<>"Delivery On Time",IIf([TBL_Source_Reason_Codes]![Reason code - resp party]="Customer","Accountable","Non-accountable"),"Non-accountable")) AS [Delivery Net Customer]
FROM TBL_Source_Reason_Codes RIGHT JOIN (QRY_Gross_Performance_4 LEFT JOIN TBL_Master_Reason_Code_Per_Shipment ON QRY_Gross_Performance_4.[Shipment Number] = TBL_Master_Reason_Code_Per_Shipment.[Shipment Number]) ON TBL_Source_Reason_Codes.[Reason code - code] = TBL_Master_Reason_Code_Per_Shipment.Reasoncode
WHERE (((IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",[TBL_Master_Reason_Code_Per_Shipment]![Reasoncode])) Is Null Or (IIf([QRY_Gross_Performance_4]![Delivery Gross]="","",[TBL_Master_Reason_Code_Per_Shipment]![Reasoncode])) Like "LD****"));

In the results, I've got (for example) a shipment number 1201368 which pops up as follows:

Shipment Number: 1201368
Delivery Gross: Delivery On Time
Reasoncode - Delivery: Null
Responsible Party -Delivery: Null
Error - Delivery: Null
Delivery Net Forwarder: Non-accountable
Delivery Net Office: Non-accountable
Delivery Net Customer: Non-accountable

Based on that query, I run te next query

SELECT TBL_Master_CP203.[Shipment Number], TBL_Master_CP203.[Departure Date Plann], TBL_Master_CP203.[Departure Date], TBL_Master_CP203.[Planned date for end], TBL_Master_CP203.[Actual Date for END], TBL_Master_CP203.[Actual Time for END], TBL_Master_CP203.[POD Date], TBL_Master_CP203.[POD Time], TBL_Master_CP203.[Forwarding agent], QRY_Gross_Performance_4.[Collection Gross], QRY_Net_Performance_Collection.[Reasoncode - Collection], QRY_Net_Performance_Collection.[Responsible Party - Collection], QRY_Net_Performance_Collection.[Error - Collection], QRY_Net_Performance_Collection.[Collection Net Forwarder], QRY_Net_Performance_Collection.[Collection Net Office], QRY_Net_Performance_Collection.[Collection Net Customer], QRY_Gross_Performance_4.[Delivery Gross], QRY_Net_Performance_Delivery.[Reasoncode - Delivery], QRY_Net_Performance_Delivery.[Responsible Party - Delivery], QRY_Net_Performance_Delivery.[Error - Delivery], QRY_Net_Performance_Delivery.[Delivery Net Forwarder], QRY_Net_Performance_Delivery.[Delivery Net Office], QRY_Net_Performance_Delivery.[Delivery Net Customer], QRY_Gross_Performance_4.[POD Gross], QRY_Net_Performance_POD.[Reasoncode - POD], QRY_Net_Performance_POD.[Responsible Party - POD], QRY_Net_Performance_POD.[Error - POD], QRY_Net_Performance_POD.[POD Net Forwarder], QRY_Net_Performance_POD.[POD Net Office], QRY_Net_Performance_POD.[POD Net Customer]
FROM (((TBL_Master_CP203 LEFT JOIN QRY_Net_Performance_Delivery ON TBL_Master_CP203.[Shipment Number] = QRY_Net_Performance_Delivery.[Shipment Number]) LEFT JOIN QRY_Net_Performance_Collection ON TBL_Master_CP203.[Shipment Number] = QRY_Net_Performance_Collection.[Shipment Number]) LEFT JOIN QRY_Net_Performance_POD ON TBL_Master_CP203.[Shipment Number] = QRY_Net_Performance_POD.[Shipment Number]) LEFT JOIN QRY_Gross_Performance_4 ON TBL_Master_CP203.[Shipment Number] = QRY_Gross_Performance_4.[Shipment Number];

However, in the result of this query, for the same shipment 1201368, the fields Delivery Net Forwarder, Delivery Net Office and Delivery Net Customer are Null.

I can't seem to find the error in my queries / criteria causing this, as to me it seems like the second query simply pulls the results out of the first query, where the results are correct. :banghead:

Thanks for helping me!

 

[CJ_London]

your code is very difficult to read (at least to someone who is not familiar with the code) - spaces in names, very long names, no layout

Suggest you separate out the two comparative bits of code and lay them out in a readable way - use multiple lines and indenting

Also, going forward consider using aliasing -e.g. instead of using

QRY_Gross_Performance_4

alias as Q4

it will make code much easier to read

 

[robertbwainwright]

First I would check in QRY_Net_Performance_Delivery to see if you have duplicate transactions on 1201368. I would simply run the query with 1201368 as criteria.

I must say that I agree with CJ, you need to post code in a way that is readable. Here is how I write my SQL:

SELECT 
 TBL_Master_CP203.[Shipment Number], 
 TBL_Master_CP203.[Departure Date Plann], 
 TBL_Master_CP203.[Departure Date], 
 TBL_Master_CP203.[Planned date for end], 
 TBL_Master_CP203.[Actual Date for END], 
 TBL_Master_CP203.[Actual Time for END], 
 TBL_Master_CP203.[POD Date], 
 TBL_Master_CP203.[POD Time], 
 TBL_Master_CP203.[Forwarding agent], 
 QRY_Gross_Performance_4.[Collection Gross], 
 QRY_Net_Performance_Collection.[Reasoncode - Collection], 
 QRY_Net_Performance_Collection.[Responsible Party - Collection], 
 QRY_Net_Performance_Collection.[Error - Collection], 
 QRY_Net_Performance_Collection.[Collection Net Forwarder], 
 QRY_Net_Performance_Collection.[Collection Net Office], 
 QRY_Net_Performance_Collection.[Collection Net Customer], 
 QRY_Gross_Performance_4.[Delivery Gross], 
 QRY_Net_Performance_Delivery.[Reasoncode - Delivery], 
 QRY_Net_Performance_Delivery.[Responsible Party - Delivery], 
 QRY_Net_Performance_Delivery.[Error - Delivery], 
 QRY_Net_Performance_Delivery.[Delivery Net Forwarder], 
 QRY_Net_Performance_Delivery.[Delivery Net Office], 
 QRY_Net_Performance_Delivery.[Delivery Net Customer], 
 QRY_Gross_Performance_4.[POD Gross], 
 QRY_Net_Performance_POD.[Reasoncode - POD], 
 QRY_Net_Performance_POD.[Responsible Party - POD], 
 QRY_Net_Performance_POD.[Error - POD], 
 QRY_Net_Performance_POD.[POD Net Forwarder], 
 QRY_Net_Performance_POD.[POD Net Office], 
 QRY_Net_Performance_POD.[POD Net Customer]
FROM 
 ((TBL_Master_CP203 
  LEFT JOIN 
   QRY_Net_Performance_Delivery 
  ON 
   TBL_Master_CP203.[Shipment Number] = QRY_Net_Performance_Delivery.[Shipment Number]) 
  LEFT JOIN 
   QRY_Net_Performance_Collection 
  ON 
   TBL_Master_CP203.[Shipment Number] = QRY_Net_Performance_Collection.[Shipment Number]) 
  LEFT JOIN 
   QRY_Net_Performance_POD 
  ON 
   TBL_Master_CP203.[Shipment Number] = QRY_Net_Performance_POD.[Shipment Number]) 
  LEFT JOIN 
   QRY_Gross_Performance_4 
  ON 
   TBL_Master_CP203.[Shipment Number] = QRY_Gross_Performance_4.[Shipment Number];

 

[Scaniafan]

your code is very difficult to read (at least to someone who is not familiar with the code) - spaces in names, very long names, no layout

Suggest you separate out the two comparative bits of code and lay them out in a readable way - use multiple lines and indenting

Also, going forward consider using aliasing -e.g. instead of using

QRY_Gross_Performance_4

alias as Q4

I'll certainly look in to this, as you say, I'm familiar with this code of course. I never heard of the aliasing. Worth looking in to.

First I would check in QRY_Net_Performance_Delivery to see if you have duplicate transactions on 1201368. I would simply run the query with 1201368 as criteria.

This returns only one line, with the correct entry in the fields.

 

[CJ_London]

I never heard of the aliasing

bet you use it all the time without realising it

Have you never summed/averaged in a group by query?

sum(myField) AS SumOfmyField

and changed to

sum(myField) AS Total

SumOfmyField is an alias and it is changed to something more readable/meaningful