"Mailing Program efficiency" - design issues!

[gangel]

Hey people,
So in a nutshell:

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.

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"

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,,,,?

[June7]

Do not have a table for each service. A db structure that routinely requires creation of tables is definitely not efficient. Your tblServices and tblAnswers sounds like a better track.

Avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be IsSent, IsReceived as names for yes/no fields.

[gangel]

thanks June, yes my expamples of fields was just examples with yes/no written in to say the type of field, but accidentally put in the [] with the name! lol

the issue my brain is having is that

[tblCustomer]
CustIDf
irstname
lastname
address


[tblCombined]
ComboID
ServiceID
ResponseID
CustomerID

[tblResponses]
ResponseID
Sent
Received

[tblServices]
ServiceID
ServiceName
datestarted
enddate


This means linking via the obvious:
A Service has many customers
A Service has many responses

SO on creation of a new record in [tblCust] I can create a new record [tblCombined] which I can VBA so that in the end each customer gets subscribed to each program which has its own reponse record as well..

I guess when someone creates a new program in [tblprogram] each current patient would then auto get created a new record for responses as well.

Does this sound like Im on the right track?

I think the linked [tblcombined] is the best way to handle this as it connects customer, service and their own personal response to that service...

[June7]

tblResponses doesn't make sense to me. If each customer can have only one response to a service then just put the answers in tblCombined (and call the table tblResponses).

A date sent and date received is more informative than just a yes/no.

There is no tblProgram in the data structure although you refer to one in narrative.