Lookup all possible matches in access

[Ebraham]

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.

 

[alansidman]

Maybe

SELECT Master.ID, Master.email
FROM Master INNER JOIN Accounts ON Master.ID = Accounts.[Accnt Nr];

 

[Ebraham]

the query does not gives the result of all the matches in once cell ?

 

[jackd]

Perhaps you should show us a mock up of what your expected output would be.

 

[Ebraham]

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

Name	Details	Email_ids	Account_No
Bilal	dempo	thresh@co.in	456342​
Chagan	Sali	bilal_786@gmail.com	4562486​
Kasmani	main	smash_add@.com	456342​

Accounts table

FDL details	Account	CMF COA	Others
145260000​	456342​	XXX_code	400-1123
478561000​	42144​	XXX_code	400-1145
963412000​	456342​	XXX_code	400-1145

Required output

FDL details	Account	CMF COA	Others	Email_ids
145260000​	456342​	XXX_code	400-1123	thresh@co.in ; smash_add@.com
478561000​	42144​	XXX_code	400-1145	None
963412000​	456342​	XXX_code	400-1145	thresh@co.in ; smash_add@.com

 

[welshgasman]

Use Allen Browne's Concat function

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[alansidman]

Edit: Missed the point about consolidating in same field and record. Will relook at that.

I think that welshgasman has the right answer here.

 

[welshgasman]

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]

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]

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.