INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MS Access query, ? Sum Issue

MS Access query, ? Sum Issue

(OP)
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

RE: MS Access query, ? Sum Issue

Hi,

Please post your query SQL.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access query, ? Sum Issue

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

RE: MS Access query, ? Sum Issue

(OP)
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;

RE: MS Access query, ? Sum Issue

(OP)
Thanks to both of you for your time.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close