Populate a new join?

[MSPJ]

Hi -

I have three related questions, related to joins. Here's my situation - I have 3 tables: one tracks doctor's visits, then there are two related tables - one for patients and one for the services that occur at each visit. I would like to be able to keep track of how many times each patient has had individual services. In other words, how many visits occur with a given combination of patient and service.

This seems pretty simple in concept, but I couldn't find a way to easily determine this except with a script that goes through every combination, does a find, and records the number of records in the found set.

So - my first question is - is that the best way to do this, or is there some incredibly simple method I've missed?

Okay - assuming this IS the best way to do this, how do I store those results for display on a layout? Essentially, I'm looking for a cross-tab table - patient by service, with count of each combo.

Should I create a standalone table with both id fields and a separate field for count, and then have a script update that everytime? Or should this table actually be a join between the patient and service tables?

If it should be a join, two more questions: since I already have many visits, how do you quickly populate the join for the existing records?

In use, would there be some way to have it updated automatically every time a visit is added, or do I still need my script to go through, do the counts, and update the join table count field?

Thanks!

Michael

[T-Square]

You should be able to get this to work with a summarizing report based in the services table. Create a part that displays when your preview is sorted by PatientID and ServiceID, and put a summary field in that part that counts the PatientID field. See if that doesn't get you headed in the right direction.

David