Count function via relationship

[lsmall]

My database collects information about medication use in a hospital.

In Table 1 every medication that every patient is on is entered daily. Each medication for each patient on each day is an individual record. There are about 5000 records daily added to this table. The fields include medication name, dose, patient number, location of patient, date etc.

In Table 2 I have a list of medications that I want analyzed using data from Table 1. Each record is a different medication of interest and only occurs once. Table 1 and Table 2 are related via the name of the medication. In table 2 I use the Count function to show how many times that particular medication occurs in table 1. This is basically a one to many relationship from Table 2 to Table 1 and most medications in Table 1 do not occur in Table 2 (only those meds of interest are listed in Table 2)

The count function works well in giving me information on unfiltered data, however I am looking for an effective way to get the same information in table 2, but with various filters applied. e.g. count how many times medication x occurs in table 1 on date y or how many times medication x occurs from date y...z at location A etc.

If a found set of Table 1 could be analyzed via Table 2, there would be no issue, but the count function only provides the information about all entries in the table and not the found set.

What options are there to do what I am describing? Let me know if you need a better description or more information.

Thanks

[comment]

Basically, you have two options: either filter your relationship by additional criteria, such as date range and location, or do a summary report directly from the data table.

[Fenton]

You could do this using a trick that Søren Dyhr came up with. Basically you have global fields to set the parameters for each of the lines of the relationship (4 in the above case). Then you create a calculation field for each, which will either mirror the global field, if it is not empty, or the ValueListItems ( "value list name" )* of all possible values for that field (in the target table) if the global field is empty; effectively giving you a match of all records for that line.

The tricky part of your situation would be the date field. You'd want a global "date begin" and "date end", with the operators of that line of the relationship being ≤ and ≥ respectively (I think, depends on which way you look at the relationship). If you used ValueListItems() for these, it would be a huge waste of processing time. Whereas, if the records are entered in ascending order by date (normal), then all you'd need is Table1::Date for the beginning and Last ( Table1::Date) for the end. Way faster. (Or use Min() and Max(), but that would also be slow.)

If you could upload a smallish file (with just a sampling of records, of a few dates), then we (the royal or personal -) could set it up for you.

*Notice the value list name is inside quotes; it's a weakness; don't change the name of the Value List later.

S__ren_Popglobal.fp7.zip

[comment]

For unfiltered dates, you could use simply Date ( 1 ; 1 ; 1 ) and Date ( 12 ; 31 ; 4000 ).

[lsmall]

Thanks for all of your replies. I was close a few days ago, but the dates were the difficult part and I gave up. These are some good ideas. I will give them a go. If I can't work it out, I may take you up on the offer and upload it.

Thanks.

[lsmall]

The date filter works. I added a begin date ≥ relationship and end date ≤ relationship from table 2 to table 1:date. I made these 2 fields global with auto insert calculations of the first date I started collecting data for the begin date field and get(currentdate) for the end date field. So now the default includes all dates and I can easily filter by date.

Great stuff. Thanks.