I have been given 200 free hotel rooms every year for ten years by vendor ZZ and 100 per year for ten years by vendor YY. I want to see how many remaining rooms i have by vendor by year. I have:

Database: Vendor/free offer information

Table A: hotel rooms used or reserved (includes date)

Table B: hotel rooms remaining (one record for each of 10 years)

I have a calculated field in table B that subtracts used and reserved from total available and gives me total left, but if I have reserved rooms in the next year, it is included in the record. What I think i need to do is find all records in Table A whose year matches the year in the record in Table B and then subtract the sum of those from total offered. Record 2 in table B will have a subsequent year and will count only those records in table A that match by year.

I realize i am in way over my head and likely should just get a calulator and do it manually, but i find FM potential fascinating and want to use it fully!

Thanks for any advice you can give!!

dales

I figured it out. I changed the relationships so that each vendor is tied to inventory which is then linked to utilization. (I originally had utilization directly related to the vendor.) Then it was easy to see if the year of the date of utilization matched the inventory year.

Thank you to all who may have spent time trying to help me with this.

dales