DCount Problem

[Saxman]

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

 

[Duane Hookom]

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

 

[Saxman]

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!

 

[Pat Hartman\(MVP\)]

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]

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.

 

[Duane Hookom]

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

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!

 

[Saxman]

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.

 

[Saxman]

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)

 

[Duane Hookom]

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

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.

 

[Duane Hookom]

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



cold ;-)

 

[Saxman]

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

Sorry not to have got back earlier. My newsserver software is playing
up, so I have resorted to another.

Payment Status is numeric, created from the drop-down list on the main
table. The 'Amount Paid' can vary and is not always £10. Where will
the '2' and '1' end up? In the 'Amount Paid' column?

TIA

 

[Tom Lake]

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



cold ;-)

Don't go to the EU (European Union, not Etats-Unis) then! They drink beer
at room temp.
Faugh!

Tom Lake

Spotted Dick - Tasty snack or medical condition?

 

[Duane Hookom]

The expression evaluates like you suggested you wanted it to. If the Amount
Paid is 10 then count the number where PaymentStatus = 2 or else cound the
number where Payment Status = 1.

I can't figure out what you are attempting to do with this expression. I
don't know why 10 and if it varies, why and how?

Maybe you need to back up and tell us what you expect to accomplish.

--
Duane Hookom
MS Access MVP

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

Sorry not to have got back earlier. My newsserver software is playing up,
so I have resorted to another.

Payment Status is numeric, created from the drop-down list on the main
table. The 'Amount Paid' can vary and is not always £10. Where will the
'2' and '1' end up? In the 'Amount Paid' column?

TIA

 

[Saxman]

The expression evaluates like you suggested you wanted it to. If the Amount
Paid is 10 then count the number where PaymentStatus = 2 or else cound the
number where Payment Status = 1.

I can't figure out what you are attempting to do with this expression. I
don't know why 10 and if it varies, why and how?

Maybe you need to back up and tell us what you expect to accomplish.

£10 was just figurative. I meant any amount currency wise, i.e. if
there's a value in there, return '2', if not, return '1'.

Thanks again.

 

[Duane Hookom]

Again "Maybe you need to back up and tell us what you expect to accomplish."
You seem to be divulging a bit of your needs at a time. It is very possible
that our "bit" answers may lead you in the wrong direction. If we knew your
current position and final destination, we might avoid some mistakes.

 

[Saxman]

Again "Maybe you need to back up and tell us what you expect to accomplish."
You seem to be divulging a bit of your needs at a time. It is very possible
that our "bit" answers may lead you in the wrong direction. If we knew your
current position and final destination, we might avoid some mistakes.

I am trying to create a small database for a medical charity, for which I
am not too brilliant at. Nonetheless, I think I have done a good job so
far.

One of the things they requested (right at the end) was whether they could
have a field on the main table indicating whether a member had paid or not
paid their membership. I did this with a drop down list (No/Yes). As you
know, it puts a 1 or a 2 (formatted as text) in the 'Payment Status' column
of the main table 'Clients'. There is also a text box on the main table
indicating the amount members paid in sterling (formatted as currency).
(The amount can vary). This is recorded in the 'Amount Paid' column on the
main table 'Clients'.

My query is this, if the is a value in the 'Amount Paid' column, then could
it be indicated in another form whether the member had paid/not paid?

On second thoughts, I think I will keep it as it is and leave the onus on
the charity office workers to do the recording, as there might be problems
when annual membership subscriptions are due for renewal?

 

[Duane Hookom]

I would not keep payment information in the main table. Payments are
transactions that should be kept in a related table. Every payment from
every member should create a record in the related table.

 

[Saxman]

I would not keep payment information in the main table. Payments are
transactions that should be kept in a related table. Every payment from
every member should create a record in the related table.

You are right, but as the company who needs it only employ 2 people and I'm
not getting paid, it can stay as it is.

It was wine last night, but another beer festival looms at the end of the
week! As it's a 'Winter Ale' festival, they will all be dark and strong
(the beer that is). The only cold beer will be bottled Beligian which is
excellent.

 

[Duane Hookom]

I assume your question has been either answered or mooted.
Raise a Beligian for me...

 

[Saxman]

I assume your question has been either answered or mooted.
Raise a Beligian for me...

I will! It might be an Orval.

http://www.beerritz.co.uk/Beers/Belgian/

I think this is a valuable resource, especially the downloads.

http://www.mvps.org/access/index.html