Hi:
I am trying to create a subset from a table that contains
historical data for bed transactions for all Long Term Care facilities (e.g. Nursing homes, Assisted Living, Adult Day Care) in a state. The following are the relevant fields from this historical Bed Detail table, a brief description, the data type, and field length:
1) BEDSUMMARYID Unique identifier for each bed transaction Number(Double) Size=8
2) FACILITY_INTERNAL_ID Uniques identifier for each facility Number(Long) Size=4
3) BEDTYPE Code that identifies the type of bed (e.g. Assisted Living, Residential)
4) BEDDESC Verbal description corresponding to the codes in field BEDTYPE
5) BEDEFFECT Effective date of bed transaction (e.g. adding, deleting, changing type of bed (e.g. facility decides to convert Residential Beds to Comprehensive Personal health Care beds))
6) LIC Number of Licensed Beds in each category
I would like to create a dynaset containing only current records (in other words, there should only be one record for each combination of fields FACILITY_INTERNAL_ID and BEDTYPE, and in cases in which there are multiple records for this combination in the current table, I would like to save the record containing the most recent date (field: BEDEFFECT). This dynaset would enable me to produce list and summary reports detailing the current mix of Bed Types.
I would be very grateful for any suggestions.
I am trying to create a subset from a table that contains
historical data for bed transactions for all Long Term Care facilities (e.g. Nursing homes, Assisted Living, Adult Day Care) in a state. The following are the relevant fields from this historical Bed Detail table, a brief description, the data type, and field length:
1) BEDSUMMARYID Unique identifier for each bed transaction Number(Double) Size=8
2) FACILITY_INTERNAL_ID Uniques identifier for each facility Number(Long) Size=4
3) BEDTYPE Code that identifies the type of bed (e.g. Assisted Living, Residential)
4) BEDDESC Verbal description corresponding to the codes in field BEDTYPE
5) BEDEFFECT Effective date of bed transaction (e.g. adding, deleting, changing type of bed (e.g. facility decides to convert Residential Beds to Comprehensive Personal health Care beds))
6) LIC Number of Licensed Beds in each category
I would like to create a dynaset containing only current records (in other words, there should only be one record for each combination of fields FACILITY_INTERNAL_ID and BEDTYPE, and in cases in which there are multiple records for this combination in the current table, I would like to save the record containing the most recent date (field: BEDEFFECT). This dynaset would enable me to produce list and summary reports detailing the current mix of Bed Types.
I would be very grateful for any suggestions.
