Query Or Report?

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

I have a form with a drop down list for membership status, i.e. member,
fellow, registered or associate. The 4 membership types are stored in a
table 'Memstatus' and the data stored in a table 'Clients', in a column
'Membership Type' with the values 1,2,3,or 4.

How do I find out how many members of each type?
 
Saxman said:
I have a form with a drop down list for membership status, i.e.
member, fellow, registered or associate. The 4 membership types are
stored in a table 'Memstatus' and the data stored in a table
'Clients', in a column 'Membership Type' with the values 1,2,3,or 4.

How do I find out how many members of each type?
Click to expand...


SELECT [Membership Type], Count(*) AS Qty
FROM Clients
GROUP BY [MemberShip Type]

If you want to see the text values...

SELECT [Membership Status], Count(*) AS Qty
FROM Clients Inner Join MemStatus
ON Clients![MemberShip Type] = MemStatus![Membership Status]
GROUP BY [MemberShip Status]

I'm guessing on the name of the field containing the text. Substitute as
required.
 
Rick said:
Saxman said:
I have a form with a drop down list for membership status, i.e.
member, fellow, registered or associate. The 4 membership types are
stored in a table 'Memstatus' and the data stored in a table
'Clients', in a column 'Membership Type' with the values 1,2,3,or 4.

How do I find out how many members of each type?
Click to expand...


SELECT [Membership Type], Count(*) AS Qty
FROM Clients
GROUP BY [MemberShip Type]

If you want to see the text values...

SELECT [Membership Status], Count(*) AS Qty
FROM Clients Inner Join MemStatus
ON Clients![MemberShip Type] = MemStatus![Membership Status]
GROUP BY [MemberShip Status]

I'm guessing on the name of the field containing the text. Substitute as
required.
Click to expand...

Sorry, that second SQL should be...

SELECT [Membership Status], Count(*) AS Qty
FROM Clients Inner Join MemStatus
ON Clients![MemberShip Type] = MemStatus![Membership Type]
GROUP BY [MemberShip Status]
 
I have a form with a drop down list for membership status, i.e. member,
fellow, registered or associate. The 4 membership types are stored in a
table 'Memstatus' and the data stored in a table 'Clients', in a column
'Membership Type' with the values 1,2,3,or 4.

How do I find out how many members of each type?
Click to expand...

In a report unbound controls.
=Sum(IIf([Membership Type]=1,1,0))
=Sum(IIf([Membership Type]=2,1,0))
=Sum(IIf([Membership Type]=3,1,0))
=Sum(IIf([Membership Type]=4,1,0))
 
There area various approaches:

1. Use a crosstab query. This can cope with an unknown number of categories.

2. As in this case you have a known number of categories you can
conditionally aggregate the data by summing expressions which return 1 or 0
depending on the category. In a query you'd do this:

SELECT
SUM(IIF([Membership Type] = 1,1,0) AS MemberCount,
SUM(IIF([Membership Type] = 2,1,0) AS FellowCount,
SUM(IIF([Membership Type] = 3,1,0) AS RegisteredCount,
SUM(IIF([Membership Type] = 4,1,0) AS AcssociateCount
FROM Clients;

You can base a report, a form or an unlinked subreport or subform in a
parent form/report on this query.

3. You can use the DCount function to count the numbers of rows in the
Clients table which meet each criterion for type of membership, e.g. for
Members:

= DCount("*", "Clients", "[Membership Type] = 1")

You could use this expression, and similar ones for the other membership
types as the ControlSource of a text box in a form or report.

Incidentally. you might sometimes see a method recommended for conditional
aggregation which exploits the fact that in Access TRUE is -1 and FALSE is 0,
e.g. in a query:

SUM(([Membership Type = 1)*-1) As MemberCount
or
SUM(ABS([Membership Type = 1)) As MemberCount

DON’T DO THIS! Relying on the implementation in this way is bad programming
practice.
 
SELECT [Membership Type], Count(*) AS Qty
FROM Clients
GROUP BY [MemberShip Type]

If you want to see the text values...

SELECT [Membership Status], Count(*) AS Qty
FROM Clients Inner Join MemStatus
ON Clients![MemberShip Type] = MemStatus![Membership Status]
GROUP BY [MemberShip Status]
Click to expand...

I am assuming I would paste this in the crosstab query field?

It would nice to click a button and see the types listed on a table or
something?!
 
Saxman said:
SELECT [Membership Type], Count(*) AS Qty
FROM Clients
GROUP BY [MemberShip Type]

If you want to see the text values...

SELECT [Membership Status], Count(*) AS Qty
FROM Clients Inner Join MemStatus
ON Clients![MemberShip Type] = MemStatus![Membership Status]
GROUP BY [MemberShip Status]
Click to expand...

I am assuming I would paste this in the crosstab query field?
Click to expand...

Nope. That's the entire SQL of a new query.
It would nice to click a button and see the types listed on a table or
something?!
Click to expand...

After getting a query that lists what you want make that the RowSource of a
ListBox on your Form.
 
3. You can use the DCount function to count the numbers of rows in the
Clients table which meet each criterion for type of membership, e.g. for
Members:

= DCount("*", "Clients", "[Membership Type] = 1")

You could use this expression, and similar ones for the other membership
types as the ControlSource of a text box in a form or report.
Click to expand...

This sounds like a good solution. Would I have to create a table with a
field and link the code you have listed in field properties?

Exactly how would I do this?

TIA.
 
No, you don't have to do anything with your table, just use the DCount
function to count the relevant rows in it. You can do this anywhere you
wish, so if you wanted to get all 4 counts on a form or report you'd just add
4 text boxes to the form or report and enter the expression as the
ControlSource property in the properties sheet for each text box in design
view. So the first text box, the count of members, would have the
ControlSource:

= DCount("*", "Clients", "[Membership Type] = 1")

and the other three would be the same, but would substitute 2,3 or 4 for the
1. The third argument of the function is the criterion for the count, so
this would mean you'd get counts in each case where the Membership Type was
whatever the value you specify for it.

Saxman said:
3. You can use the DCount function to count the numbers of rows in the
Clients table which meet each criterion for type of membership, e.g. for
Members:

= DCount("*", "Clients", "[Membership Type] = 1")

You could use this expression, and similar ones for the other membership
types as the ControlSource of a text box in a form or report.
Click to expand...

This sounds like a good solution. Would I have to create a table with a
field and link the code you have listed in field properties?

Exactly how would I do this?

TIA.
Click to expand...
 
No, you don't have to do anything with your table, just use the DCount
function to count the relevant rows in it. You can do this anywhere you
wish, so if you wanted to get all 4 counts on a form or report you'd just add
4 text boxes to the form or report and enter the expression as the
ControlSource property in the properties sheet for each text box in design
view. So the first text box, the count of members, would have the
ControlSource:

= DCount("*", "Clients", "[Membership Type] = 1")

and the other three would be the same, but would substitute 2,3 or 4 for the
1. The third argument of the function is the criterion for the count, so
this would mean you'd get counts in each case where the Membership Type was
whatever the value you specify for it.
Click to expand...

You have explained this impeccably, but even if I copy and paste the above
function, I get #Error in the textbox. I have checked that my source form
is definitely named 'Clients' and the respective column within is
definitely named 'Membership Type'.

TIA
 
Saxman said:
No, you don't have to do anything with your table, just use the
DCount function to count the relevant rows in it. You can do this
anywhere you wish, so if you wanted to get all 4 counts on a form
or report you'd just add 4 text boxes to the form or report and
enter the expression as the ControlSource property in the
properties sheet for each text box in design view. So the first
text box, the count of members, would have the ControlSource:

= DCount("*", "Clients", "[Membership Type] = 1")

and the other three would be the same, but would substitute 2,3 or
4 for the
1. The third argument of the function is the criterion for the
count, so this would mean you'd get counts in each case where the
Membership Type was whatever the value you specify for it.
Click to expand...

You have explained this impeccably, but even if I copy and paste the
above function, I get #Error in the textbox. I have checked that my
source form is definitely named 'Clients' and the respective column
within is definitely named 'Membership Type'.

TIA
Click to expand...

"Clients" needs to the name of a table or query, not of a form.
 
Back
Top