Same part number multiple quantities totals 2

[netrusher]

I have a table with Part Numbers and a Qty. The Part Number can be in the table multiple times with different
quantity's. What I would like is to have a query that will show the Part Number one time and total of all the
Different Quantity's. I am stumped. How can I total up the Quantity for each part number and show the
Part Number once with the total Quantity for that Part Number? Thanks for all help.

 

[netrusher]

I forgot to put in my original post that this is based on a date range. When I add the date range it gives me different
sums for each different date. I need one sum no matter how
many dates.

 

[SkipVought]

Hi,

Select [part number], sum([quantity])
From [your table]
Group by [part number]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

Skip, I have two tables linked together as tblReqdParts has
the Part numbers and MRB Table has the Date and Qty Fields.
Sorry, I left this info off.

 

[SkipVought]

Then add the other table and join them on the proper key(s).

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[netrusher]

That is where I am lost with SQL.

I did try this though. I created a query:

SELECT tblReqdParts.PART, MRP.Qty
FROM tblReqdParts LEFT JOIN MRP ON tblReqdParts.PART = MRP.PartNumber
GROUP BY tblReqdParts.PART, MRP.Qty, MRP.Date
HAVING (((MRP.Date) Between "2009-06-08" And "2009-07-20"));

Then I created the query below and it works but I have
three Part Numbers with 0 Qty and I need those Part Numbers
to show with a Qty of 0. Any Suggestions?

SELECT BucketPartNumberQty.PART, Sum(BucketPartNumberQty.Qty) AS Qty
FROM BucketPartNumberQty
GROUP BY BucketPartNumberQty.PART;

 

[SkipVought]

Hi,

Your date criteria filters out the parts that have no qty.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I would try something like:

SELECT tblReqdParts.PART, Sum(MRP.Qty) as MRPQty
FROM tblReqdParts LEFT JOIN MRP ON tblReqdParts.PART = MRP.PartNumber
WHERE MRP.Date Between "2009-06-08" And "2009-07-20" OR MRP.Date Is Null
GROUP BY tblReqdParts.PART;

Duane
Hook'D on Access
MS Access MVP