Summing non-existent fields

[miconian]

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:

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];

 

[Norie]

Are you sure you've got the join right?

It should be set to have all the order IDs in the table that doesn't have the problem ID and only those that match from the other table.

So the problem ID wouldn't appear in the results of the query.

 

[miconian]

I was oversimplifying in my explanation. Actually, it is one combination of order ID and line number that is in one table, but not the other. And yes, I'm sure the problem is in the join.

I think what I'll do is create a new concatenated orderID + linenumber field in each table, and then do the join on that.

 

[Norie]

Well I hope that works, but I can't help wondering why you are concatenating.

Is it just for this query?

 

[miconian]

Well, yes, it's just for this query.

I'm concatenating in order to create an easy way for me to connect the combo of order ID and line number in one table, with the same combo in the other table. If you want to tell me about a better way, that's great too.

 

[Norie]

So the join is on the concatenated field(s)?

That will work but can be a bit slow if dealing with a lot of records.