Update Query (with Nested Sql) Not Working
Sep 23, 2007
hi guys,
i have this sql that filters 2 tables to give me the highest number against a record and then i want to update the 1st table. but it dosnt seam to work it brings back the correct data but the sql has generated a non-updatable query.
any ideas?
UPDATE [SELECT ARCtblErrorInfo.Uber, Max(ARCtblHistory.Status) AS MaxOfStatus
FROM ARCtblErrorInfo LEFT JOIN ARCtblHistory ON ARCtblErrorInfo.Uber = ARCtblHistory.Uber
WHERE (((ARCtblErrorInfo.Status)=121))
GROUP BY ARCtblErrorInfo.Uber]. AS test INNER JOIN ARCtblErrorInfo ON test.Uber = ARCtblErrorInfo.Uber SET ARCtblErrorInfo.Status = [test].[MaxOfStatus];
View Replies
ADVERTISEMENT
Aug 2, 2005
Hi all,
I'm sure this is easy to do but for some reason i just cannot get it working. Hopefully someone here can tell me where i'm going wrong.
I have two tables in a database and the have a linking unique field. I want to update a column in one table from a column in another where the linking ID field matches.
Here's what i have at present.
Update Table_1 inner join Table_2 On Table_1.ID = Table_2.ID
Set Table_1.[CHI Number] = [Table_2]![CHI_No]
This is just returning the CHI Number column as blanks instead of what i expected would be the same as Chi_No.
Can anyone shed some light on this for me?
Thanks in advance
View 2 Replies
View Related
Jan 12, 2006
Hello all,
I have the below update query running
UPDATE tbl_master SET tbl_master.COLLRAW =
Iif(tbl_master.PSTATE = "WV","WV",
Iif(tbl_master.PSTATE = "MA","MA",
Null))
WHERE tbl_master.EXCLUDEREASON Is Null
but when I add this line:
UPDATE tbl_master SET tbl_master.COLLRAW =
Iif(tbl_master.PSTATE = "WV","WV",
Iif(tbl_master.PSTATE = "MA","MA",
Iif(tbl_master,MSPBANK in ("751","752","753","854","855"),"GS",
Null)))
WHERE tbl_master.EXCLUDEREASON Is Null
It give me an error message that says "Wrong number of arguments in Query Expression." Can anyone tell me what is wrong with the query above?
Thanks in advance,
Vassago
View 2 Replies
View Related
Apr 24, 2007
Hi Everyone,
Here's the situation:-
I have a patient record with a specific consultant assigned to the patient. In some instances, the patient may request a different consultant. What I want to do is to allow the user to change the consultant and to save details about the change e.g. file no, old value, new value, date etc.
This is what I have done so far:-
I created a Query to retrieve details from 3 tables and assigned the value to be changed to a variable (oldvalue)
I then allow the user to select from a combo box the new consultant.
Details of these changes are then written to a table called tblchanges.
All the above is working.
The problem occurs when I try to update the consultant table, by replacing the old consultant with the newly selected consultant.
Can someone look at the code and let me know first if I am approaching this problem correctly and then why the code is not working.
Private Sub Combo36_Change()
Dim flno As String
Dim fldnme As String
Dim oldval As String
Dim newval As String
Dim tdate As Date
Dim ttime As Date
Dim sqn As String
Dim yrn As String
flno = PFILENO
fldnme = "Radiation Oncologst"
oldval = Text29
newval = Combo36
tdate = Date
ttime = Time
'Insert change values into tblchanges
strSql = "INSERT INTO tblchanges " _
& "( fileno,fieldname, oldvalue, newvalue, datechg,timechg ) " _
& "VALUES ('" & flno & "','" & fldnme & "','" & oldval & "','" & newval & "',#" & tdate & "#,'" & ttime & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
strFILENO = (Left(Me!PFILENO, 4) & "/" & Right(Me!PFILENO, 4))
'update consultant table with new value
stsql2 = "UPDATE tblatdocts SET roncologist = '" & newval & "' "
stsql2 = stsql2 & "WHERE tblatdocts.FILENO = '" & strFILENO & "' "
DoCmd.SetWarnings False
DoCmd.RunSQL stsql2
DoCmd.SetWarnings True
End Sub
Thanks so much for your help.
Will
View 1 Replies
View Related
May 19, 2005
Hi guys,
I have a text field in a database.
I run an update query and the database updates when the form field is text e.g 'location' or numerical '1234'. However if it is mixed, e,g VV1234 it fails to update and i get an update error.
Is this an Access thing? Is there a work around?
Thanks
ronan
View 1 Replies
View Related
Sep 6, 2007
Hello,
I am having a problem w/ my UPDATE statement when the date value is included in the statement. I have tried surrounding the date variable (strDate) in "#" and w/ and w/out single quotes to no avail. I doesn't matter if I dimension strDate as a string or a date. I know there must be a simple solution. In short, the UPDATE statement looks like this:
strSQL = "UPDATE tblTasks SET POC = '" & strPOC & "', Date = #" & strDate & "#, Task = '" & strTask & "' WHERE id = " & lID & ";"
If I remove Date = #" & strDate & "#, then the query works.
These also fail:
Date = #'" & strDate & "'#,
Date = '#" & strDate & "#',
Date = '" & strDate & "',
Any help would be greatly appreciated. Thanks.
Ken
View 3 Replies
View Related
Sep 6, 2007
Hello,
I am having a problem w/ my UPDATE statement when the date value is included in the statement. I have tried surrounding the date variable (strDate) in "#" and w/ and w/out single quotes to no avail. I doesn't matter if I dimension strDate as a string or a date. I know there must be a simple solution. In short, the UPDATE statement looks like this:
strSQL = "UPDATE tblTasks SET POC = '" & strPOC & "', Date = #" & strDate & "#, Task = '" & strTask & "' WHERE id = " & lID & ";"
If I remove Date = #" & strDate & "#, then the query works.
These also fail:
Date = #'" & strDate & "'#,
Date = '#" & strDate & "#',
Date = '" & strDate & "',
Any help would be greatly appreciated. Thanks.
Ken
View 2 Replies
View Related
Mar 23, 2007
I am using an update query comparing 2 tables to add new records from table 2 to table 1. This worked fine until we upgraded to Office 2007. Is this a function of 2007?
I am not getting any prompts to OK, "You are about to update 1500 records"
Thanks for any help.
-Marcus
View 1 Replies
View Related
Jun 26, 2014
I have this update query that is triggered by an after update event on a main form. The record being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.
I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:
Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim db As Database
Dim strSql As String
Dim lngID As Long
Dim dblRate As Double
Set db = CurrentDb
[Code] ....
View 14 Replies
View Related
Jul 31, 2013
I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table.
Code:
SQLstock1 = "UPDATE TBL_STOCK SET Stock = '" & DLookup("RemainingQty", "QRY_STOCK", "ActionEntity = '" & Me.cmb_customer1 & "'" And "StockType = '" & Me.cmb_stock_type1 & "'") & "' WHERE StockEntity = '" & Me.cmb_customer1 & "'"
View 1 Replies
View Related
Jun 27, 2014
Why update query isn't working when trying to update an multivalued field. In this table, there are 647 records and only 9 of which already have a value set in the "AssociatedProject" field. More than one AssociatedProject is associated with a vendor, so this field allows for multiple entries. I've looked at all the information on update queries and updating a multivalued field, but it will only update the existing 9 records. It does not recognize the remaining ones (they have no values yet).
Here's the SQL:
UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject.[Value] = 3
WHERE (((Tbl_Vendor.AssociatedProject.Value) Is Null));
View 9 Replies
View Related
Dec 23, 2013
Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
View 3 Replies
View Related
Mar 12, 2014
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
[code]...
View 3 Replies
View Related
Apr 26, 2006
Hello everyone,
I’m looking for some help with a “nested query”
I have four fields that typically have a quantity amount in them.
I also have a field with a date.
I would like to say something like this
If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank
Thanks a lot
Corey
View 2 Replies
View Related
Aug 12, 2007
I'm wanting to run an update query but first I want to see the results by using a select query to make sure that I'm getting the correct results.
See the following query statement:
SELECT ([Goodrec - All] INNER JOIN WH_ACCTCOMMON ON [Goodrec - All].ACCTNO = WH_ACCTCOMMON.ACCTNBR) INNER JOIN [Trial-GL-Reference] ON WH_ACCTCOMMON.CURRMIACCTTYPCD = [Trial-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode], [Goodrec - All].CURRBAL = IIf(Not IsNull([chgoffamt]) And Not IsNull([partsold]),CCur([grossbal])-CCur([chgoffamt])-CCur([partsold]),IIf(Not IsNull([CHGOFFAMT]),CCur([grossbal])-CCur([CHGOFFAMT]),IIf(Not IsNull([partsold]),CCur([grossbal])-CCur([partsold]),[grossbal]))), [Goodrec - All].PCTOWNED = IIf(Not IsNull([origamt]) And Not IsNull([partsold]), CCur([origamt])-CCur([partsold]/CCur([origamt]), IIf(IsNull([partsold]), [grossbal]));
This is the error that I receive each time I attempt to run it and I'm not sure what I'm missing.
Error message:
MICROSOFT OFFICE ACCESS
Syntax error(missing operator) in query expression '([Goorec-ALL] Inner Join WH_ACCTCOMMON ON [Goodrec-All].ACCTNO=WH_ACCTCOMMON.ACCTNBR) inner join [TRIAL-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode]'
Any help with this issue would be greatly appreciated.
Thanks.
Govmate!
View 3 Replies
View Related
Sep 23, 2007
Dear Access Expert
I have been doing some experimentation with Queries and I have discovered something unusual.
If I have one query and I use the totals feature (only using group by) I get all the records based on the inputed criteria
If I then use that same query as an underlying recordset for a second query and I use the totals feature (only using group by) in the second query... I only get the first record of each type. See query results below..
I am trying to achieve the results of the second query in the first query (DON'T WANT NESTED Queries) and I don't really understand why the results of the queries are different when everything else is the same other than one being nested.
Example below
First Query Results (using totals with group by)
1 a
1 a
1 a
2 a
2 a
2 a
3 a
3 a
3 a
4 a
4 a
5 a
Second Query Results with the First query as its recordset (using Group By)
1 a
2 a
3 a
4 a
5 a
View 3 Replies
View Related
Feb 24, 2005
Hi folks,
I have a query which queries another query. I need to re-write the whole thing in SQL, but struggling with incorporating the nested query.
here's the main query:
SELECT tblProductSubCategory.SubCatDesc AS Products, qrySummaryStage3.[Early Cash Sales], qrySummaryStage3.[Late Cash Sales], qrySummaryStage3.[Cash Variance], qrySummaryStage3.[Cash Variance %], qrySummaryStage3.[Early Credit Sales], qrySummaryStage3.[Late Credit Sales], qrySummaryStage3.[Credit Variance], qrySummaryStage3.[Credit Variance %], qrySummaryStage3.[Early Total], qrySummaryStage3.[Late Total], qrySummaryStage3.[Variance Total], qrySummaryStage3.[Total Variance %], tblBudgetNew.Amount AS [Late Budget Amount], [Late Total]-[Late Budget Amount] AS [Budget Variance], qrySummaryStage3.[Early Cash Margin %], qrySummaryStage3.[Late Cash Margin %], qrySummaryStage3.[Early Credit Margin %], qrySummaryStage3.[Late Credit Margin %]
FROM (qrySummaryStage3 INNER JOIN tblBudgetNew ON qrySummaryStage3.SubCatID = tblBudgetNew.SubCatID) INNER JOIN tblProductSubCategory ON qrySummaryStage3.SubCatID = tblProductSubCategory.SubCatID
WHERE (((Month([date]))=1) AND ((Year([date]))=2005) AND ((tblBudgetNew.Site)=[Enter Site ID]) AND ((tblBudgetNew.Type)="Monthly Breakdown"));
and here's the nested query , called "qrySummaryStage3":
SELECT tblProductSubCategory.SubCatID, tblReportTemp1.[Early Cash Sales], tblReportTemp1.[Late Cash Sales], [Late Cash Sales]-[Early Cash Sales] AS [Cash Variance], IIf(nz([early cash sales],0)=0 Or [Early Cash Sales]=0,0,[Cash Variance]/[Early Cash Sales]) AS [Cash Variance %], tblReportTemp1.[Early Credit Sales], tblReportTemp1.[Late Credit Sales], [Late Credit Sales]-[Early Credit Sales] AS [Credit Variance], IIf(nz([Early Credit Sales],0)=0 Or [early Credit Sales]=0,0,[Credit Variance]/[Early Credit Sales]) AS [Credit Variance %], [Early Cash Sales]+[Early Credit Sales] AS [Early Total], [Late Cash Sales]+[Late Credit Sales] AS [Late Total], [Late Total]-[Early Total] AS [Variance Total], IIf([Early Total]=0,0,[Variance Total]/[Early Total]) AS [Total Variance %], IIf([Early Cash Sales]=0,0,([Early Cash Sales]-[Early Cash Cost])/[Early Cash Sales]) AS [Early Cash Margin %], IIf([Late Cash Sales]=0,0,([Late Cash Sales]-[Late Cash Cost])/[Late Cash Sales]) AS [Late Cash Margin %], IIf([Early Credit Sales]=0,0,([Early Credit Sales]-[Early Credit Cost])/[Early Credit Sales]) AS [Early Credit Margin %], IIf([Late Credit Sales]=0,0,([Late Credit Sales]-[Late Credit Cost])/[Late Credit Sales]) AS [Late Credit Margin %]
FROM tblReportTemp1 RIGHT JOIN tblProductSubCategory ON tblReportTemp1.ProductSubCatId = tblProductSubCategory.SubCatID;
Thanks in advance!
View 4 Replies
View Related
Jul 29, 2007
Hi, I have a query that looks like:
SELECT * FROM ((TBL_Properties INNER JOIN TBL_Property_Landlords ON TBL_Properties.Property_ID=TBL_Property_Landlords. Property_ID) INNER JOIN TBL_Landlords ON TBL_Property_Landlords.Landlord_ID=TBL_Landlords.L andlord_ID) INNER JOIN TBL_Agents ON TBL_Properties.Agent_ID=TBL_Agents.Agent_ID;
so far. But I need to join the results from another query which will be calculating SUM(Pay). Is it possible to INNER JOIN to a: (SELECT ....) nested query?
Sorry if this is hard to understand.
Thanks.
View 3 Replies
View Related
Nov 14, 2013
I have done smoe baisc queries to sort, filter and report on data.How do I create a nested query where I don't repeat all the information across the query results line? Is there a way I can build a nested query to give all the SMCode codes associated with each appointment in one column separated by a coma?I have a table with appointments (APPTS):
RecID
ADate
ATime
Durantion
PVID
Desc
Case#
Then I have a table with Orders related to these appointments (ORDERS):
RecID
Case#
SMCode
SMDate
Here's my query:
SELECT DISTINCTROW Appts.RecID, Appts.ADate, Appts.ATime, Appts.Durantion, Appts.PVID, Appts.Desc, Orders.SMCode
FROM Appts INNER JOIN Orders ON Appts.[Case#] = Orders.[Case#];
My results look like this:
RecID ADate ATime Duration PVID Desc SMCode
RecID ADate ATime Duration PVID Desc SMCode
RecID ADate ATime Duration PVID Desc SMCode
I need my results to look like this:
RecID ADate ATime Duration PVID Desc SMCode,SMCode,SMCode...
View 3 Replies
View Related
Oct 11, 2006
Hi,
I used a before Update procedure to check if the date of a textbox is later than another text box. However, nothing seem to happen when I enter the wrong date.
Here is my code:
Private Sub txtDateOfCompletionForHandover_BeforeUpdate(Cancel As Integer)
Dim anticipatedCompletionDate As Date
Dim dateCompletionHandover As Date
'assign vars values
anticipatedCompletionDate = txtAnticipatedCompletionDate.Value
dateCompletionHandover = txtDateOfCompletionForHandover.Value
'If date1 refers to a later point in time than date2, the DateDiff function returns a negative number
If DateDiff("d", anticipatedCompletionDate, dateCompletionHandover) < 0 Then
MsgBox "You are trying to Handover house before the house is finished!" & vbCrLf & vbLf & _
"Please enter a later date than Anicipated Completion Date above.", , "House Handover"
'empty textbox and keep focus
txtDateOfCompletionForHandover.Text = ""
txtDateOfCompletionForHandover.SetFocus
End If
End Sub
Any help will be very much appreciated.
B
View 10 Replies
View Related
Apr 15, 2015
I have different product groups and they can have one of 3 rental rates(5,4,3%) I have written a nested query but I keep getting parenthesis errors, the last one I got told me I must enclose iif functions in parentheses. I know its probably a small thing but I just cant see it. By the way the server is German so ; need to be used instead of ,.
IIf([view_India]![Sub_Group] In (1730;1810;2870);5;iif Left([view_India]![Sub_Group];2) In (01;02;21;24;29)
;5;iif([view_India]![Sub_Group] In (3310;3330;3340;3360);3;4))))
View 2 Replies
View Related
Jun 1, 2014
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen
FROM voorwerp
(inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp)
inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau
where rubriek in (select rubrieknummer
from rubriek
where rubriek = variable name
)
group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
View 5 Replies
View Related
Dec 10, 2005
Hi all,
I'm trying to use an update query, but somehow, it doesn't store anything in my database. This is my code:
sql = "UPDATE CONSTRUCTIEF SET ConstructiefScore = " & strGebouwConsScore & " AND ConstructiefCommentaar = '" & strGebouwConsOmschrijving & "' WHERE ConstructiefID = " & strGebouwCons
DoCmd.RunSQL sql
When the execution reaches this peace of code, I actually get the right warning message because he's going to update a table, but when i'm looking in the table afterwards, nothing has been updated.
Anybody got any clue?
Thnx in advance,
Freak
View 2 Replies
View Related
Jul 6, 2005
The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.
For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.
I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.
HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.
I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")
I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.
PLEASE HELP! :(
View 4 Replies
View Related
Apr 27, 2014
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
View 6 Replies
View Related
Oct 30, 2013
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.
My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
View 7 Replies
View Related