Counting with multiple criteria

[Guest]

Hello,
First I use access 2003, I am trying to create a make table Query that
will the following characteristics

Col 1 Col 2 Col 3 Col 4
Col 5 Col 6
Total Grp count sum first
first First
criteria None, none, none, =a and is not null, >b and<c, <>d

Basically I want it to group column 1, count column 2, and sum column 3.
Column 4,5,6 are the conditions I want met for a record to be counted and
summed.

Problem - the count and sum function are not being restricted by the
criteria in columns 4,5 and 6. So if I had 10 total records (sum = 100), and
only 5 (sum = 50) met all the criteria the count function gives me 10 (sum =
100). I want it to give me 5 (sum = 50) what am i doing wrong (note I have
all the criteria listed on one row)

 

[Douglas J. Steele]

What's the actual SQL of your query?

 

[Guest]

Here is the SQL.

SELECT DMR.Vendor, Count(DMR.[dmr number]) AS [CountOfdmr number],
Sum(DMR.[Qty Reject]) AS [SumOfQty Reject], First(DMR.[Responsibility 1]) AS
[FirstOfResponsibility 1], First(DMR.[Date Received]) AS [FirstOfDate
Received], First(DMR.[DMR Source]) AS [FirstOfDMR Source] INTO [Vendor ENCMRS
Open]
FROM DMR
GROUP BY DMR.Vendor
HAVING (((First(DMR.[Responsibility 1]))="vendor" And
(First(DMR.[Responsibility 1])) Is Not Null) AND ((First(DMR.[Date
Received]))>=[beginning Time Frame?] And (First(DMR.[Date Received]))<=[end
of Time Frame?]) AND ((First(DMR.[DMR Source]))<>"RG"))
ORDER BY Count(DMR.[dmr number]) DESC;

Thanks

 

[Guest]

I am a little new to SQL so I was trying to design this using the design view
and I think that is where my problems were. I Powered through the SQL books
and came up with this

SELECT * INTO [TVendor report Open]
FROM [SELECT
[dmr].Vendor,

count(iif(([dmr].[Date Received] >= [What is Start Date?] and [dmr].[Date
Received] <= [What is End Date?]), [dmr].[dmr number],null)) as ENCMRs,
sum(iif(([dmr].[Date Received] >= [What is Start Date?] and [dmr].[Date
Received] <= [What is End Date?]), [dmr].[Qty Reject],null)) as QTY

FROM [dmr]

WHERE
[dmr].[Responsibility 1] = "Vendor" and [dmr].[Responsibility 1] is not null
AND [dmr].[DMR Source] <>"RG"

group by
[dmr].vendor

]. AS data
ORDER BY data.vendor;


Seem to work. So any tips would be appreciated but as far as the query goes
I solved my problem. Thanks for the help