X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Update Query - Match Record To Field Name, Access 2016    
post Jun 29 2020, 11:59 AM

Posts: 67
Joined: 26-April 16

Hello all,

I have been trying to figure out how to google the following problem and not gaining much ground.

I won't be attaching the database as it has all propitiatory data in it.

What I am trying to do is update a table based on matching the value in a record with a field name

Example -

I have a table 1 with a field named animals with records Cat, Dog, Mouse, Elephant...
Then I have table 2 with the field names Cat, Dog, Elephant...

I want to update a record in table 2 where record Cat matches Field Name Cat

Is this possible, if so, what would the SQL and/or VBA code look like?


Go to the top of the page
post Jun 29 2020, 12:08 PM

UtterAccess Moderator
Posts: 13,012
Joined: 6-December 03
From: Telegraph Hill

>> Then I have table 2 with the field names Cat, Dog, Elephant... <<

Don't do it!



David Marten
Go to the top of the page
post Jun 29 2020, 12:18 PM

UtterAccess VIP
Posts: 12,381
Joined: 10-February 04
From: South Charleston, WV

Have you tried making a crosstab query?

Robert Crouser
Go to the top of the page
post Jun 29 2020, 02:39 PM

Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA

Using a field for each type of animal is a bad design, you're better of using just one animal field and use an animal lookup table to store the ID number instead of the text description.

Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
tina t
post Jun 29 2020, 03:07 PM

Posts: 6,680
Joined: 11-November 10
From: SoCal, USA

hi Mark, i agree with everyone else that posted. if it's your design, i strongly urge you to fix it. if it's somebody else's design that you're stuck with, and trying to work with, i fully sympathize. in that case, yes, i think you could do it in VBA with two loops:

write a query to pull the target record(s) in Table1. open a recordset against that query, as

Dim rst1 As DAO.Recordset
Set rst1 = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)

write a second query to pull the target record(s) in Table2, and ditto above, as

Dim rst2 As DAO.Recordset
Set rst2 = CurrentDb.OpenRecordset("Query2", dbOpenDynaset)

you don't say how you would identify the record(s) in Table2 that you want to update, or what you want to update the data to, so i can only say that you can use the data in field Animals in the record in Table1, to provide the fieldname for Table2, as

rst2(rst1("Animals")) = <some value>


"the wheel never stops turning"
Go to the top of the page
post Jun 29 2020, 03:19 PM

Posts: 1,519
Joined: 25-January 16
From: The Great Land

Review https://www.accessforums.net/showthread.php?t=21972 and https://stackoverflow.com/questions/6261544...615623#62615623

You could provide db with a few dummy data records.
This post has been edited by June7: Jun 29 2020, 03:20 PM

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
post Jun 29 2020, 03:23 PM

Posts: 3,784
Joined: 27-February 09

Could you post at least the structure of the tables you're trying to query?

but I agree with the other commenters here ... I've worked with data like that, and it's going to be a huge hassle. Maybe you can export it to Excel, unpivot it, and then reimport, but I don't know yet. (That way I can get the information out of the column names and into the table records.)
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    7th July 2020 - 11:27 AM