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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros