Hey people,
So in a nutshell:
Now if I had a set amount of programs I could just have Table [top300] with customerID numbers and then a bunch of fields eg. "podiatry sent", "Podiatry rcvd", "Audiology sent", "Audiology received"I have a query which scans my top 300 customers and ranks them by profit per customer. This data is extracted from close to 20,000 customers and exported to a new table [Top300]
I then want to create a system that creates letters (written by a staff member) for a bunch of services we offer.
So:
For each service I want to record if a patient was sent the letter (eg was the letter printed) and then I will record (yes/no) if the patient has responded to it in a positive manner and has taken up that service.
But it doesn't sound efficient and I think im barking up the wrong tree.
What I really need is that if someone "adds a new service" that they want mailed out it will automatically create a new report(letter) for the service, and create columns for all the patients in [top300]
OR
Should I have a [tblServices] which is a list of the tables... and then... a table with just [tblAnswers] with [ServiceID], [Sent (yes/no)], [received (yes/no)]
I think I've confused my brain in here somewhere trying to organise it...
Any input and ideas would be great
so each patient has multiple services and each service has 2 reponses yes/no boxes
Maybe each service has its own table and when I create a new service it just copies the format of the previous table and creates an entry for each patient on the [top300] and marks them as defult no/no,,,,?