Home

Access Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-392102%22%20slang%3D%22en-US%22%3EAccess%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392102%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20that%20contains%20three%20colunms%3A%20%E2%80%9CCustomerId%E2%80%9D%20%2C%20%E2%80%9CAppointmentDay%E2%80%9D%20and%20%E2%80%9CSeen%20By%E2%80%9D.%20I%20need%20to%20find%20all%20customers%20seen%20by%20a%20specific%20individual%20during%20the%20time%20they%20worked%20at%20the%20organization%2C%20as%20well%2C%20once%20that%20individual%20left%20the%20company-%20when%20was%2Fis%20the%20customer%20next%20appointment%20and%20the%20individual%20they%20were%20seen%20by.%3CBR%20%2F%3EThe%20table%20is%20all%20in%20access%20%E2%80%93%20is%20there%20an%20expression%20to%20pull%20such%20information%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-392102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393414%22%20slang%3D%22en-US%22%3ERe%3A%20Access%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393414%22%20slang%3D%22en-US%22%3ESorry.%20Can't%20use%20text%20files.%20Here's%20a%20spreadsheet.%20Just%20copy%20the%20cell%20and%20paste%20into%20your%20query.%3CBR%20%2F%3EGeorge%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393410%22%20slang%3D%22en-US%22%3ERe%3A%20Access%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393410%22%20slang%3D%22en-US%22%3EHi.%20I've%20attached%20a%20text%20file%20that%20does%20what%20you%20want.%3CBR%20%2F%3EImport%20the%20excel%20sheet%20into%20access.%3CBR%20%2F%3EThe%20primary%20key%20is%20all%203%20fields%2C%20I'd%2C%20date%20and%20staff%20working%20on%20the%20file.%3CBR%20%2F%3EIf%20you%20create%20a%20query%20and%20select%20SQL%20mode%2C%20paste%20the%20text%20in%20the%20text%20file.%3CBR%20%2F%3EExecute%20the%20query%20and%20it%20will%20prompt%20for%20the%20person%20who%20left.%20Either%20John%20or%20Lois%20will%20show%20you.%3CBR%20%2F%3EIt%20joins%20the%20table%20to%20itself%20and%20looks%20for%20the%20last%20time%20John%20worked%20with%20someone.%20It%20joins%20to%20the%20next%20person%20working%20with%20that%20customer%20and%20shows%20the%20first%20time%20that%20they%20did.%3CBR%20%2F%3ELook%20inside%20Table1%20and%20you'll%20see%20what%20is%20in%20there.%3CBR%20%2F%3EIf%20it%20does%20what%20you%20want%20just%20join%20your%20table%20to%20itself.%20Select%20the%20group%20by%20and%20put%20the%20prompt%20in%20the%20same%20way.%20If%20the%20fids%20are%20really%20the%20same%2C%20you'll%20just%20have%20to%20change%20Table1%20and%20Table1_1%20in%20the%20sql.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20helps.%3CBR%20%2F%3E%3CBR%20%2F%3EGeorge%3C%2FLINGO-BODY%3E
nad_123
New Contributor

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?

2 Replies
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.

Hope this helps.

George
Sorry. Can't use text files. Here's a spreadsheet. Just copy the cell and paste into your query.
George
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies