Can I Use Sum() Function Along With Update Query?
Sep 19, 2007
I'm trying to use sum() along with UPDATE as shown below
UPDATE ALLDATA INNER JOIN [Total Data] ON ALLDATA.PanNumber=[Total Data].PanNumber SET ALLDATA.PLAForMonthPrevYear = sum([Total Data].PLAForMonth), ALLDATA.CENVATForMonthPrevYear = sum([Total Data].CENVATForMonth), ALLDATA.PLAUptoMonthPrevYear = sum([Total Data].PLAUptoMonth), ALLDATA.CENVATUptoMonthPrevYear = sum([Total Data].CENVATUptoMonth)
WHERE [Total Data].Month=Forms![FORM REPORT]!Combo3;
IT IS THROWING ERROR
PLS HELP ME
View Replies
ADVERTISEMENT
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Sep 27, 2007
I am having a bit of a problem with my update query. i have a field that shows a forename. i am importing data from an excel file. The forename populates with forename and middle names and they are seperated by spaces as opposed to commas. I have used the following InStr function in my update query however it works fine when the records forename field has a middle name but it deletes all data in the records forename field if it contains only one name which many do. How can I adjust the function to ignore those records that do not hold more than one name in the forename field. As you can imagine some forename and middle name combinations hold many names.
For instance the filad may have Ivor as a name which I would want to keep But if the Field showed Ivor Bigun then Bigun needs deleting.
Left([Forename],InStr([Forename]," "))
View 2 Replies
View Related
Dec 31, 2007
Hi ALL,
I have 2 tables, trying to update one table filed by using max(onefiled) from other table.
my qyery is as follows:
my tables are tblSubFlowForecast and tblPickCalendar
I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.
UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");
I am getting error saying that "Operation must be an updateable query" :(
I have also tried in other way:
UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar
This is giving me syntax error (missing operator) :(
Can any body tell me why it is giving error?
Thanks in advance
View 3 Replies
View Related
Oct 31, 2004
I'm having problems with an assignment in an online course I am taking.
The assignment is to create one Update Query to find & remove typos in a field. Specifically, the typos are multiple f's & g's embedded in a field.
I created an Update Query that finds the typos & removes just the f's. I'm having trouble with the syntax to also remove the g's.
This is what I have so far that works great.
Update: Replace([Field2],"fff","")
Criteria: Like *fff*
or: Like *ggg*
The Query finds the records that contain both the "fff" & the "ggg" typos but I'm having trouble with Syntax for removing both.
Does anyone know what I have to change in the "Update:" line to include removing the g's?
I know I could easily create 2 Querys to remove the f's then the g's but the assignment requires only 1 Query to remove both.
Thanks,
Yaani-Mai
View 5 Replies
View Related
Nov 21, 2013
Would I use an update query or function to complete my task?
Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]
Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.
[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due
Query: Below is the query I just started to ignore the calculation if either Review or Completed exist.
Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.
Due in 24 = (0-1)
Due in 24-48 = (2-3)
Beyond 48 = (greater than or equal to 4)
Over Due= (-1)
View 14 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Jan 9, 2012
I have a column with a date in it, and the left four characters are the year. I want to add another column that is just the year. I've tried doing an update query and updating the year column to left(date,4) but it doesn't work. How should I go about updating my year column?
View 3 Replies
View Related
Jun 16, 2013
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code:
Call GeraAuditCalc
DoCmd.RefreshRecord
Function
Code:
Function GeraAuditCalc()
Dim fHrs As Double
Dim Ttl As Double
Dim Ttl1 As Double
Dim Ttl2 As Double
[Code] ....
View 4 Replies
View Related
Jun 10, 2015
I'm trying to update some field value depending two string field using a function with if and case, function below:
Code:
Private Function checkDATI(tipotransazione As String, tipovendita As String) As String
Dim r As String
r = ""
If tipotransazione = "VENDITA" Then
Select Case tipovendita
[code]...
And then when I call this function in a command button event as:
Code:
MsgBox (checkDATI(Me.CausaleMov, Me.txt_tipomov))
It's not update those field.
View 6 Replies
View Related
Apr 2, 2014
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
View 3 Replies
View Related
Feb 21, 2006
I have two forms (FormA, FormB) with combo boxes (cboA, cboB) that control the record being shown in the subforms (SubformA, SubformB). I have written code and attached it to the "AfterUpdate" function on the combo boxes and this works perfectly.
Here's my problem....
I have a button on FormA that, when clicked, opens FormB, assigns the value in cboA to cboB and closes FormA. Unfortunately, I can not figure out how to have SubformB automatically update based on the new value assigned by code to cboB.
I've tried .Requery, "After Update/Change/Dirty/etc." on cboB, "OnOpen" on FormB, and moved the code from cboB "AfterUpdate" to ButtonA "AfterClick" and nothing seems to work.
I need the user to use buttons to navigate through various forms displaying information for the same record. I also need to give the user the option to switch the record using the combo box. Does anyone know how to do this? Is it possible to use code to update the value in a combo box and then have the record in the subform automatically change?
View 1 Replies
View Related
Jun 28, 2005
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
View 9 Replies
View Related
Nov 29, 2006
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies
View Related
Feb 10, 2008
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
Thanks in advance
Wayne
View 5 Replies
View Related
Oct 3, 2007
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
View 1 Replies
View Related
Mar 5, 2007
Please help, the attached code works fine in Windows XP but twhen run on a Windows 2000 pc it fails on the create object, any ideas?
Function GetXmlFilename()
Dim objDialog
GetXmlFilename = ""
Set objDialog = CreateObject("UserAccounts.CommonDialog")
objDialog.Filter = "XML Files|*.xml|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C: emp"
If Not objDialog.ShowOpen Then
Exit Function
End If
GetXmlFilename = objDialog.Filename
End Function
Thanks in advance
View 2 Replies
View Related
Jan 17, 2006
I have written a simple function that I would like to use in a query. The function would return a value for each record where the parameters equals 2 different fields of the same record.
Private Function FctConvertInch(LineDim As String, LineShape As String)
'To Convert a String to a value and then convert that value from mm to inches
'Lines dimension = 00.0000x00.0000 if line is flat
'Lines dimension = 00.0000 if line is round
Dim StNum1 As String
Dim StNum2 As String
Dim DbNum1 As Double
Dim DbNum2 As Double
If LineShape = "round" Then
'"CDbl" convert a string to Double number
FctConvertInch = CDbl([LineDim]) / 25.4
ElseIf LineShape = "flat" Then
StNum1 = Left([LineDim], 7)
StNum2 = Right([LineDim], 7)
DbNum1 = CDbl(StNum1)
DbNum2 = CDbl(StNum2)
FctConvertInch = (DbNum1 / 25.4) & "x" & (DbNum2 / 25.4)
End If
End Function
I have put the function in a new module, and have try to use the function in a query with the fields LineDim and LineShape , I've also added the field FctConvertInch(LineDim,LineShape) but it doesn't work. Am I using the function the the correct way?
View 10 Replies
View Related
Jul 25, 2006
i want to write a query where i ask it to update a table where the text is
like "Ack. Stamp"
And the lenght is less than 15
how do i do this
View 1 Replies
View Related
Dec 14, 2006
Hi to all,
I have a table consisting of 12 columns of data type number. for each row i want to display a count wherever the value of a row is greater than 100, that is, how many times the value greater than 100 appear in the 12 columns.
PLease help???
Thanks
View 1 Replies
View Related
Jul 5, 2007
I have a field in a query that I want to select the value from a field, but if the value is null, then to select the value from the same named field in a different table.
Basically the whole point is to use the given description unless one doesnt exist, where the default one will be inserted instead.
Cheers,
View 5 Replies
View Related
Jul 12, 2007
I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able to calculate the value in 1 field instead of 4 fields.
I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function IfThen(BYStep As String)
Dim test2 As Integer
Dim Step070 As Integer
Dim Step075 As Integer
Dim Step080 As Integer
If
BYStep = "07.0" Then
test2 = Step070
ElseIf BYStep = "07.5" Then
test2 = Step075
ElseIf BYStep = "08.0" Then
test2 = Step080
Else: test2 = 0
End If
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In my query I have:
IfThen([BYStep]) AS Test2
It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I don't get an error message, but the field Test2, comes up empty for every record.
Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field in the query)?
View 14 Replies
View Related
Jan 24, 2006
How do you use the IIf function in a query if you want to specify more than one paremeter?
eg. IIf(MotherBirth="Australia", "Oceania", "Other") works but what happens if Oceania applies to Australia, Fiji and New Zealand?
I tried IIf(MotherBirth="Australia" or "new Zealand" or "Fiji", "Oceania", "Other") but this doesn't work...
What am i doing wrong?
View 1 Replies
View Related
Jan 30, 2014
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
View 2 Replies
View Related
May 18, 2006
Hey guys, can anybody help
here is what I have:
Job
JobId-----PK
Name
Position
PositionId----PK
Name
Activity
ActivityId-----PK
Name
JobTask as you can see the relationship between Job and Task is M-M
JobId-----PK
TaskId-----PK
PositionTask as you can see the relationship between Position and Task is M-M
PositionId-----PK
TaskId-----PK
ActivityTask as you can see the relationship between Activity and Task is M-M
ActivityId-----PK
TaskId-----PK
Task
TaskId-----PK
TaskName
TaskCompetency as you can see the relationship between Task and Competency is M-M
TaskId-----PK
CompetencyId-----PK
Competency
CompetencyId-----PK
CompetencyName
Keyword
Priority
I have a query like this:
The user will input the JobId, PositionId, ActivityId.
I want to return all the TaskName ,CompetencyId, CompetencyName
Where the following:
The highest priority within the same keyword
Basically group by keyword then pick the highest priority I have done this:
SELECT Max(Competency.Priority), Competency. Keyword
FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID
WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity]))
GROUP BY Competency.CompetencyKeyword;
It works fine, it returns the all the keyword, and its highest priority.
But I cant return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max
View 2 Replies
View Related
May 11, 2005
I was wondering if anyone can point me in the right direction please.
I have created a new module in my Access database (in the module object view) and I can see it in the module view. I am also able to call it in the expression builder under Functions.
Unfortuantaly if I create a query I am unable to use it. If I create it for the first time and open the query and use it, it works lovely. But if I exit my database and go back in, it does not work. I can still view it in the modules and it is still in the expression builder under functions.
My function is as follows;
Public Function GetLabourCost(WorkCodeID, PayRate, Hours) As Currency
Select Case WorkCodeID
Case "7800"
GetLabourCost = [PayRate] * 2 * [Hours]
Case "7500"
GetLabourCost = [PayRate] * 1.5 * [Hours]
Case "1000"
GetLabourCost = [PayRate] * [Hours]
Case "2000"
GetLabourCost = [PayRate] * [Hours]
Case "7000"
GetLabourCost = [PayRate] * [Hours]
Case "8000"
GetLabourCost = [PayRate] * [Hours]
Case "9000"
GetLabourCost = [PayRate] * [Hours]
Case "9500"
GetLabourCost = [PayRate] * [Hours]
Case "9999"
GetLabourCost = [PayRate] * 0 * [Hours]
Case "9550"
GetLabourCost = ([PayRate] * [Hours]) / 2
Case "9560"
GetLabourCost = [PayRate] * 0 * [Hours]
Case Else
GetLabourCost = 0
End Select
End Function
In my query I have called it as;
Expr1: GetLabourCost([WorkCodeID],[PayRate],[Hours])
I would be grateful of any advice.
Thanks
View 1 Replies
View Related
