MS Access query, ? Sum Issue

[alan1203]

Hi,

I have a simple Access query that Sums the number of items received and the number of items used, then uses an expression to calculate 'Received' - 'Used'.

The table that contains this data just has 'Date', 'PartNumber', 'Received', 'Used', 'OrderNumber' and 'Comments' fields.

I populate this table using the same form used as a subform in 3 different main forms.

My problem is that when I run the query, the same part number is shown 2 times, almost as if it is 2 separate numbers. When I go into the datasheet view on the query the two part numbers look the same.

I apologise if this is not a query problem and any help will be appreciated.

Alan

 

[SkipVought]

Hi,

Please post your query SQL.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

This is a query forum so it is recommended that you provide the SQL view of your query.

Duane
Hook'D on Access
MS Access MVP

 

[alan1203]

Thanks guys, here is the SQL

SELECT tblParts.PartDescription, tblParts.PartNumber, tblParts.Price, tblParts.Drawer, tblParts.Compartment, Sum(tblUsedRecd.Received) AS SumOfReceived, Sum(tblUsedRecd.Used) AS SumOfUsed, [SumOfReceived]-[SumOfUsed] AS Expr1, tblParts.[Do Not Re-order], tblParts.[For Info Only], tblParts.[Discontinued by Supplier], tblUsedRecd.Comments
FROM tblParts INNER JOIN tblUsedRecd ON tblParts.PartNumber = tblUsedRecd.PartNumber
GROUP BY tblParts.PartDescription, tblParts.PartNumber, tblParts.Price, tblParts.Drawer, tblParts.Compartment, tblParts.[Do Not Re-order], tblParts.[For Info Only], tblParts.[Discontinued by Supplier], tblUsedRecd.Comments
HAVING (((tblParts.Drawer)>"0"))
ORDER BY tblParts.Drawer, tblParts.Compartment;

 

[dhookom]

I expect you only need to remove the comments field from the group by.

Duane
Hook'D on Access
MS Access MVP

 

[alan1203]

Thanks to both of you for your time.