possible to have an update query to only update 20 rows with a new data in the fields? And, how do I pass the information that I want to update only data from a certain date.If I have the word Cat and want to update it to Dog, but only with the dates 12-01-2014, where do I need to put the date part (also possible to do this for dates that are greater than 12-01-2014).
View RepliesI have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
I have a table in this manner:
Customer | Debit Amount | Credit Amount | Date of Transaction
My goal is to have the customer's name | (Sum of Debit amount MINUS Sum of Credit Amount) and it will only show if it is past a certain due date.
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.
I am facing problem regarding updating of certain field of table. For example i would like to take data from a column of Table1 and would like insert that data into particular column in Table2.
Suppose Table One has data:
Table 1
ID Name amount
1 a 0
34 b 0
3 ab 0
Table2
ID Name amount
34 a 400
1 b 900
3 ab 4500
There are about 2000 records in each table.I would like to update column "Amount" of Table1 by taking data from Table2 "Amount" Field. how can i update the amount column by keeping the ID field?
I need to make a coupel of statements that display the total (sum) of negative amounts and positive amounts from a single list of results which comtains both negative and positive values. The two answers need to be displayed in the footer of a report.
View 12 Replies View RelatedHi to all!
I face the following problem: I cannot add amounts per line!
I have one table consisting of many different columns (fields), some of them having amounts. I want to update this table by doing the following:
let's say I have the following records:
1/6/2006 A 12
1/5/2006 B 20
15/6/2006 C 21
22/6/2006 D 17
25/6/2006 E 30.
I want to use a filter (e.g. the date being between 1/6/2006-30/6/2006), sort the records descending according to the amounts and then add in another field of the table A, B or C, according to whether the total amount including the specific record is above 70% of the total or above 90% of the total. In the specific example I want the following result:
25/6/2006 E 30 A
15/6/2006 C 21 A
22/6/2006 D 17 B
1/6/2006 A 12 C
Their total amount is 30+21+17+12=80 and the percentage of each record is: E:0.375 , C:0.2625 , D:0.2125 and A:0.15. Their adding percentage is
E:0.375 , E+C:0.6375 , E+C+D:0.85 and E+C+D+A:1.
How can I do that? I calculate the percentage of each record in a query, but how can I calculate their adding percentage, so that I can use an if-clause in an appending query?
Please help!!
Thanks in advance for your time
Panagiotis
Hello Everyone,
I'm using the following on a form in my database:
=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])),
((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100
In this case, I want to calculate a total for an order. If the
LineTaxExempt field is checked (meaning tax exempt), the total is
figured by the ([Qty] field X [Price] field)+the [ShippingHandling]
field.
If the LineTaxExempt is not checked, then the above total, including
shipping, is multiplied by 6% sales tax or 1.06. I then want this
value rounded up to 2 decimal places. This control is a running total
of the order.
This works most of the time, but I have run into a few rounding errors.
For example:
With exempt checked
1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me $34.35
instead of $34.34
With tax exempt checked (and multiple lines)
2(Qty)X141.02(Price) + 0.00(ShippingHandling)
10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a
grand total of $323.25 instead of $323.24
It's frustrating because it seems like it works part of the time and
rounds wrong the other part of the time. By "rounds wrong", I mean I
set it wrong to round.
Is there a better formula to use in a form or query to round up to 2 decimal places other than the
=-Int(-Sum([Fieldname]) * 100) / 100 method that will give me the right results?
Any help/examples would be greatly appreciated on how to
modify this to give me the correct result each time.
Here are my field types:
QTY
-------
Field: Double
Format: Standard
Decimal Places: 2
Price
----------
Format: Currency
Decimal Places: 4 (some unit prices are 4 digits--Example $1.2525)
ShippingHandling
-------------------------------
Format: Currency
Decimal Places: 2
Thanks,
-Chad
Heelo all:
I have a form based on a query. This form has a combobox with 4 names in it. When a person is selected form the combo box, other fields automatically populate IE: room number, address, etc. Another field is a currency box which also populates automatically.
This is what I wish to happen: When I select a person from the combo box, can it show the TOTAL amount received for THAT PERSON in a separate text box?
Any help is most welcome.
Regrds,
Dee
im working on an update query (written in VB coding) for one of my tables, for now im just setting the value to 1 (as currently all the data is set to 0, so for now it works), however id like to improve upon the query and get it to add 1 every time instead of setting it to 1 (i can work with it the way it is, but changing it would give me more info)
currently its at
DoCmd.RunSQL "UPDATE tblBatches SET tblBatches.batchCarryover = 1;"
I have 2 tables that hold similar data. In one table tblMultiSchedule I have a few blank columns. I want to update each column(ActualCost*) with the correct data which is stored in tblOrdersItems. the corresponding fields in each tbl are
tblMultiSchedule.OrderItemID1
tblOrdersItems.OrdersItemsID
I've been trying all day to have an update query to input the actual cost data into the tblMultiSchedule.
I know the rules regarding duplicate data but this is the only way i can do this.
one of my tables.
tbl_type1
-type1_id
-PONumber
tbl_type2
-type2_id
-PONumber
tbl_bill
-billID
-PONumber
-bill_type
Now I have huge data from tbl_bill which contains mix PONumber depends on type. If bill_type is type1 then it will get data from tbl_type1 table and vise versa. the problem is tbl_type1 and tbl_type2 is changing, when someone update this table then the data in tbl_bill will not match to 2 tables.
Now, i need an update query where I can update all records of tbl_bill to match with tbl_type1 and tbl_type2 PONumber.
I have created the tables I need for my Access Web App.I would like to create queries.I cannot update fields that show in the query (fields have a grey frame and cannot be edited) . I have even created a completely new app with only 1 table (no relationships whatsoever in case this may influence). It is still not possible to edit any field in a query (that would update the underlying table). Are there any general settings in Access I need to enable? Is there a setting in Sharepoint I need to enable?
View 1 Replies View RelatedCan't figure the SQL to UPDATE a table (tblAccomResv) with columns: AccomID and DtBooked. It will be an empty table.
I need to populate it with one record for each record in tblAccomodations (ID) by each record in tblEventDts (EventDts).
Like:
Room1 Dt 1
Room1 Dt 2
Room2 Dt 1
Room2 Dt 2
etc.
I can do this in like 5 seconds in SQL Server but I can't get the query to even ALLOW me to update records.
Table1 - Columns
Query1 - ColumnDesc
Code:
SELECT DISTINCT C.Column
FROM Columns C INNER JOIN (
SELECT Cols.Column, Count(Cols.DataType)
FROM (SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns) Cols
WHERE Cols.DataType = 'char' OR Cols.DataType = 'varchar'
GROUP BY Cols.Column
HAVING Count(Cols.DataType) > 1
) C2 ON C.Column = C2.Column;
Simple, straight forward query, that grabs all the rows from table Columns where there are more than one DataType per Column [name], and either one of those datatype strings are 'char' or 'varchar'.Now I want to UPDATE table Columns to set all of the columns whose "column" value is in the above query, and set all those DataType values to 'VarChar'. Thus I run that UPDATE query, and the above query should come back empty afterwords in SQL Server I would simply write:
Code:
UPDATE Columns
SET DataType = 'varchar'
WHERE EXISTS (
SELECT 1 FROM ColumnDesc INNER JOIN Columns ON ColumnDesc.COlumn = Columns.Column
)
And this would already have been done.But Access doesn't like that syntax. using the designer it created sql like this:
Code:
UPDATE Columns INNER JOIN ColumnDesc ON Columns.Column = ColumnDesc.Column
SET Columns.DataType = 'varchar'
But that wasn't an "Updateable" query.I have to run this on several patterns and right now I'm completely screwed if I can't get this to work.
My end goal is to have:
SELECT DISTINCT Columns.Column FROM Columns
return the same # of rows as
SELECT DISTINCT Columns.Column, Columns.DataType FROM Columns
for each duplication I will have to do different algorithm, but I can't even get one update query to work so I'm currently frustrated (and ready to reaffirm my belief that Access should have been discontinued 5 versions ago).
Hi, it's me again, the guy who works with the Amusement ride industry. I'm trying to set up a way to ogranize all our saftey bulletins that we get.
I've already looked into indexing but the major problem is that the user will need to be able to search on multiple criteria (for example the Tilt A Whirl was made by three different manufacturers). Also I can't download that nifty google tool bar because we're running win 2000 without service pac 3.
Access seemed like a great alernative, but I have about 300 or so files I need to link. Is there a faster way of linking these files than going through and doing them one by one?
I have a pre-made index the guy before me made in exel. Most all the files are in PDF format if it helps at all.
Thanks.
hey all, thanks to anyone that can help
i have 5 fields facid custid empid amount invdate.
i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year)
the date range totals, last yeardate range
current ytd, last ytd
i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query.
i've used dsum() but not quite getting the results i should.
i think one of the problems is the user has the option of filtering the data based on the 3 fields.
anyways if you can help i'd greatly appreciate it..
Is there any way we can convert the amount in numeric form to text format Eg: $ 1000 as Thousand Dollars.
I need this for generating invoices.
Thanks in advance for the help...
Rajesh
Is it possible to create an update query where the Update To field is set to the results of a previous query? I've been playing around with the Design View but I haven't figured out how to do this yet.
View 3 Replies View RelatedI currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...
Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.
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] ....
I have a form that displays an updatable subform. The main form has search fields and a search button that when clicked will reset the record source of the subform and then do a refresh. The problem is that after the search the subform stops becomes locked. Here is the code:-
Me!sbfDepartmentSub.Form.RecordSource = MySQL
Me.Refresh
I have tried Me.Requery also.
I am using an audit trail on my database that works perfectly. My question is, can I run an update query and still log any updates through the audit process?
I suppose the update query is not a necessity, but it would be nice to retain it if possible.
I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.
My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.
I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.
Is it possible to have query update only on demand and not automatically? I have a big database and some are best run overnight.
View 12 Replies View Related