count related records

[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.

 

[Steve]

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)

 

[John W. Vinson]

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.

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.