Results 1 to 2 of 2
  1. #1
    jcright is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2022
    Posts
    21

    Need advice on creating a report that involves multiple variables

    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.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Does not sound normalised?
    Should have one record for each salesperson and store.
    Then a simple query gets all salespeople for a store, whether 1 or 100.
    In fact more likely
    TblSalesperson
    TblStore
    TblStoreSalesperson to link stores and salespersons
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 25
    Last Post: 09-20-2018, 04:48 AM
  2. Replies: 5
    Last Post: 04-16-2018, 10:51 AM
  3. Replies: 3
    Last Post: 07-08-2015, 08:59 AM
  4. Replies: 3
    Last Post: 02-19-2015, 04:41 PM
  5. Replies: 1
    Last Post: 06-09-2014, 02:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums