UpDate Query

  • Thread starter Thread starter Linda Ribbach
  • Start date Start date
L

Linda Ribbach

Hi,

How can I change the following append query to an update query?

INSERT INTO tblBLTotals ( RecordNo, EntryDate, PayBL, PayMC, PaymentsDR, IntBL, IntMC, InternalDR, CRBL, CRMC, CRDR, CX )
SELECT Right( CStr((SELECT COUNT(*) + 1 FROM tblBLTotalsPrelim T2 WHERE T2.EntryDate < T1.EntryDate)),3) AS RecordNo, T1.EntryDate AS Expr1, T1.PayBL, T1.PayMC, T1.PaymentsDR, T1.IntBL, T1.IntMC, T1.InternalDR, T1.CRBL, T1.CRMC, T1.CRDR, T1.CX
FROM tblBLTotalsPrelim AS T1
ORDER BY T1.EntryDate;

Thanks in advance
 
Hi Linda,

Here's the abbreviated version (but you'll get the picture):

UPDATE tblBLTotals INNER JOIN (your select statement) T1 ON
tblBLTotals.RecordNo = T1.RecordNo SET tblBLTotals.EntryDate =
T1.EntryDate, tblBLTotals.PayBL = T1.PayBL;


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights




--------------------
| From: "Linda Ribbach" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: UpDate Query
| Date: Tue, 11 May 2004 14:22:41 -0500
| Organization: University of Minnesota, Twin Cities Campus
| Lines: 10
| Message-ID: <[email protected]>
| X-Trace: lenny.tc.umn.edu 1084303368 27329 160.94.49.195 (11 May 2004
19:22:48 GMT)
| X-Complaints-To: (e-mail address removed)
| X-Newsreader: Groupwise 6.5
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!news-out.cwix.com!newsfeed.cwix.com!newspeer.monmouth.com!news-out
.visi.com!petbe.visi.com!news.octanews.net!upp1.onvoy!msc1.onvoy!onvoy.com!l
enny.tc.umn.edu!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:200353
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi,
|
| How can I change the following append query to an update query?
|
| INSERT INTO tblBLTotals ( RecordNo, EntryDate, PayBL, PayMC, PaymentsDR,
IntBL, IntMC, InternalDR, CRBL, CRMC, CRDR, CX )
| SELECT Right( CStr((SELECT COUNT(*) + 1 FROM tblBLTotalsPrelim T2
WHERE T2.EntryDate < T1.EntryDate)),3) AS RecordNo, T1.EntryDate AS Expr1,
T1.PayBL, T1.PayMC, T1.PaymentsDR, T1.IntBL, T1.IntMC, T1.InternalDR,
T1.CRBL, T1.CRMC, T1.CRDR, T1.CX
| FROM tblBLTotalsPrelim AS T1
| ORDER BY T1.EntryDate;
|
| Thanks in advance
|
 
Hi Eric,

The following is what I did: I'm getting an error. "Syntax error in the From clause"

UPDATE tblBLTotals INNER JOIN (SELECT Right( CStr((SELECT COUNT(*) + 1 FROM tblBLTotalsPrelim T2 WHERE T2.EntryDate < T1.EntryDate)),3) T1 on tblBLTotal.RecordNo = T1.RecordNo SET tblBLTotals.EntryDate = T1.EntryDate, tblBLTotals.PayBL=T1.PayBL;


Thanks Linda

The following is the original SQL:

INSERT INTO tblBLTotals ( RecordNo, EntryDate, PayBL, PayMC, PaymentsDR, IntBL, IntMC, InternalDR, CRBL, CRMC, CRDR, CX )
SELECT Right( CStr((SELECT COUNT(*) + 1 FROM tblBLTotalsPrelim T2 WHERE T2.EntryDate < T1.EntryDate)),3) AS RecordNo, T1.EntryDate AS Expr1, T1.PayBL, T1.PayMC, T1.PaymentsDR, T1.IntBL, T1.IntMC, T1.InternalDR, T1.CRBL, T1.CRMC, T1.CRDR, T1.CX
FROM tblBLTotalsPrelim AS T1
ORDER BY T1.EntryDate;
 
Back
Top