Windows 10 
Access 2013 64bit
Greetings!
I would like some help making a database that performs multiple functions on one form. I do not have the form created but before I start down the path again I wanted to hear from experts if this is possible or if there is something like this already available in Access. I am a novice and would like to learn how to do this. Please let me know if this is the wrong place to post this. Here is the general idea followed by the parameters:
General Idea
*I would like to be able to track how much each sales associate has sold, who they have sold it to and how much their customers have spent.
*I would also like to be able to track how much each customer has purchased, when they purchased it and how many times they purchased.
*I would like to be able to maintain a rewards program based on how much referral business each customer has generated for 3 years. The idea is that Customer 1 buys Product A for $10 and refers Customer 2. Customer 2 buys product B for $100 and refers Customers 3 and Customer 4. Etc... Etc... At the end of every month I would be able to see how much business Customer 1 has generated which includes Customer 3 and Customer 4. I would also see how much business Customer 2 generated which also include Customer 3 and Customer 4.
Parameters
Master Form Setup:
*The form has multiple fields for the customer's name and contact information. ie.. First Name, Last Name, Address, Zip Code, Company they work for, which department they work in and what department they are ordering for
*The form has buttons at the bottom that show which product the customer is choosing to purchase. We can name these products Product A, Product B, Product C etc...
*The form has a field that can input which sales associate sold the product to the customer. (possible lookup feature for previously input associates?)
*The form has a field who referred them to the sales associate.
*The form has a field for when the product was purchased
*The form has a field for when they should be contacted again for a follow up call ie...3 months/6months after sale date.
Master Form Functionality:
*The user puts the required information into the form and can select which individual product has been chosen. The result is recorded in each individual product's table. (with an enter button?)
*The user has the ability to recall any entry and move it from one table to the other (with a “transfer” button?). The idea is that if there is a mistake we can record the mistake with another record that cancels it out and move it to a new table.
Individual Product Table Parameters:
*The individual table shows all customer information
*The individual table shows which sales person sold them the product
*The individual table has a field for when the product was sold
*The individual table has a field for when the customer is to be contacted which is automatically filled
Queries/Reports:
Not sure about how to run these queries/reports but the "questions" that I would like answered are the following...
*How many products each individual employee sold total?
*How many products each individual employee has sold within a given time. ie…Previous 7 days, 15 days, 30 days, 60 days, 90 days, 6 months, 1 year.
*How many products of each type have been sold?
*How many products in total have been sold?
*How many people were referred in total?
*How many people were referred by department?
*A breakdown of each customer, who they referred and who referred new customers. Show this from the oldest customer to the newest customer. (I see multiple pyramids in my mind but do not know how this would be output.)
Thanks in advance for the help. I have already spent one week on this and have gone to the drawing board 3 times. Anything and everything that will get me started and on the right path would be appreciated!
Reply With Quote
