How to check for unique related records in a found set
I have a database that is doing bookings, invoicing and payments for my wife's medical practice.
When a booking is required to transfer to an invoice, it's "Invoice" field is marked with a "yes" via a checkbox. I then perform a find for all bookings with a "yes" that don't have an invoiceID_fk associated with it and the results are what is used for data on the invoice.
This all works perfectly but it is possible to invoice two different patients on the same invoice, which is occasionally necessary but generally not so I would like to add an If statement to the found set to check that the related records have a unique patientID_fk, ie I can have multiple bookings from the same patient but if there is bookings from a second or greater patient then it will trigger a warning.
Can anyone help with how to approach this. I'm sure it's a a Count function but just not sure how to use it.
Many Thanx in advance for any help.