I have a table that contains three colunms: “CustomerId” , “AppointmentDay” and “Seen By”. I need to find all customers seen by a specific individual during the time they worked at the organization, as well, once that individual left the company- when was/is the customer next appointment and the individual they were seen by. The table is all in access – is there an expression to pull such information?
Hi. I've attached a text file that does what you want. Import the excel sheet into access. The primary key is all 3 fields, I'd, date and staff working on the file. If you create a query and select SQL mode, paste the text in the text file. Execute the query and it will prompt for the person who left. Either John or Lois will show you. It joins the table to itself and looks for the last time John worked with someone. It joins to the next person working with that customer and shows the first time that they did. Look inside Table1 and you'll see what is in there. If it does what you want just join your table to itself. Select the group by and put the prompt in the same way. If the fids are really the same, you'll just have to change Table1 and Table1_1 in the sql.