Coupon Creation Query

Project Description: I want to advertise services that have not been used by my current clients. There are 6 tables Involved. JobType = List of all of our services Contacts = List of our customers Orders = All of the work previously done for the client Calls = Communication with the customer (we will store the coupons given here) ContactEmployee = Employees that work for our customers ContactsAcc = Additional information about contacts. We will need to reference the Prospecting field I have currently created a Union Query (CouposAndServices) so that I can see all of the jobtypes used from orders and all of the coupons that have been given. What I need to do is Pull a jobtype that has not been used (and a coupon that has been sent in the last 6 months) at random from the remaining available jobtypes and create a list for all of the customers so I can export that to excel for a mail merge. If the Prospecting field is selected in the contactsAcc table then it will create a coupon for every “employee” of that company. If it is not selected then we will just create one coupon for the company using the default contact info. [login to view URL] [login to view URL] for the name. so Each line of the list would look something like this: ID,Name,Fname,Lname,Addr1,City,State,Zip,Coupon,CouponLocation,CouponAddText We also need to write the Coupon that is currently being issued to the Calls table with the date so that we don't use the same coupon next time. We need to also include the Employee that we sent the coupon to so we can track that employee and not send the same coupon to them next month. I have added some fields to JobType so that I can “turn off” the coupons for any of our services.