UtterAccess.com
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    
 
   
mmchaley
post Jun 29 2020, 11:59 AM
Post#1



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?

Thanks,

Mark
Go to the top of the page
 
cheekybuddha
post Jun 29 2020, 12:08 PM
Post#2


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


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

Don't do it!

--------------------


Regards,

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


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


Have you tried making a crosstab query?


--------------------
Robert Crouser
Go to the top of the page
 
FrankRuperto
post Jun 29 2020, 02:39 PM
Post#4



Posts: 1,113
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
Post#5



Posts: 6,692
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>

hth
tina

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



Posts: 1,538
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
 
MadPiet
post Jun 29 2020, 03:23 PM
Post#7



Posts: 3,796
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    14th July 2020 - 07:13 AM