Count with Criteria from Multiple Tables

[DrB1]

I'm so stuck. I hope one of you gurus will easily guide me. I have the following tables:

• Individuals
  • IndividualId
  • Label (name)
•  EventRegistration
  • IndividualId
  • EventId
  • DateRegistered
• Meetings
  • EventId
  • MeetingId
  • StartDateTime
• Markings (includes individualid's that were marked present for a specific meeting id)
  • MeetingId
  • IndividualId

I have tried various ways of connecting these tables but cannot find what I need. I can count registrations easy enough, and I can count attendees easy enough. I am using the registration data to show who registered prior to the event (DateRegistered<StartDateTime, and I need to show which column the those who attended are in. Something like if(DateRegistered<StartDateTime, count(Markings.IndividualId) but it won't work. It gives me a huge number which lets me know it is not filtering the marking count down to the specific MeetingId I'm working with.

Currently the tables are linked like this. I'm sure one issue it the Markings table IndividualId is not linked to Individuals, but I don't know how to use multiple keys in a table. Is that my issue?

[rubenmarin1 · Accepted solution]

Hi, I think that you can join events and meeting tables, so you have one table with MeetingId and IndividualId, and then use the Markings table to add a flag on this table to count presents.

[DrB1]

Ok, @rubenmarin1 - I will try it. Concatenate or outer join - I'm not sure what the difference is, but I will try both.

[DrB1]

Thank you rubenmarin for this suggestion to join Event and Meeting tables. It does help in many ways. I was able to add a couple calculated fields by using dates from the two tables.  

I still cannot use a dimension from the new Event table to impact the counts from the markings table.  For example - I'm getting 91 in the markings count for both rows so the field 'expected' that is splitting the counts into 2 rows, does not affect the 91.   The Registered did split correctly, but I need the Markings/Attended 91 to split based on the Expected/Walk-in field. What am I missing?

[DrB1]

I am going to try making a calculated id field of MeetingId+IndividualId on both the new Event table and the markings table. I'll keep you posted. Thank you so much for helping me.

[DrB1]

I thinked the dual id key worked. Thanks again!!

[rubenmarin1]

I had no time to get back to your answers before you solved it. Good work!