Discussion Locked

Access 97 Multiple Counts

0 helpful
13 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an Access97 Membership database. Each member can have a Basic, Silver, Bronze or Gold Membership category. Each has a different membership fee.

I want to print out a Summary report (NO DETAIL LINES) and show how many members are in each membership category and how much I collected for each category. I want them all on the same sheet!

Can you help??? Please Email a copy of your reply to:
MRA2@mindspring.com

Was closed in Error!!! Sorry!!!

13 replies

by john_wills

Use the Sorting & Grouping window in your report; if it is not visible click on the icon to the right of the hammer and spanner in the toolbar. Choose field MembershipCategory, whichever sort order you want. At the bottom of the window set Group Header to No(at least for your first try), Group Footer to Yes, Group On to Each Value, Group Interval to 1, Keep Together to No. A space for Membership Category footer will appear in the design of the report: here insert a label "Number in Category", atext box with for data MembershipCategory and a text box with for data Count(MembershipID). The membership fee needs another text box, with an appropriate data specification including a multiplication by Count(MembershipID), but how you do it depends on the structure of your tables and the query you use behind the report.

by shanghai sam

Create a query using the table where your info is located. Pick the CATEGORY & FEE fields. Click on menu option, VIEW, then TOTALS. Change the GROUP BY option in the query to SUM on the FEE field. Save/Name the Query. Create a new report using theREPORT WIZARD option. Pick the QUERY you just made.Select all the fields from the QUERY. Click the NEXT button. Select the MEMBERSHIP CATEGORY for the grouping question. Click the NEXT button. On the SORT OPTION screen, click the SUMMARY OPTIONS button. Click on the SUM box next to the FEE field. Click the SUMMARY ONLY radio button in the SHOW area. Click the OK button. Click the NEXT button. Click the NEXT button again. Pick your layout and click the NEXT button. Name the report and click the FINISH Button. Hope this helps!

by mj512003

I forgot your count. In the query, you can also do a count on the Name field and then pull that into your report. mj51@mindspring.com

by mj512003

I forgot your count. In the query, you can also do a count on the Name field and then pull that into your report. mj51@mindspring.com

by kees.valkenswaard

Suppose you have the following fields [you should have more]:
Mem-ID
Type
Subscription [numeric or better currency]
Make a query based on this table.
Use the sum-button [Sigma, the same sign as auto-sum in Excel]
Put in the grid the field type [silver, etc]
It will automatically get Group by [in the total line]
Add the same field with count
Add the field Subscription with sum
That is all.
Just print the query result.
If you want to make it fancy you can make a report [even an auto report] based on this query.
Good luck

by timfox

The structure of your query depends on the structure of your tables. The following query works for a table that has the following fields:

tblMembers
MemID (Autonumber)
MemName (Text)
MemCategory (Text)
MemCost (Currency)

SELECT tblMembers.MemCategory, Count(tblMembers.MemID) AS MemCount, Sum(tblMembers.MemCost) AS CostSum
FROM tblMembers
GROUP BY tblMembers.MemCategory;

hope it helps
TimFox

This discussion is closed to new replies.