Lookup all possible matches in access

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
213
Hi

I have one master table and other as accounts table. In master table i have around 7-8 columns out of which two columns are account number and email_ids. and in accounts table i have several columns out of which one column is account number.

i want to create a query which Vlook-up all possible matches of email ids on the bases of account number in one cell field from master table.

To better understand this is how i do in excel link . i am looking for a similar solution in access.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,688
Office Version
2019
Platform
Windows
Maybe

Rich (BB code):
SELECT Master.ID, Master.email
FROM Master INNER JOIN Accounts ON Master.ID = Accounts.[Accnt Nr];
 

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
213
the query does not gives the result of all the matches in once cell ?
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,269
Office Version
365
Perhaps you should show us a mock up of what your expected output would be.
 

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
213
Hi below are two table from which i require the desired table. its just a look up of all the matches in front of the found account number in one cell.

Master table
NameDetailsEmail_idsAccount_No
Bilaldempothresh@co.in
456342​
ChaganSalibilal_786@gmail.com
4562486​
Kasmanimainsmash_add@.com
456342​

Accounts table
FDL detailsAccountCMF COAOthers
145260000​
456342​
XXX_code400-1123
478561000​
42144​
XXX_code400-1145
963412000​
456342​
XXX_code400-1145

Required output

FDL detailsAccountCMF COAOthersEmail_ids
145260000​
456342​
XXX_code400-1123thresh@co.in ; smash_add@.com
478561000​
42144​
XXX_code400-1145None
963412000​
456342​
XXX_code400-1145thresh@co.in ; smash_add@.com
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,688
Office Version
2019
Platform
Windows
Edit: Missed the point about consolidating in same field and record. Will relook at that.

I think that welshgasman has the right answer here.
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
822
Edit: Missed the point about consolidating in same field and record. Will relook at that.

I think that welshgasman has the right answer here.
TBH the original question did not even give impression to me. It was only the display in #5 that indicated to me that was what is required?
 

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
213
Thanks both . working as a treat for(me) an 15 days old access user !!

Other useful functions are also welcome if you have any ?
 

Jalal Kasmani

New Member
Joined
Feb 14, 2015
Messages
42
This is a great solution inside access.

Is there a way to query access database from excel considering the same scenario where the first two above tables would remain the same in access, and the required table or only the accounts_no would be there in column A of excel sheet, would it be possible to fetch all the possible emails in column b of excel.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,592
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top