Group query - Access XP

[Newbie]

I have two related tables - 1 has invoices the other has the structure for
the part invoiced. I am trying to find the amount of raw material used by
stockcode that has been invoiced in a given period

Invoice Table
Invoice - PK
StockCode - Join Field
QtyInvoiced
InvoiceDate

Structure Table
ParentPart - Join Field and PK
Component - PK
SeqNum - PK
QtyPer

I want to find the QtyInvoiced * QtyPer Where SeqNum = "A1" and InvoiceDate
= Between 1/8/02 and 31/7/03 Grouped By StockCode.

I have tried lots of ways of doing this but it always seems to give me
double the amount for Qty Invoiced compared to what the answer should be

Here is what I have to get the total of Qty Invoiced: - where am I going
wrong and how do I fit QtyPer into this without getting the message about
not included in aggregate function?

SELECT DISTINCT Sum(Invoice.QtyInvoiced) AS SumOfQtyInvoiced,
Structure.ParentPart
FROM Invoice INNER JOIN Structure ON Invoice.StockCode =
Structure.ParentPart
WHERE (((Structure.SeqNum)="A1") AND ((Invoice.InvoiceDate) Between
#8/1/2002# And #7/31/2003#))
GROUP BY Structure.ParentPart
HAVING (((Structure.ParentPart)="XYZ"));

Thanks

 

[HSalim]

Select Invoice, StockCode, Sum(qtyInvoiced) as SumQty
From invoice
Where InvoiceDate Between 1/8/02 and 31/7/03
group by Invoice, StockCode
will give you the sum of quantity invoiced per invoice, by Stock code


Select I.Invoice, I.Stockcode, I.SumQty * S.QtyPer as SumXQtyPer
from Structure S InnerJoin [
Select Invoice, StockCode, Sum(qtyInvoiced) as SumQty
From invoice
Where InvoiceDate Between 1/8/02 and 31/7/03
group by Invoice, StockCode]. as I
on I.StockCode = S.ParentPart
Where S.ParentPart ="XYZ"
and S.SeqNum="A1"

HS