troubleshooting Question

Update Query to update one record from multiple records

Avatar of insuremce
insuremce asked on
Microsoft AccessSQL
4 Comments1 Solution394 ViewsLast Modified:
I have the below data in a table:

tbl_Names
ID      bpyrefno                            Name#                           
788      GREX48MC01      Mr Ryan Boydell      
980      GRJB20MC01      Mrs Paula Greenwood      
875      GRSA59MC01      Mr John Green      
368      HABZ23MC01      Mrs Fiona Hacking      
368      HABZ23MC01      Dr Matthew Hacking      
368      HABZ23MC01      Dr Joanna Hacking      

I need an update query that takes this date and updates another table:

tbl_details
ID      bpyrefno                            Name1                       Name2                 Name3               Name4
788      GREX48MC01            
980      GRJB20MC01            
875      GRSA59MC01            
368      HABZ23MC01      

in the example above for ID 788, 980 and 875 the query needs to simply update Name1 on tbl_Details with the value from tbl_Names. However ID 368 needs to update Name1 with the first record with ID 368 and Name2 with the next record with ID 368 and so on.

Therefore after the query the data in tbl_Details will be:
ID      bpyrefno                            Name1                      Name2                         Name3                 Name4
788      GREX48MC01      Mr Ryan Boydell      
980      GRJB20MC01      Mrs Paula Greenwood      
875      GRSA59MC01      Mr John Green            
368      HABZ23MC01                    Mrs Fiona Hacking    Dr Matthew Hacking     Dr Joanna Hacking


Many thanks experts.
Adam
ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 4 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 4 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004