insuremce
asked on
Update Query to update one record from multiple records
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
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
Last Comment
while this layout may be easier to work with in creating a report, it would not be considered a *normalized* data structure and you will likely want to do something later that pulls the names all back together (for a different report) and there simply will be no way to do that without running VBA code. Are you really sure you want to go down this path :-) You would build a crosstab query.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Capricorn,
That seems to be working great. I did have to change it to a function rather than a sub, so that I could call it from a macro.
That seems to be working great. I did have to change it to a function rather than a sub, so that I could call it from a macro.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
how is your vba coding?