Query Dcount with multiple conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Andy said:
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.
Click to expand...

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
 
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")?

Tom Lake said:
Andy said:
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.
Click to expand...

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
Click to expand...
 
Andy said:
In the query would the expression go something like this: Status:
Count([Status]="W")?
Click to expand...


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"))
 
Back
Top