Counting Occurrences in a 2nd Table

[jcbhydro]

Good Afternoon from the UK,

I am having difficulty in solving a problem in Access 2010 which was a doddle in the Excel version of my database.

Table1 (MailList) has a field 'Member Name' and a new field 'NumberGroups'.
Table 3 (GroupMembers) is a junction table and also has a field 'Member Name'. Records for 'Member Name' are repeated in several 'Groups' within Table 3.

I require to Count the occurrences of 'Member Name; in Table 3 and display them in Table1 'Numbergroups'.

I have written numerous Queries to try to achieve this, but without success. Does it have to be a Query, or can it be achieved entirely within the structure of Table1?

In addition, I need to interrogate the resulting numbers in Table1 'NumberGroup' to determine how many member attends 7,6, 5 etc different Groups. Again this was extremely simple in Excel but I can't crack it in Access.

All suggestions will be gratefully received.

 

[Mihail]

Post a pic with the relationships.

 

[jcbhydro]

Hi,

I am attaching a pdf of the very simple relationship of my Membership Database.

jcbhydro

 

[Mihail]

This should be the setup for your DB (see the attachment).
Now, based on my tables names, reformulate, please, the questions.

 

[Cronk]

The Junction table does not need any text data - it's redundant. Just include the MemberID and the GroupID.

To count the number of Groups that a person is a Member of, use a Totals query. There will be heaps of examples that an on line search will give you.

To store the results in a table is also redundant and runs the risk of the data not being updated when new members/groups/member-group records are added. Just run the Totals query again.

 

[jcbhydro]

Thank you for the 'Totals' suggestion. I have created a query which does it perfectly.
However, I did have a supplementary question as to interrogating the results to count the numbers of 7, 6, 5, etc in the results column. Can such a routine be incorporated into the same query?

jcbhydro.

 

[Cronk]

The easiest for you would be to create a second query based on the first, doing the same Totals function, but this time instead of grouping on MemberID, group on the sum from the first query.

 

[jcbhydro]

Thank you Cronk,
The second query produces the analysis I was looking for.

Returning to the first query, I attempted to import the occurrences for individuals back into the main Mail List Table, but it didn't work, probably because such a procedure is tanatamount to picking oneself up by ones bootlaces. Is there any other way of achieving this?

Regards,

jcbhydro

 

[Cronk]

Read the last para in my post #5.

It's bad practice to store calculated results. Create a query based on your query1 and the member table to show the total for each MemberID.

 

[jcbhydro]

As mentioned I had run a second query.

Thank you for your contribution.

jcbhydro