Databases
StackOverflow2013
Postsdbo
POAccess 2003 - Running an update query based on select query results
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccess 2003 - Running an update query based on select query results
    primarykey
    data
    text
    <p>I currently have a subform that displays a select query. I want to update all the records of Table B that are showing in the subform with information from the form. The subform is not necessary. I was just using it to make sure my select query was displaying correctly.</p> <p>Table A has 3 columns (OID, Project_Number, Landowner)<br> Table B has 4 columns (OID, PhoneNum, Address, Year)<br></p> <p>These tables have a one to many relationship. One OID in Table A relates to many in Table B</p> <pre><code>Table A 1 A10 Bill 2 B10 Sally 3 A10 Bill Table B 1 555 123 blah st 2012 1 2013 2 111 456 aaa st 2012 3 2012 </code></pre> <p>The form allows the user to enter information that populates Table B. The subform displays a list of records where Project_Number, Landowner, and Year are equal to the record showing on the form</p> <pre><code>For example. If the form is showing 1 A10 Bill the subform is showing 1 A10 Bill 2012 3 A10 Bill 2012 </code></pre> <p>When I click a save command button I would like it to run the update query but I'm having issues with the SQL command.</p> <p>My Select query is as follows:</p> <pre><code>SELECT B.Project_Number, A.LANDOWNER, B.Year FROM A INNER JOIN B ON A.OBJECTID = A.OBJECTID; </code></pre> <p>The subform is setup<br> Link Child Fields: Project_Number; Year; Landowner<br> Link Master Fields: B.Project_Number; Year; A.Landowner <br></p> <p>I would like:<br></p> <pre><code>UPDATE B.PhoneNum, B.Address, B.Year WHERE items found in my subform WITH information from my form </code></pre> <p>Is it easier to forget the subform and do it all through a single update query?</p> <pre><code>UPDATE B SET B.phonenum = [New_Info]![PhoneNumCtrl], B.Address = [New_Info]![AddressCtrl] WHERE [A]![Landowner] = The same landowner as the OID selected, [A]![Project_Number] = The same project number as the OID selected, [New_Info]![Year] = [B]![Year] </code></pre> <p>Thanks in advance for any help!</p> <p>Everything is working now. I wanted to add to djphatic's answer.<br> When doing this make sure to add [Forms]![formname]![controlname]</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmkmitchell
    1. USmkmitchell
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload