I have a query that is returning numbers for a sum operation, when there is actually nothing to sum for that field.
I have two tables, basically like this:
Estimates Table:[orderID, estimates dollars, line number]
Accruals Table: [orderID, accrual dollars, line number]
The tables are linked by order ID and line number.
I have a query where I make a new table, which looks like this:
Big Table: [orderID, line number, estimates dollars, actual dollars]
The problem is that there is one orderID that appears in the estimates table, but not in the accruals table.
I thought that when I ran the query, that problem order would show up in the big table, with zero accrual dollars. But instead it showed up in the big table with twice the estimates dollars represented as the accrual dollars. I'm pretty confused about what happened.
Here is the SQL if it helps:
I have two tables, basically like this:
Estimates Table:[orderID, estimates dollars, line number]
Accruals Table: [orderID, accrual dollars, line number]
The tables are linked by order ID and line number.
I have a query where I make a new table, which looks like this:
Big Table: [orderID, line number, estimates dollars, actual dollars]
The problem is that there is one orderID that appears in the estimates table, but not in the accruals table.
I thought that when I ran the query, that problem order would show up in the big table, with zero accrual dollars. But instead it showed up in the big table with twice the estimates dollars represented as the accrual dollars. I'm pretty confused about what happened.
Here is the SQL if it helps:
Code:
SELECT [sales-rep-rev].[Capture ID], [sales-rep-rev].[Campaign Descr], [sales-rep-rev].[Internet Site], [sales-rep-rev].[Order Line], Sum([sales-rep-rev].[Net Amount]) AS [SumOfNet Amount], Sum(VOA.[Accrual Amount]) AS [SumOfAccrual Amount], VOA.[Dart Order Id], VOA.[Dart Ad Id], VOA.[Sales Planner], [sales-rep-rev].[Capture ID] & " " & [Campaign Descr] & " " & [Dart Order Id] AS [CID-Descr], [sales-rep-rev].[Order Line] & " " & [Dart Ad Id] & " " & [sales-rep-rev].[Product Description] AS [LineNum-AdNum], VOA.[Billing Method], VOA.[Dart Delivered Qty], IIf([VOA].[Billing Method]="CONT" And [VOA].[Dart Delivered Qty]=0,"Contracted No Delivery","Normal") AS Manifest, VOA.[Sales Associate], [sales-rep-rev].[Product Description] INTO [accrual-pivot-base-jan-2012]
FROM [sales-rep-rev] INNER JOIN VOA ON ([sales-rep-rev].[Order Line] = VOA.[Order Line]) AND ([sales-rep-rev].[Capture ID] = VOA.[Capture ID])
GROUP BY [sales-rep-rev].[Capture ID], [sales-rep-rev].[Campaign Descr], [sales-rep-rev].[Internet Site], [sales-rep-rev].[Order Line], VOA.[Dart Order Id], VOA.[Dart Ad Id], VOA.[Sales Planner], [sales-rep-rev].[Capture ID] & " " & [Campaign Descr] & " " & [Dart Order Id], [sales-rep-rev].[Order Line] & " " & [Dart Ad Id] & " " & [sales-rep-rev].[Product Description], VOA.[Billing Method], VOA.[Dart Delivered Qty], IIf([VOA].[Billing Method]="CONT" And [VOA].[Dart Delivered Qty]=0,"Contracted No Delivery","Normal"), VOA.[Sales Associate], [sales-rep-rev].[Product Description];