Counting linked records (1 Viewer)

reglarh

Registered User.
Local time
Today, 15:53
Joined
Feb 10, 2014
Messages
118
Having passed a big birthday this week I suspect I am getting too old for this game!

I cannot figure out how to create an update query that will count the number of linked records and paste this number back into the master record.

The master records are a list of members of a society and the linked table is a list of groups to which each member belongs. This also links to a table of groups.

So Member 1 belongs to groups 2,5,9
Member 2 belongs to groups 1, 11, 14, 17

I need to insert counts of 3 and 4 into Member records 1 and 2.

Sounds easy!

But how?
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
Create an aggregate query where you 'Group by' member and 'Count' the number of groups they belong to.

However, you shouldn't then insert the count into the table as calculated values should never be stored

The reason for that is simple:
If for example Member1 leaves group 5 or joins new groups 1 & 8, your totals will immediately be out of date
Whereas the query result will always be correct
 

reglarh

Registered User.
Local time
Today, 15:53
Joined
Feb 10, 2014
Messages
118
Thanks

The reason for storing the result is that I currently have a form that shows the member details and the count of groups. However, it will not let me delete a member's record, presumably because the entry consists of data from two tables. Therefore I was going store the values, then delete the unwanted entries. This would be an annual process.

The other way is a delete query but I cannot seem to get the SQL to work.
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,186
Thanks

The reason for storing the result is that I currently have a form that shows the member details and the count of groups. However, it will not let me delete a member's record, presumably because the entry consists of data from two tables. Therefore I was going store the values, then delete the unwanted entries. This would be an annual process.

The other way is a delete query but I cannot seem to get the SQL to work.

If you use referential integrity and tick the cascade delete option then that shouldn't be an issue. However if you do that be sure to check carefully for unwanted deletions.

Having said that, I wouldn't recommend deleting members records. Add a Boolean field called Active so inactive data is flagged but still available
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,169
Dont keep us hanging.
Immediately mention the Tables Names involved (master and link).
Also mention what are the linking Field names both for master and link.
Just to minimize the guessing game.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:53
Joined
May 7, 2009
Messages
19,169
If the MasterID and LinkingID are string use this:

UPDATE MasterTableName SET MasterTableName.FieldToUpdate = DCount("*","LinkingTable","LinkingIDFromMaster=" & """" & [MasterID] & """");

If both are numeric:

UPDATE MasterTableName SET MasterTableName.FieldToUpdate = DCount("*","LinkingTable","LinkingIDFromMaster=" & [MasterID]);

==
You must supply the correct fieldnames for both tables.
 

reglarh

Registered User.
Local time
Today, 15:53
Joined
Feb 10, 2014
Messages
118
Many thanks.

All obsolete records gone!
 

Users who are viewing this thread

Top Bottom