Solved Creating DSum fields on Report

[hbrehmer]

Hello,
I am writing a report based on a query where I need to sum the quantity of a part based on a criteria (type of transaction). The DSum expression requires that I look at the query used, instead of the detail of the record in the report and my answer returned on the report is totaling all records in the query based on the criteria, not the individual record (part number).

I have tried the follow, but keep getting errors:

=DSum("[Quantity]","TEST1QRY", "[TEST1QRY]![Part Number] = [Inventory Transactions].Report![Part Number_Inventory]", And "[Transaction Type]![ID] = 1")

Please help!

Heidi

 

[jdraw]

Please post the SQL of your query.

Query design ->SQL view->copy and Paste into your post.

Welcome to the forum.

 

[hbrehmer]

SELECT [Inventory Transactions].*, [Transaction Type].ID, Inventory.Customer
FROM Inventory INNER JOIN ([Transaction Type] INNER JOIN [Inventory Transactions] ON [Transaction Type].ID = [Inventory Transactions].[Transaction Type]) ON Inventory.ID = [Inventory Transactions].[Part Number];


Sorry, I should have sent this along, as well.

Thanks for the welcome.

 

[Pat Hartman]

DSum() is a domain function. It operates on the ENTIRE domain so if you want only some of the set to be included, you need to include criteria.

For a report, you should be using sorting and grouping options and therefore Sum() rather than DSum() to summarize data.