Basic statistical query

[emueller]

If I can break through this initial problem, I should be on track to complete my project... but my Access newbie thought process is not helping me!

Here is what I'm working on doing. I am trying to make a query that will average the number of days to complete a process for records in my table (ATDays) that meet a certain parameter (in this case, a "Yes/No" block entitled "Holding" is checked).

I know that I need to average a DateDiff function, but continuing that the one step farther is frustrating me to no end.

I've got the

ATDays: Avg(DateDiff("d",[NUI Date],Date()))

but adding something to the effect of WHERE [Holding] = "Yes" has me stumped.

Thank you for your help to what I'm cetain must be a very easy solution!

 

[MajP]

If it is a yes/no boolean field, your criteria is True without quotes. "Yes" is a string value.

Where Holding = true
or
Where Holding = -1

in Access true = -1, false = 0

 

[MichaelRed]

in Access, True <> 0



MichaelRed

 

[emueller]

Thank you very much. That bit of information will help with this entire process.

 

[emueller]

I may need this broken down to its bare basics, as I get an "Invalid Syntax" error when trying this process.

Here is what I have for a query:

ATDays: Avg(DateDiff("d",[NUI Date],Date())) WHERE [Holding] = -1

 

[PHV]

What about this ?
ATDays: Avg(IIf([Holding]=True,Date()-[NUI Date],Null))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[emueller]

I would have NEVER thought of that, but that gets me to what I need.

I'm going to try various versions of that to see how defined I can get, as I'll need to further drill down some subcategories.

As an example, I'll need a subgroub where holding is true and Legal is false. As I understand it, utilizing the above function, this should be the idea:

ATDays: Avg(IIf([Holding]=True AND [Legal]=False,Date()-[NUI Date],Null))

Oh... and the 'Null' parameter. Does that serve to ignore values from the average that do not have an [NUI Date]

Thanks, again! This is REALLY helpful!