Lookup all possible matches in access

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,376
Office Version
  1. 365
Platform
  1. 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
215
the query does not gives the result of all the matches in once cell ?
 

jackd

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

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215

ADVERTISEMENT

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
6,376
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
899
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
215
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
45
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,351
Messages
5,680,991
Members
419,948
Latest member
Sbakker1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top