Calculating one to multiple relationships
In a FM database we have two tables: appointments and sales. The company sells multiple products so an appointment might generate multiple sales.
Currently the relationship appointment -> sale is one-to-one. Yet that does not work for obvious business reasons. An appointment could be related to multiple sales.
The current situation:
When appointment1234 turns into a sale the first job number is PROD1SALE1234. Then someone manually has to duplicate it and change it to reflect the second sale with a job number of PROD2SALE1234. As you can see the SALE1234 remains consistent and currently is the only mechanism we have to figure out how many sales we have for one appointment.
We need an automated way to calculate how many sales we have on each appointment. Since there is only one entry in the appointments table it only reports back one sale from the sales table. I want it to look on all entries in the sales table, count how many SALE1234 (independent of the first PRODx characters) entries is has and report back to me automatically.
Any ideas on how to accomplish this?