Hello all,
For months now I've been working on a database that creates a commission report for each salesperson and a general store report. I've got it so that it will automatically email all of the commission reports. I'm getting bogged down on emailing the store reports. I've successfully created the store report, but I'm having a hard time figuring out how to automate sending it via email. Each store report will be sent to certain individiuals but not all. The owner will get a store report for each location. The store manager will get a store report for their store location(s). Other peons in the organization get nothing. Right now I have a table called "email". Each row contains all of the salespeople in the company. The Columns are as follows: Salesperson, Email, Store1, Store2, Store3. Store1/2/3 holds either "Yes" or "No" which is to signify whether or not they get a store report for that particular store. Right now this table is also being used to send the commission report to each individual, but it doesn't use the "Store" columns, just name and email. Is this the wrong way to do it?
My thought had been to use vba to open a record set to evaluate if the person gets a store report from any of the stores. However, I have no idea how to make it hop from one column to the next automatically. Furthermore, I don't know how to tell it how many columns there really are. My goal is to make this scalable so that the only thing I change is the table itself and not any of the coding. The number of salespeople need to increase and decrease. The number of stores need to increase and decrease as well. The other option is to hardcode for individuals and locations, but then I lose scalability.
I'd appreciate your thoughts on how to go about doing this.