count related records

  • Thread starter Thread starter Mitch
  • Start date Start date
M

Mitch

2 tables - 1) tickets and 2) messages. Tickets contains the main data and
messages contains all the comments made relating to that ticket. How do I
count the number of records (or messages) in the related table? So, I have 1
ticket in the system and 4 comments have been made in that ticket. I want to
be able to take that number and append it to another ticket table showing "4"
in that field.
 
Appending the count to another ticket table is not the thing to do! Say you
append 4 and then add another message. The count is now 5 but you only have
4 in the other table.

You can get the count any time with the expression:
DCount("*","Messages","[TicketID] =" & Forms!NameOfYourForm!TicketID)

Steve
(e-mail address removed)
 
2 tables - 1) tickets and 2) messages. Tickets contains the main data and
messages contains all the comments made relating to that ticket. How do I
count the number of records (or messages) in the related table? So, I have 1
ticket in the system and 4 comments have been made in that ticket. I want to
be able to take that number and append it to another ticket table showing "4"
in that field.
Click to expand...

I agree with Steve that storing this 4 in any table is A Very Bad Idea; and
it's also unnecessary.

The DCount() function Steve suggests is one way (and the best way if you want
the query to be updateable); a faster query will be a Totals query. Create a
Query joining Tickets to Messages; include whatever information you want from
the Tickets table and *only* the joining field from the Messages table. Make
the query a Totals query by clicking the Greek Sigma icon (looks like a
sideways M); change the default "Group By" to Count on the Totals row under
the Messages join field.
 
Back
Top