Help With Expression Please

[Saxman]

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'")

The above works fine. It sums up the number of therapists who specialise
in a particular treatment. I need to add which therapists are active
(available). I have tried doing this with the AND function, but I have not
got it quite right.

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'") AND [Active] = '1'"

It's the bit on the end that does not work. I have tried adding/removing
quotes to no avail. The 'Active' column comes from the same table. Active
can be 1 or 2 (not active).

I need you Duane Hookhom as this has got to get sorted before the beer
festival!

 

[John Spencer]

How about the following?

DCOUNT("*","Clients","Therapy1='1' and Active='1'") +
DCOUNT("*","Clients","Therapy2='1' and Active='1'") + ...

Depending on your structure you might be able to use this to get it done in
one DCount
DCount("*","Clients","Active = '1' AND (Therapy1='1' or Therapy2='1' Or ...
Or Therapy6 ='1')" )

 

[Saxman]

How about the following?

DCOUNT("*","Clients","Therapy1='1' and Active='1'") +
DCOUNT("*","Clients","Therapy2='1' and Active='1'") + ...

Depending on your structure you might be able to use this to get it done in
one DCount
DCount("*","Clients","Active = '1' AND (Therapy1='1' or Therapy2='1' Or ...
Or Therapy6 ='1')" )

I get the #Name? error using the above.

 

[Saxman]

I get the #Name? error using the above.

This is the SQL statement that my software generated:-

Select * from [Clients] Where [Therapy1]="1" AND [Active]="1"

I also get the same error, probably because of the SQL. (I'm a novice at
these things).

 

[Saxman]

How about the following?

DCOUNT("*","Clients","Therapy1='1' and Active='1'") +
DCOUNT("*","Clients","Therapy2='1' and Active='1'") + ...

Depending on your structure you might be able to use this to get it done in
one DCount
DCount("*","Clients","Active = '1' AND (Therapy1='1' or Therapy2='1' Or ...
Or Therapy6 ='1')" )

This works:-


=DCount("*","Clients","[Therapy1] = '1' AND [Active] = '1'")

This does not:-

=DCount("*","Clients","[Therapy1] = '1' AND [Active] = '1'") +
("*","Clients","[Therapy2] = '1' AND [Active] = '1'") +
("*","Clients","[Therapy3] = '1' AND [Active] = '1'") +
("*","Clients","[Therapy4] = '1' AND [Active] = '1'") +
("*","Clients","[Therapy5] = '1' AND [Active] = '1'") +
("*","Clients","[Therapy6] = '1' AND [Active] = '1'")

It displays blank

I just need to add [Therapy2], 3,4,5,and 6.

 

[Saxman]

DCount("*","Clients","Active = '1' AND (Therapy1='1' or Therapy2='1' Or ...
Or Therapy6 ='1')" )

=DCount("*","Clients","Active = '1' AND (Therapy1='1' OR Therapy2='1' OR
Therapy3='1' OR Therapy4='1' OR Therapy5='1' OR Therapy6='1' OR Therapy6
='1')" )

This works fine! You made my day!

Just one more problem and it's finished! Look out for me!

Thanks again.

 

[John Spencer]

Glad you got it working and glad I read to the end of your postings. I was
just starting to think how to reply. What additional information I needed
to get from you to work on the problem.

I would have left this for Duane Hookom, but the urgent deadline of the beer
festival just called out for an answer.

 

[John Vinson]

=DCount("*","Clients","[Therapy1] = '1'")+DCount("*","Clients","[Therapy2]
= '1'")+DCount("*","Clients","[Therapy3] =
'1'")+DCount("*","Clients","[Therapy4] =
'1'")+DCount("*","Clients","[Therapy5] =
'1'")+DCount("*","Clients","[Therapy6] = '1'")

I see you got a solution... BUT.

Part of the problem is that your table structure is non normalized. If
you have six therapies for a client, someday you might need seven.
What do you do then? Restructure your table, rewrite all your queries,
redesign all your forms?

If you have a Many (clients) to Many (therapies) relationship... model
it as a many to many relationship, with a ClientTherapy table with one
row for each therapy/client combination.

John W. Vinson[MVP]