Re: Displaying # of duplicates in one table to another table
Solved
Jump to Solution
1
608
1
Is there a way to count the number of times a duplicate name appears in one table and display that number in another table? For example, Minnie Mouse’s name appears 3 times in the “Billing” table and I want to output the number 3 under a column for Minnie Mouse in the “Child” table.
The way my data is set up is I have 2 tables, one for “Child” and another for “Billing”.
The “Billing” table keeps track of all the client with the months we billed them for, their name will appear multiple times corresponding to the month he/she was billed. This duplication is how our biller kept track of the data, by having a Jan, Feb, Mar, etc tabs in excel and the client’s name being inputted in the corresponding month tab. The “Child” table has a different set of data that can’t be combined with “Billing”
I’ve been trying to understand other answered inquiries that was posted here, but my knowledge of setting up the logic statements for formulas is very elementary
1 Solution
Accepted Solutions
Welcome to the community, @Melanie_Lai! :grinning_face_with_big_eyes: I think you could get what you want with a slight change to your base design.
Instead of having the person’s name as the primary field in your [Billing] table, I suggest using a link field named {Child} that lets you choose a record from your [Child] table. To make the primary field unique (which it should be), you could turn it into a formula that combines the child’s name with the month or date of the interaction. For example, if you record a date for the interaction, the formula could be something like this:
IF(AND(Child, Date), Child & " - " & DATETIME_FORMAT(Date, "L"))
The results of all of these changes would look something like this:
You can then add a count field in the [Child] table to count the number of linked records, which will tell you how many interactions you had with that child.
Welcome to the community, @Melanie_Lai! :grinning_face_with_big_eyes: I think you could get what you want with a slight change to your base design.
Instead of having the person’s name as the primary field in your [Billing] table, I suggest using a link field named {Child} that lets you choose a record from your [Child] table. To make the primary field unique (which it should be), you could turn it into a formula that combines the child’s name with the month or date of the interaction. For example, if you record a date for the interaction, the formula could be something like this:
IF(AND(Child, Date), Child & " - " & DATETIME_FORMAT(Date, "L"))
The results of all of these changes would look something like this:
You can then add a count field in the [Child] table to count the number of linked records, which will tell you how many interactions you had with that child.
