Stuck on an update to a Query

M

mike17316

I have a current query and the user is requesting an change by adding only
Event.Status that are "Open", only for the StartCount records. I can't seem
to get it right. Thank you for the help!

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event
Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND
[Received Date] < #" & [Enter Start Date] & "#") AS StartCount,

Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd,

DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority]
= '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND
[Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed

FROM Event

WHERE (((Event.[Received Date])>=[Enter Start Date] And
(Event.[Received Date])<[Enter End Date]+1) AND
((Event.[Event Reference]) Is Not Null)) OR
(((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is
Not Null))

GROUP BY Event.[Event Type], Event.Priority;
 
J

John W. Vinson

I have a current query and the user is requesting an change by adding only
Event.Status that are "Open", only for the StartCount records. I can't seem
to get it right. Thank you for the help!

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event
Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND
[Received Date] < #" & [Enter Start Date] & "#") AS StartCount,

Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd,

DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority]
= '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND
[Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed

FROM Event

WHERE (((Event.[Received Date])>=[Enter Start Date] And
(Event.[Received Date])<[Enter End Date]+1) AND
((Event.[Event Reference]) Is Not Null)) OR
(((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is
Not Null))

GROUP BY Event.[Event Type], Event.Priority;
Click to expand...

Could you explain the problem? What do you mean by "the StartCount records" -
StartCount is a calculated field; what do you want to do with that field?
 
M

mike17316

Hi John,

For the calculted field of StartCount, I need to include only those records
that have an Event.Status of "open". Currently it is retrieving all records,
regardless of the Status, but I opnly want to count the Open records.
Thanks
Mike



John W. Vinson said:
I have a current query and the user is requesting an change by adding only
Event.Status that are "Open", only for the StartCount records. I can't seem
to get it right. Thank you for the help!

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event
Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND
[Received Date] < #" & [Enter Start Date] & "#") AS StartCount,

Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd,

DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority]
= '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND
[Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed

FROM Event

WHERE (((Event.[Received Date])>=[Enter Start Date] And
(Event.[Received Date])<[Enter End Date]+1) AND
((Event.[Event Reference]) Is Not Null)) OR
(((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is
Not Null))

GROUP BY Event.[Event Type], Event.Priority;
Click to expand...

Could you explain the problem? What do you mean by "the StartCount records" -
StartCount is a calculated field; what do you want to do with that field?
Click to expand...
 
J

John W. Vinson

Hi John,

For the calculted field of StartCount, I need to include only those records
that have an Event.Status of "open". Currently it is retrieving all records,
regardless of the Status, but I opnly want to count the Open records.
Thanks
Mike
Click to expand...

Then just include that criterion in the DCount function calls:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event
Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND
[Received Date] < #" & [Enter Start Date] & "# AND [Status] = 'Open'") AS
StartCount,

Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd,

DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority]
= '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND
[Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed

FROM Event

WHERE (((Event.[Received Date])>=[Enter Start Date] And
(Event.[Received Date])<[Enter End Date]+1) AND
((Event.[Event Reference]) Is Not Null)) OR
(((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is
Not Null))

GROUP BY Event.[Event Type], Event.Priority;

Note that I'm using ' rather than " as a delimiter for the 'Open' criterion,
since the string is already within " delimiters.
 
M

mike17316

Thanks!


John W. Vinson said:
Hi John,

For the calculted field of StartCount, I need to include only those records
that have an Event.Status of "open". Currently it is retrieving all records,
regardless of the Status, but I opnly want to count the Open records.
Thanks
Mike
Click to expand...

Then just include that criterion in the DCount function calls:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Event.[Event Type], Event.Priority, DCount("*","[Event]","[Event
Type] = '" & [Event Type] & "' AND [Priority] = '" & [Priority] & "' AND
[Received Date] < #" & [Enter Start Date] & "# AND [Status] = 'Open'") AS
StartCount,

Sum(IIf(Event.Status="Open",1,0)) AS NbrRecd,

DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority]
= '" & [Priority] & "' AND [Close Date] < #" & [Enter End Date]+1 & "# AND
[Close Date] >= #" & [Enter Start Date] & "#") AS NbrClosed

FROM Event

WHERE (((Event.[Received Date])>=[Enter Start Date] And
(Event.[Received Date])<[Enter End Date]+1) AND
((Event.[Event Reference]) Is Not Null)) OR
(((Event.[Received Date])>#12/31/2099#) AND ((Event.[Event Reference]) Is
Not Null))

GROUP BY Event.[Event Type], Event.Priority;

Note that I'm using ' rather than " as a delimiter for the 'Open' criterion,
since the string is already within " delimiters.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add condition to current query 0
New to Access and in a pinch for a report 4
Query/Report Help for Date Difference 8
how to update ALL child records 2
DMax on existing record to increment serialized # fails 1
Parameter Queries 1
Query for a report 1
Parameter Query with DSum 2

Top