Query Dcount with multiple conditions

[Guest]

Hello, hope someone can help me out

I have a query where how many projects that we won, lost, or our still
pending from each particular general contractor for each year. Here is the
query coming from the proposal log table.
Table Columns in Query: Year, General, W, L, P.
The W, L, & P columns are yes/no

Year General Win Lost Pending
2006 ABC

In the win column is an expression here it is: Win:
Dcount("[W]","Log","[W]=true")

What i am getting result is the full count in each general & year row.

Well i hope that is enough info, if not let me know.

Andy

 

[Tom Lake]

Hello, hope someone can help me out

I have a query where how many projects that we won, lost, or our still
pending from each particular general contractor for each year. Here is the
query coming from the proposal log table.
Table Columns in Query: Year, General, W, L, P.
The W, L, & P columns are yes/no

Year General Win Lost Pending
2006 ABC

In the win column is an expression here it is: Win:
Dcount("[W]","Log","[W]=true")

What i am getting result is the full count in each general & year row.

Well i hope that is enough info, if not let me know.

Don't put the DCount in the Win column. Make it a calculated column of its
own.
Personally, I'd just use one field, Status that contained W, L or P.

Tom Lake

 

[Guest]

Tom,

so i took the three columns and made them into one calling it "Status" and
using the only three letters of "W", "L", & "P"

In the query would the expression go something like this: Status:
Count([Status]="W")?

Hello, hope someone can help me out

I have a query where how many projects that we won, lost, or our still
pending from each particular general contractor for each year. Here is the
query coming from the proposal log table.
Table Columns in Query: Year, General, W, L, P.
The W, L, & P columns are yes/no

Year General Win Lost Pending
2006 ABC

In the win column is an expression here it is: Win:
Dcount("[W]","Log","[W]=true")

What i am getting result is the full count in each general & year row.

Well i hope that is enough info, if not let me know.

Don't put the DCount in the Win column. Make it a calculated column of its
own.
Personally, I'd just use one field, Status that contained W, L or P.

Tom Lake

 

[Marshall Barton]

In the query would the expression go something like this: Status:
Count([Status]="W")?

Count counts all non-Null values and your expression should
be a True or False, so it count everthing. Try one of these
instead:
Count(IIf([Status]="W", 1, Null))
or
Sum(IIf([Status]="W", 1, 0))
or
Abs(Sum([Status]="W"))