DCount Problem

Just one more little problem with my database owing to another request from
the user!

Basically, there are four membership types with the status of Paid/Not
Paid. The user wants to know how many of each type have Paid/Not Paid.

In my main table 'Clients' I have two columns, namely 'Membership Type' and
'Payment Status'. 'Membership Type' has four values 1,2,3,4.

'Payment Status' has two values 1 and 2 (No and Yes).

=DCount("*","Clients","[Membership Type] = '4'") returns the total number
of members of that value correctly, but when I add the following, the sums
do not add up correctly.

=DCount("*","Clients","[Membership Type] =
'4'")+DCount("*","Clients","[Payment Status] = '2'")

Where am I going wrong please?

TIA

It's not clear what you want. If there are a total of 12 with Membership
Type of 4 and a total of 15 with Payment Status of 2, do you want to return
27? Or, do you want the number that meet both conditions?

I expect you want
=DCount("*","Clients","[Membership Type] = '4' AND [Payment Status] = '2'")

If you fields are numeric, then ditch the single quotes:
=DCount("*","Clients","[Membership Type] = 4 AND [Payment Status] = 2")

--
Duane Hookom
MS Access MVP


"Saxman" wrote in message
...
Just one more little problem with my database owing to another request
from
the user!

Basically, there are four membership types with the status of Paid/Not
Paid. The user wants to know how many of each type have Paid/Not Paid.

In my main table 'Clients' I have two columns, namely 'Membership Type'
and
'Payment Status'. 'Membership Type' has four values 1,2,3,4.

'Payment Status' has two values 1 and 2 (No and Yes).

=DCount("*","Clients","[Membership Type] = '4'") returns the total number
of members of that value correctly, but when I add the following, the sums
do not add up correctly.

=DCount("*","Clients","[Membership Type] =
'4'")+DCount("*","Clients","[Payment Status] = '2'")

Where am I going wrong please?

TIA

On Sat, 26 Nov 2005 11:11:49 -0600, Duane Hookom wrote:

I expect you want
=DCount("*","Clients","[Membership Type] = '4' AND [Payment Status] = '2'")

That is exactly want I want! Thank you so much! I'm going to pour a beer
out now. Wish you could join me!

A more flexible solution is to use a totals query:
Select [Membership Type], [Payment Status], Count(*) As GroupCount
From [Clients]
Group By [Membership Type], [Payment Status];

If you really want only a single type and status, add a parameter.

BTW, it is poor practice to use embedded spaces or special characters in
your names. It is also best to avoid function and property names since
these will cause problems with VBA and occasionally SQL.

"Saxman" wrote in message
...
Just one more little problem with my database owing to another request
from
the user!

Basically, there are four membership types with the status of Paid/Not
Paid. The user wants to know how many of each type have Paid/Not Paid.

In my main table 'Clients' I have two columns, namely 'Membership Type'
and
'Payment Status'. 'Membership Type' has four values 1,2,3,4.

'Payment Status' has two values 1 and 2 (No and Yes).

=DCount("*","Clients","[Membership Type] = '4'") returns the total number
of members of that value correctly, but when I add the following, the sums
do not add up correctly.

=DCount("*","Clients","[Membership Type] =
'4'")+DCount("*","Clients","[Payment Status] = '2'")

Where am I going wrong please?

TIA

On Sat, 26 Nov 2005 12:42:48 -0500, Pat Hartman(MVP) wrote:

A more flexible solution is to use a totals query:
Select [Membership Type], [Payment Status], Count(*) As GroupCount
From [Clients]
Group By [Membership Type], [Payment Status];

If you really want only a single type and status, add a parameter.

BTW, it is poor practice to use embedded spaces or special characters in
your names. It is also best to avoid function and property names since
these will cause problems with VBA and occasionally SQL.

Thanks for the feedback. It is difficult for novices such as myself.
There appears to be lots of material on using Access, but not on database
design in the way that you have described my situation.

I can tell that you are not from around here. "pour a beer out" would
suggest dumping it on the ground or in the sink. We would just "pour a beer"
or "tip a brewski".

--
Duane Hookom
MS Access MVP


"Saxman" wrote in message
.. .
On Sat, 26 Nov 2005 11:11:49 -0600, Duane Hookom wrote:

I expect you want
=DCount("*","Clients","[Membership Type] = '4' AND [Payment Status] =
'2'")

That is exactly want I want! Thank you so much! I'm going to pour a beer
out now. Wish you could join me!

On Sat, 26 Nov 2005 12:45:49 -0600, Duane Hookom wrote:

I can tell that you are not from around here. "pour a beer out" would
suggest dumping it on the ground or in the sink. We would just "pour a beer"
or "tip a brewski".

This is what I have in my cell now which works fine.

=DCount("*","Clients","[Membership Type] = '2' AND [Payment Status] = '2'")

Could this be modified to something like:-

=DCount("*","Clients","[Amount Paid] = '£10' THEN [Payment Status] = '2'")
ELSE [Payment Status] = '1'")

By refering to £10 as a sum I mean any sum preceeded by £ or sum etc.,
otherwise the cells in 'Amount Paid' are empty (null), i.e if there was a
monetary quantity in 'Amount Paid', then could a value of 1 or 2 be pasted
into column 'Payment Status' for access by another table?

This would obviate the need for a field on the main form to record whether
payment had been made (No/Yes) (1/2) in a drop-down list.

On Sat, 26 Nov 2005 12:45:49 -0600, Duane Hookom wrote:

I can tell that you are not from around here. "pour a beer out" would
suggest dumping it on the ground or in the sink. We would just "pour a beer"
or "tip a brewski".

Our beer is not that bad! There are more brewers per head of population in
the UK now than anywhere else in Europe:-))

You can use the IIf() function.
=DCount("*","Clients","[Payment Status] = IIf([Amount Paid] = 10, '2','1'")

I still question whether or not Payment Status is numeric or text????
--
Duane Hookom
MS Access MVP


"Saxman" wrote in message
...
On Sat, 26 Nov 2005 12:45:49 -0600, Duane Hookom wrote:

I can tell that you are not from around here. "pour a beer out" would
suggest dumping it on the ground or in the sink. We would just "pour a
beer"
or "tip a brewski".

This is what I have in my cell now which works fine.

=DCount("*","Clients","[Membership Type] = '2' AND [Payment Status] =
'2'")

Could this be modified to something like:-

=DCount("*","Clients","[Amount Paid] = '£10' THEN [Payment Status] = '2'")
ELSE [Payment Status] = '1'")

By refering to £10 as a sum I mean any sum preceeded by £ or sum etc.,
otherwise the cells in 'Amount Paid' are empty (null), i.e if there was a
monetary quantity in 'Amount Paid', then could a value of 1 or 2 be pasted
into column 'Payment Status' for access by another table?

This would obviate the need for a field on the main form to record whether
payment had been made (No/Yes) (1/2) in a drop-down list.

There is only one type of beer that I truly enjoy....



cold ;-)

--
Duane Hookom
MS Access MVP


"Saxman" wrote in message
...
On Sat, 26 Nov 2005 12:45:49 -0600, Duane Hookom wrote:

I can tell that you are not from around here. "pour a beer out" would
suggest dumping it on the ground or in the sink. We would just "pour a
beer"
or "tip a brewski".

Our beer is not that bad! There are more brewers per head of population
in
the UK now than anywhere else in Europe:-))