Change records in Append query. 1

[rproactive]

Hi -
I can't figure out how to procced--- help please.

I want to select some records drom a table (based on col 1 being equal t 1 and column 2 being equal to 12 for example). I would like to then change column 2 to( to 56 for example) and append the new records to the original table.

I have the select query working and an append query also working however don't know how to 1st change the records in column 2.

Any ideas on how to do this?

 

[dhookom]

I have the select query working and an append query also working"... Post your SQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rproactive]

Dhookom/Duane- thanks for you prompt reply

Select query

SELECT 
  M_RRoll.RRollSites__IDs, 
  M_RRoll.RRollRR_IDs, 
  M_RRoll.RRollLeases_IDs, 
  M_RRoll.RRollUnit, 
  M_RRoll.RRollSchedRentAmt, 
  M_RRoll.RRollScheSCEPAmt, 
  M_RRoll.RRollSchedOtherAmt,     
  M_RRoll.RRollSchedOtherDescription, 
  M_RRoll.RRollTenant 
FROM 
M_RRoll
WHERE 
(((M_RRoll.RRollSites__IDs)=[Forms]![F_RRoll]![RRollSites__IDs]) AND ((M_RRoll.RRollRR_IDs)=[Forms]![F_RRoll]![RRollRR_IDs]))
ORDER BY M_RRoll.RRollUnit;

Append Query

INSERT INTO 
  M_RRoll ( RRollSites__IDs, RRollRR_IDs, RRollLeases_IDs, RRollUnit, RRollSchedRentAmt, RRollScheSCEPAmt, RRollSchedOtherAmt, RRollSchedOtherDescription, RRollTenant, RRollSctyDue, RRollAnv )
SELECT 
  M_RRoll.RRollSites__IDs, 
  M_RRoll.RRollRR_IDs, 
  M_RRoll.RRollLeases_IDs, 
  M_RRoll.RRollUnit, 
  M_RRoll.RRollSchedRentAmt, 
  M_RRoll.RRollScheSCEPAmt, 
  M_RRoll.RRollSchedOtherAmt, 
  M_RRoll.RRollSchedOtherDescription, 
  M_RRoll.RRollTenant
FROM M_RRoll
WITH OWNERACCESS OPTION;

 

[dhookom]

I don't see column 1 and column 2 etc ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rproactive]

Duane-

Sorry. Column 1 is the 1st field (M_RRoll.RRollSites__IDs) and column 2 is 2nd (M_RRoll.RRollRR_IDs). They are selected on form F_RRoll.

Need any additional info? Thanks for your resposne!

 

[Golom]

Your INSERT INTO has 11 fields specified in the field list but the SELECT statement is returning only 9 fields. The two must be the same.

 

[rproactive]

during trouble shooting I have gone thru several interations the correct code is-

Select query

SELECT 
  M_RRoll.RRollSites__IDs, 
  M_RRoll.RRollRR_IDs, 
  M_RRoll.RRollLeases_IDs, 
  M_RRoll.RRollUnit, 
  M_RRoll.RRollSchedRentAmt, 
  M_RRoll.RRollScheSCEPAmt, 
  M_RRoll.RRollSchedOtherAmt,     
  M_RRoll.RRollSchedOtherDescription, 
  M_RRoll.RRollTenant,
  M_RRoll.RRollSctyDue, 
  M_RRoll.RRollAnv 

FROM 
M_RRoll
WHERE 
(((M_RRoll.RRollSites__IDs)=[Forms]![F_RRoll]![RRollSites__IDs]) AND ((M_RRoll.RRollRR_IDs)=[Forms]![F_RRoll]![RRollRR_IDs]))
ORDER BY M_RRoll.RRollUnit;

 

[PHV]

You wanted something like this ?
INSERT INTO
M_RRoll ( RRollSites__IDs, RRollRR_IDs, RRollLeases_IDs, RRollUnit, RRollSchedRentAmt, RRollScheSCEPAmt, RRollSchedOtherAmt, RRollSchedOtherDescription, RRollTenant, RRollSctyDue, RRollAnv )
SELECT
RRollSites__IDs,
[!]56[/!],
RRollLeases_IDs,
RRollUnit,
RRollSchedRentAmt,
RRollScheSCEPAmt,
RRollSchedOtherAmt,
RRollSchedOtherDescription,
RRollTenant,
RRollSctyDue,
RRollAnv
FROM M_RRoll
WHERE RRollSites__IDs=[Forms]![F_RRoll]![RRollSites__IDs]
AND RRollRR_IDs=[Forms]![F_RRoll]![RRollRR_IDs]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rproactive]

PH- this is exactly what i needed thanks
many ******