Need help generating sql statement to work with report. 1

[MGrassman]

I have the following tables

[New Kits]
PackageCode | Print_type
45 | simplex letter
45 | simplex letter
45 | simplex letter
48 | duplex letter
48 | duplex letter
48 | duplex letter
50 | simplex legal
50 | simplex legal
50 | simplex legal

[History Table]
LetterCode | Quantity | RunDate | Complete/BO
45 | 3 | 5/20/2005 | C
45 | 5 | 5/21/2005 | C
45 | 1 | 5/25/2005 | C
48 | 13 | 5/23/2005 | C
50 | 2 | 5/2/2005 | C
50 | 8 | 5/17/2005 | C
50 | 5 | 5/12/2005 | C
50 | 7 | 5/30/2005 | C

the outputed report needs to look like below
Simplex Letter 9
Duplex Letter 13
Simplex Legal 22

Is there a way to write a sql statement to place this in a report easily or am I better off just generating a pdf with a component I have.

My main question is, is this doable in sql alone?

Thanks for everyones help in advance.

 

[lespaul]

select print_type, sum(quantity)
From [New Kits]
INNER JOIN [History Table] on [New Kits].LetterCode = [History Table].LetterCode
GROUP BY print_type

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

SELECT Print_type, Sum(Quantity) AS Total
FROM [History Table] AS H INNER JOIN (
SELECT DISTINCT PackageCode, Print_type FROM [New Kits]
) AS K ON H.LetterCode = K.PackageCode
GROUP BY Print_type;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MGrassman]

Thanks everyone for that answer. I used PHV's select statement and it worked great. Now what I'm trying to do is put a where condition on the doCMD.OpenReport method to filter the results by RunDate which is a field in the [History Table] Table. But not used on the report. Is there a way I can just assign a sql statement to the report I'm opening.

Thanks again for everyones help.