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

Rey Obrero (Capricorn1)

you need VBA coding and recordset to do this.
how is your vba coding?

stevbe

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

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

insuremce

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.