Queries :: DSum - Update A Field In Specific Table With Info Form Another Table
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View Replies
ADVERTISEMENT
Apr 27, 2007
This may be a bit detailed, so stick with me. Is there a way to have a form which you can pull tabel information into, but look at a specific line? I am trying to create a database that will pull up the first line of a specific tabel's information, then when a command button is pressed, it will pull up a seperate form with the information from line 2 of that SAME tabel. I don't want this to be done by clicking the arrows at the bottom of the form. I actually want this to populate seperately.
Lets say I have a Loc_ID box that is pulling the Loc_ID from a tabel.
The tabel I am trying to pull from has an autonumber format and is sorted in that manor from a-z. Is there a certain type of logic that I should use however? As of now, each time I have a form come up, it starts at the first record, which is not what I want it to do. I want it to come up with the first record, then click a few buttons, then the next time a new form comes up with the same info on it, I want it to show the second record's information.
Any information anyone might have will be MOST helpfull! :D
View 3 Replies
View Related
Dec 4, 2013
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
View 9 Replies
View Related
Apr 24, 2013
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
View 3 Replies
View Related
Nov 7, 2013
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
View 1 Replies
View Related
Jan 25, 2012
I'm VERY new to MS Access 2010 - in fact, I used some online youtube videos to get me started with the database I'm currently working on.
Here's what I have so far: 2 tables, one for customer information (name, phone, addy, etc) and one for invoices (inv #, customer ID (which should be the same as my primary key from the customer info table), phone, services rendered, etc etc)
I'm currently working on a form for Invoices, where I can simply put in the customer's name, date, cost, etc without having to look up the ID# and phone number for every time I use it.
View 1 Replies
View Related
Oct 30, 2012
I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.
View 5 Replies
View Related
May 12, 2014
I have a database which tracks product batches test results and then any reported issues.
I have one table tblOne with [Batch Number] and [Release Time/Date].
The 2nd Table tblTwo [Problem/Issue] at [Time/Date].
What i need to do is get the last batch number from table one which would have been released.
Example:
Batch: 275 Released: 21/09/2012 06:15
Batch: 453 Released: 30/09/2012 07:20
Problem: Wrong Batch Time/Date: 21/09/2012 08:20 Batch:????
How would i update table with correct batch in tblTwo
View 3 Replies
View Related
Oct 27, 2014
Okay so basically below is the table I'm linking to a list box, the field i'm focusing on is the 'Disallowed' field:
Now this is the form i'm linking it too:
As you can see the list box on the right is currently just showing the whole column under the Disallowed list, but I want it to show the specific country, obviously I'm using a single form template but the information changes depending on what country i'm on (I presume I need some kind of Query that uses whatever is shown in the Country box)
I was trying to use SELECT * from tblCountries where [Country]='"&forms!frmCountryDetail!Country&"'" but this is giving me an error from the [Country] tag so this isn't working:
View 3 Replies
View Related
Jun 25, 2015
I am programming an Acess Database, the primary data come from an excel sheet. Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.
I will explain what I mean with a hypothetical situation.
So, let's say I have two tables: WindowT1, CustomerT2.
WindowT1 has fields: WinID (the pk), WindowName.
CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.
So, in table "CustomerT2', WinID is the foreign key and I need that field filled in to make one-to-many relationship between these two tables to work. CustomerName and WindowName will be imported and prefilled already from an excel file with append query.
My question is can I make a update query (after the initial append query that brings in the data into Access) such that the criteria is matching WindowName (from CustomerT2) and the update would be the corresponding WinID (the pk number from WindowT1) that would be filled in the field WinID (in CustomerT2)? I know for this to work tables must have relationship and I did that.
Is there a better way to accomplish this task? i.e. through a form (remember info will be prefilled, so can't use combo box to fill in another form field) or maybe can I do this using the first append query (that brings in data)?
I am not good at Macro or VBA. The only thing I have done in code builder is After Update event in forms while using combo boxes. So, if there is no easier solution, I will try to deal with codes.
View 11 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Jan 16, 2015
I am developing a database for incoming inspection. I have set up two tables, one with spec callouts per product and the other table contains fields where I want the specs from the tblSpec table to be Append into the tblMeasurement table. Now, within the tblMeasurement table (where I am trying to append specs from the tblSpecs table into), I also have additional fields for actual measurements from received goods.
My plan is as I go through and select the Vendor, it populates the associated products to that vendor (no problem there). When I want to select the product, I would like the specs from the tblSpecs to dumped into the tblmeasurement table that correlates to that product on the form.
How can I have this automatically append after selecting the product? I am not sure if I have my relationships set up correctly or not, but I cannot see to append when I am trying to execute this function while in Query view.
I have the db attached in a zipped format.
View 9 Replies
View Related
Jun 29, 2015
I have a table Billing_Temp that I need one field updated if I find a match in another table Random_Temp. I runt the query and it prompts for "Enter parameter value: Random_Temp.peopleID... what could be going on? Both tables have a field called peopleID and always Billing_temp has many more records than Random_temp:
UPDATE Billing_Temp SET Billing_Temp.audited = -1
WHERE (([Billing_Temp].[peopleID]=[Random_Temp].[peopleID]));
View 1 Replies
View Related
May 7, 2014
I am putting the finishing touches on a DB and have come up with a a problem which I cannot solve. I have code on my data entry form that concatenated the days and times of up to five days. That is : [Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" and so forth for five days. It has a complicated (for me) nested IIF statement and it works fine on the form. But I have now transferred the data from an old DOS DB and I do not want to go through almost 5K records just to update three fields.When I tried to do it in an update query, it said that the string was too long for the update. My update is:
IIf([Day5] Is Not Null,[Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" & " " & [Day2] & " " & "@" & " " & Format([Time2],"h:nn AM/PM") & " " & ";" & " " & [Day3] & " " & "@" & " " & Format([Time3],"h:nn AM/PM") & " " & ";" & " " & [Day4] & " " & "@" & " " & Format([Time4],"h:nn AM/PM") & " " & ";" & " " & [Day5] & " " & "@" & " " & Format([Time5],"h:nn AM/PM"),IIf([Day5] Is Null And [Day4] Is Not Null,[Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") &
[code]...
how to update the table if a field is blank without clicking through all 5K records? I have tried simple concatenation, but I usually get a lot of extra @ in the field.
View 2 Replies
View Related
Feb 26, 2015
I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.
View 2 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
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
Aug 24, 2013
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
Code:
Me.txtTotalEarnings = DSum("Price", "KatiesPeriodTakings", "AppDate" >= CDate([Forms]! _&
[frmKatiesTakings]![txtStartDate]) And "AppDate" _&
<= CDate([Forms]![frmKatiesTakings]![txtEndDate]))
But I keep getting a mismatch error
The Query is called KatiesPeriodTakings
The field I want totalled is called Price
The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
View 5 Replies
View Related
Nov 21, 2014
I have a table (tblInstallations) which has records of date installed (for machines).
It is calculating that "next due maintenance" is one year after installation.
Sometimes we need to visit the site to maintain or service the machine before/after the specified date in "next due maintenance" and we need this to be calculated to reflect a year after date of visit.
I have a table called "tblMaintenance" which takes information about the machine an engineer is visiting from where originally the information are kept and that is in "tblInstallations".
How can I link the new date of the visit to update the record for "next due maintenance"...
View 4 Replies
View Related
Sep 11, 2007
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id
FROM ft_payees AS p, ft_txn_summary AS t
WHERE p.payee_id=t.payee_id And p.market=t.market
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc
SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
View 3 Replies
View Related
Aug 17, 2006
Group,
I thank you in advance for considering this inquiry.
From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.
This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.
What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.
I'm sure this is absurdly simple.
View 2 Replies
View Related
Feb 19, 2006
hi there
I am having some difficulty with using Access (aren't we all?) ;-(
I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.
What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.
It must be simple but I just can't get it to work and it's stopping me from sleeping :mad: I have found myself doing Access in my dreams which is not good!!
Any tips much appreciated - Thanks
Mike
PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)
View 3 Replies
View Related
Apr 6, 2006
i have a form, which is based on query from multiple tables.. in this form i want to add a unbound textbox in the detail section.. whenever a user leaves this records, the system should insert a record in another table with values from this record including this field... how to do this?
the record is from a query where as the field is unbound, still we need to insert the values from record and this field at the same time into another table..
example: i have item master and item production table.... from joining these table i get a query which will give me item A and prod qty .. when i use this query as form datasource, the detail section has these two fields.. now i want to have an unbound field budget.. so i have the followig in detail sectin
item A production qty budget qty
when users enter values in budgetqty field and moves to next recod,, the system should insert into another table xyz values (itemA, production qty, budget Qty)
how to do this..which event will be good.
urgent reply is expected.
View 2 Replies
View Related
Dec 2, 2014
I am working on a shared database. There are two tables in the database.Table1 have all the employee personal information like employee ID, name, nationality, date of birth etc whereas Table2 have the salary information like Basic, HRA etc. The two tables are joined by Employee_ID field.
I have created two forms, Form1 is only based on Table1 whereas Form2 have fields from both Table1 & Table2. The issue is that if I use to enter the data using Form1, the employee_ID is not automatically updated in table2. On the other side If i use to enter a data by using Form2 then everything is working fine. Is there a way to update a field value which is not in form?
View 1 Replies
View Related
May 27, 2014
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
View 6 Replies
View Related
Aug 2, 2013
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
View 7 Replies
View Related
