14678

How to insert a new field into a table which related to another table?

Question:

I have two tables in MS Access 2013.

There are some conditions:<br /> 1. Part can only be withdrawn after serviced.<br /> 2. After certain time, part will be returned for service again.<br /> 3. Part can be recycled.

<h2>tblService:</h2>

(ID is the primary key)

ID PART_ID SERV_DATE 1 A0001 11/1/2013 2 A0001 11/13/2013 3 A0001 11/25/2013 4 B0001 11/26/2013 5 C0001 12/1/2013 6 C0001 12/10/2013 7 C0001 12/20/2013 8 A0001 12/21/2013 <h2>tblWithdraw:</h2>

(ID is the primary key)

ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO 1 A0001 11/6/2013 PM 601 2 A0001 11/20/2013 120 PM 603 3 A0001 11/30/2013 REPLACEMENT 605 4 C0001 12/2/2013 30 PM 701 5 C0001 12/15/2013 180 PM 702 6 B0001 12/18/2013 PM 801 7 A0001 12/25/2013 60 PM 502 <br /> I'd like to insert a new field, <strong>SERVICE_ID</strong>, into tblWithdraw, look like this: ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO SERVICE_ID 1 A0001 11/6/2013 PM 601 1 2 A0001 11/20/2013 120 PM 603 2 3 A0001 11/30/2013 REPLACEMENT 605 3 4 C0001 12/2/2013 30 PM 701 5 5 C0001 12/15/2013 180 PM 702 6 6 B0001 12/18/2013 PM 801 4 7 A0001 12/25/2013 60 PM 502 8

which <strong>SERVICE_ID</strong> is the <strong>ID</strong> in <strong>tblService</strong>.

However, when I tried codes below in the query:

<pre class="lang-sql prettyprint-override">INSERT INTO tblWithdraw ( SERVICE_ID ) SELECT ID

FROM tblService;

It gave me:

ID PART_ID DRAWN_DATE DRAWN_REASON DRAWN_TO SERVICE_ID 1 A0001 11/6/2013 PM 601 2 A0001 11/20/2013 120 PM 603 3 A0001 11/30/2013 REPLACEMENT 605 4 C0001 12/2/2013 30 PM 701 5 C0001 12/15/2013 180 PM 702 6 B0001 12/18/2013 PM 801 7 A0001 12/25/2013 60 PM 502 8 1 9 2 10 3 11 4 12 5 13 6 14 7 15 8 Or codes below: <pre class="lang-sql prettyprint-override">INSERT INTO tblWithdraw (SERVICE_ID) SELECT ID FROM tblService WHERE tblService.PART_ID =tblWithdraw.PART_ID;

The system can't recognize <strong>tblWithdraw.PART_ID</strong>.

Can anyone correct my codes or give me answer or direct me to the solution? Thanks!

Answer1:

As you have discovered, INSERT statements always add new rows to a table. You want to UPDATE existing rows using something like this:

<pre class="lang-sql prettyprint-override">UPDATE tblWithdraw SET SERVICE_ID = DLookup("ID","tblService","PART_ID='" & PART_ID & "' AND SERV_DATE=#" & Format(DMax("SERV_DATE","tblService","PART_ID='" & PART_ID & "' AND SERV_DATE<=#" & Format(DRAWN_DATE,"yyyy-mm-dd") & "#"),"yyyy-mm-dd") & "#")

It uses DMax() to find the most recent SERV_DATE that precedes a given DRAWN_DATE, then does a DLookup() to find the corresponding [tblService].[ID], and then plugs it into the SERVICE_ID column in [tblWithdraw].

Recommend

  • link excel columns
  • join two tables and show all records
  • How to open a link in a new tab with Python selenium ChromeDriver
  • Display first element of a one-to-many relationship in Flask-Admin
  • Compare dates between 2 different rows and columns
  • Ruby on Rails: Selectize.js with Cocoon
  • Insert Statement
  • Select from table that does not exist
  • Cassandra 2.1: Recursion by nesting UDT's
  • Can't see some objects' internals in Qt Creator's Locals and Expressions debug window
  • Recreate the Oracle DUAL table
  • Insert records if not exist SQL Server 2005
  • how to query for min or max inet/cidr with postgres
  • d3.js selection conditional rendering
  • Use default value of a column in stored procedures
  • INSERT EXEC Statement cannot be nested [duplicate]
  • AlertDialog style when using setView()
  • Returning the auto incrementing value after an insert using slick
  • How can Delete be both a DDL and a DML statement
  • Enabling DTD support in Sql Server
  • SAXReader not re-ecape characters
  • Adding elements to a huge XML file
  • close() was never explicitly called on database
  • Eliminate partial duplicate rows from result set
  • Transactional Create with Validation in ServiceStack Redis Client
  • Why querying a date BC is changed to AD in Java?
  • Database structure design with variable amounts of fields
  • ilmerge with a PFX file
  • Why value captured by reference in lambda is broken? [duplicate]
  • PHP - How to update data to MySQL when click a radio button
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Why joiner is not used after Sequence generator or Update statergy
  • How to stop GridView from loading again when I press back button?
  • Recursive/Hierarchical Query Using Postgres
  • UserPrincipal.Current returns apppool on IIS