Adding Values With An Update Query
Aug 13, 2007
Hi folks
I have what I reckon is a complex problem that I need to solve for my warehouse.
We hold replacement parts for the machines we sell. Some parts can be subsititued for others, so where we possible we use these parts to simplify our warehousing. Every day we get a file with demand for each part in and we look to see where we could move this demand to for those parts with a common alternative. When we find one we remove the demand from that part in the database and add it to the demand for the common part.
In operational terms we upload the demand file (.xls) to a table of the following structure
Date_added - Date
Part_number - Text
QTY - number
Thus the table holds daily demand by part. What I would like to do is use a update query to:
a) Identify the parts that have common parts to which they can be moved.
b) Add that demand to that of the common part today.
c) remove it from the original part (set to zero).
Idenitfying the parts and restricting to todays date is not a problem, however the additions and deletions are.
Any suggestions?
Thanks in advance!!
View Replies
ADVERTISEMENT
Sep 28, 2005
can someone help with this. I have 2 fields in a query
tbl1_nbil = 3
tbl2_nbil = 0
however when I add them using
Bill = [tbl1_nbil] + [tbl2_nbil], I only get a few of the added numbers in the calculated field. In some records it works, but in the majority it does not
It onl seems to add when both fields are greater than zero
tbl1_nbil : tbl2_nbil : Bill
1 : 1 : 2
1 : 0 : 0
2 : 1 : 3
0 : 2 : 0
4 : 0 : 0
View 1 Replies
View Related
Mar 3, 2008
I am adding fields with numeric values in design view of my query. The only issue I am having is that the query is not showing totals when one of the fields does not have a numeric value in it. How can I show the total numeric value regardless of the null value? Thank you:cool:
View 1 Replies
View Related
Feb 25, 2013
I am writing a sports database and have a query that displays a seasons fixture list in a form. One of the fields shows the points from each game played. (either 3 for a win, 1 for a draw etc). I can not get these points to be added up and displayed in a form along side the fixture list.
View 9 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
Oct 24, 2014
I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.
I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.
I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.
I then have a reconcile form which is on a datasheet form straight from the tab;e so it is editable which has a checkbox that then once ticked copies the total from the Order_Total in the table to the reconciled field in the same table and then I use a report to show who owes me still.
So I have made a Select query from the Orders table & Order_Details table and used a Group by Order number (Which is the link between the 2 tables) this shows correctly but now not editable because of the rules So I am trying to add an editable checkbox on the same form.
I tried to use a dlookup makes all of the boxes either ticked or not. I've been looking at Recordsets but I'm unsure if this is what I need or not really
So really I just need to see if I should be putting the Total from the Subform into the mainform and then entering it into the table (Like previously) using a calculated unbound field and then using the Afterupdate event to insert the data into the table. but for some reason it just wont work.
I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to 0.00 but then the table shows correctly which ever record the form is working on.
Or should I be using the new query based idea to create the reconcile form and if this is the best way I just don't now where to start on how to get a multiple table query that I can then add a reconciled checkbox which then copies the Order_Total from the table to the Paymet_Recieved field.
View 13 Replies
View Related
May 30, 2005
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
Can this be done?!
Thanks in advance
View 2 Replies
View Related
Oct 16, 2006
Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'.
This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie.
Is there any possible solution or is there a more simple way to solve the problem.
Many Thanks in advance Dan Parker
View 1 Replies
View Related
Jul 2, 2015
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
View 3 Replies
View Related
Jan 2, 2014
What query would I use to build a date in m/d/yyyy format using month, day and year values from another field?
View 2 Replies
View Related
Sep 23, 2014
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
View 1 Replies
View Related
Oct 25, 2014
Access doesn't seem to recognize the values in a table I am updating. I am using an update query to revise prices in a column Titled "Price". I am comparing the prices in the Price column to the prices in another column with a simple IIF statement (i.e., IIF([Price]=[Column2Price],"Yes","No"). However, oddly, the formula produces a "No" result, even when the prices are plainly the same values, and all formatting for both columns is the same. Whenever I manually type over the price with the same value, the formula works and Access seems to work as intended. For example, I simply type "12.95" over the same value that is displaying in the column that I updated that is already showing "12.95". Then, after I do that, the formula works and produces a "Yes" value.
View 3 Replies
View Related
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 Replies
View Related
Dec 29, 2006
I have a report that adds time for as many as six people. This isssue is when the time goes over 24 hours in total it drops the hours. The field(s) is formated in "short time" format.
Can anyone help resolve this???:confused:
View 6 Replies
View Related
Oct 15, 2004
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers.
So, I want to show in the textbox "total" (in the footer of the sub form) the result of this function
sum([kath_dik])-sum([pistosi]) for each company. [kath_dik] and [pistosi] are text boxes in the sub-form. (scroll the sub form to the right end)
I tried the formula above end get an error message. Thanx in advance
View 2 Replies
View Related
Mar 3, 2005
I've been having trouble trying to figure out how i can add the values within one column. I have a field named Cost, under that field i have serveral rows of data. I would like access to add all the numbers under that "cost" field. Im not sure how this is done. Can anyone please help ???
thanks
EM
View 6 Replies
View Related
Sep 22, 2006
i need to add values of about 20 items and store them in a feild of their own. any ideas of how. Tthe items each have their own value but i need to be able to add all of them up and divide the by the total p[ossible points. i know their is a way to do so but i cant get it to work on my own it keeps giving me an aggregate error.
View 1 Replies
View Related
Aug 13, 2015
I am trying to set up a estimation form. What I want to accomplish is to have multiple checkboxes that a user would select the boxes relevant to his project. From these selections there would be a sum created. Each box could have different values. I tried using the default value but when I unchecked and rechecked it went back to using -1.
I am thinking I will need to assign values through a query but I want to show the sum on my form.
View 6 Replies
View Related
Feb 8, 2007
Am I missing something simple here? I have several text boxes, which I want to add the values. I have tried these formulae, but dont work:
=Sum([Postage],[ProgTotal])
=Sum([Postage] + [ProgTotal])
=DSum([Postage],[ProgTotal])
Any help would be greatly appreciated!
View 7 Replies
View Related
Oct 16, 2007
Hi all,
can i use a listbox control NOT IN A PROGRAMATICALY way, in a table to allow user choose one of some pre_defined values (for example 1,2,4). This field will be used as a parameter in an communication with HW, that's why its crucial not to let users choose whatever value that they want.
Thank you
View 3 Replies
View Related
Jun 20, 2007
Hi,
Im trying to create a database for a stock control system. I have a slight problem, the stock is in two places. So what i have to do is put in the values of stock in each place and then display the value of those two added up in the table. What is the best way to be able to do this?
View 12 Replies
View Related
Sep 21, 2007
Hi,
This may be a dumb question, but I've got kind of a question about adding values.
I have a subform that contains a department name, and it's waste totals (in Kilograms) for the day, for a particular company. The company information is the main form.
Whenever I start a new department, the totals, and category names start blank (different departments could produce different waste).
I'm trying create a query that gives me the total value for each Category of waste. However, I keep getting all the seperate values returned?? As apposed to a total. For example.
Company A has two departments:
Office and Production.
For Office they had 20 Kg of plastic bottles and Production had 20 Kg of plastic bottles.
I want to be able to total the weight of plastic bottles...it's just not working../
?
Any advice?
View 1 Replies
View Related
Aug 25, 2004
ok this is weird post but im wonderin how to go about it easily cause i can think of a few ways, dunno if they will work of not, so im asking for ur opinion. I need to find out possible cominations for a salary cap football league based on dollar amounts for the players and points based on last years results, yet it cant go past $100.00...so one possible choice is (QB Tom Brady $22.56 183.50 points). its 1 QB, 2 RB, 3 WR, 1 TE 1 Kicker, 1 DEF team and i have to do all 9 for it to count...so would i have to set up 6 columns, one for each position and have a way of picking 1 of A 2 of B 3 of C 1 of D 1 of E 1 of F for 1 qb, 2 Rb, etc? and have it so sum of values is < $100 and it also must add the points so i can see the best possible combination/most points i can get for $100.00...i know its last years points and this year will be diff but I am just comparing...all i need is an outline i dont need u guys to do it for me i have some experience with access...thanks for anyone who can help
View 2 Replies
View Related
Jan 30, 2005
Query 1:
Field #1: User Name
Field #2: CountOfUserName
Query 2:
Field #1: User Name
Field #2: CountOfUserName
Query 3:
Field #1: User Name
Field #2: CountOfUserName
Query 4:
Field #1: User Name
Field #2: CountOfUserName
I want to create a Query that will add
[Query 1].[CountOfUserName]+[Query 2].[CountOfUserName]+[Query 3].[CountOfUserName]+[Query 4].[CountOfUserName]=
[My Query].[TotalCountOfUserName]Group by UserName
It seems difficult.
View 6 Replies
View Related
Jun 19, 2006
Hi there
I have an ASP form that saves numerical data to an access database via an insert statement in the asp page.
The main problem i have is that i have some code that adds all the stored values together and provides a total on the screen for the user to print off.
I would like to save this 'total' value to a field in the table but cannot seem to get this to work.
So i thought of running a query in access that would add the sum total of a number of fields and store it to the total field.
Is this possible and how would i go about it?
Cheers
View 3 Replies
View Related
May 23, 2013
anyway you can add the values onto a graph
eg i have created a bar graph and on each bar would like the value shown on the top of it
View 9 Replies
View Related
