Update The Second Table Based In First Table???
Jun 27, 2007
I have two tables.
First Second
---- --------
RollNo Number(PK) RollNo Number
Appno Number Appno_1 Number
Now I want to update "Second" table's "Appno_1" with the "Appno" of the "First" table and the "RollNO" of "First" table should match with the "Second" table "RollNo" field.
How i will i do it :
Plz Help-----------------------
View Replies
ADVERTISEMENT
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
Feb 24, 2008
The attached Access XP file demonstrates my problem. I've included a form to make testing easier.
Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.
A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.
I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.
In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.
I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.
View 5 Replies
View Related
Sep 20, 2014
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
View 3 Replies
View Related
Apr 24, 2015
I'm trying to use VBA to update a new column in a table with info I already have in another table.The table I want to update is an inventory details table, it has around 25,000 records. I added a column called "UnitCost", of course the column is empty for all 25,000 records so I would like to fill it easily using DoCmd.RunSQL "UPDATE" feature.
I use that through-out the program however I'm unable to connect the dots for this one.What it needs to do is update "UnitCost" in "InventoryDetails" from "Products" where "InventoryDetails.ProductNumber" = "Products.ProductNumber"
The "Products" table has all the different unit cost, it just need to be placed in the "InventoryDetails" table for every record. Of course product1 needs products1 unit cost and product2 needs products2 unit cost, etc.
View 1 Replies
View Related
Mar 9, 2015
I would like to achieve the following task described below:
Just for an example i have two tables = Table1 and Table2
Table1 contains following fields: ID , CusName, Price , Date_
Table2 Contains following fields: ID, CusName, Price, Date_
I would like to update Price and Date Field of Table 1 where Table 1 ID matching with Table 2 and Table 2 Date is maximum(most recent date).
View 4 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
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
Apr 27, 2006
Hi all,
Simple question...I think. Is it possible to update values in a table, based on the results of a query?
For example, I have tblPRR and qryProcessed.
When I run qryProcessed, I would like the field "Status" in 'tblPRR' to update from 'No' to 'Yes'.
Could anyone advise if this is possible?
Cheers
Rob
View 2 Replies
View Related
Jul 25, 2006
I have the following situation.
Table 1:
------------------------
Primary Key
Name
Secondary key
Table 2:
------------------------
Primary Key
Age
Secondary Key
*****************************
This is just an example. The first table is totally populated. The second table does not have the secondary key assigned, but otherwise is populated. In additon, some of the records in Table 2 do not have a matching entry in table 2.
I would like to be able to update table2, populating the Secondary Key field in order to allow me to delete the Primary Key fields. The Primary key was assigned by somone else who created the db, and makes no logical sense, i'm trying to replace it with a key that can be used more easily. Any help would be greatly appreciated.
Kevin S. Jones
View 1 Replies
View Related
Aug 28, 2007
Dear All,
This is my first post in the forum and would like to apologise for any shortcomings.
I want to update a field in my table with the result from a query.
Eg. Table A with two fields, a1 (recorda1) & a2 (recorda2)
Result from Query (recordq) has to be replaced in Table A, field a2.
after the code is run, (recorda2) should be replaced by (recordq)
Can anyone help me how to do this with the help of an update query.
Regards to all
Basheer
View 4 Replies
View Related
Nov 24, 2005
Hi all,
This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.
I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).
That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.
I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.
Can someone please post the solution asap. Thanks so much for your help.
Regards,
Mike J.
View 4 Replies
View Related
Mar 15, 2006
I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.
The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].
tblReference example of field values:
Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset
So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.
Any help is greatly appreciated.
Thanks,
View 7 Replies
View Related
Aug 7, 2015
I want to update a table called tblFinalOrder, that looks like this:
In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:
My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.
Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.
Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);
I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.
View 3 Replies
View Related
Nov 14, 2004
I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!
Problem:
Where I work we have a series of machines… named “ C1, H7, H8 etc”
Each line has 3 counters on it,
Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.
Sounds straight forward enough….but the process gets a little more complicated.
Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters
Grouped lines are given a group code – P8 in this case.
Example…
C1 not joined
H7 and H8 joined.
C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons
H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons
H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons
P8
Output – 1600 Cartons
The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.
As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.
So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.
I now want to implement the following calculation to my DB,
(see Attached Spreadsheet)
the calculations a re stored in cells b12 & b13 in the spreadsheet.
so that the output figure is separated
in proportion to impressions made.
I then need the figure per line to be fed back to the “tblProduction” output field..
in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...
Can this be done automatically, if so please help me…
Andy
View 2 Replies
View Related
Nov 14, 2004
I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!
Problem:
Where I work we have a series of machines… named “ C1, H7, H8 etc”
Each line has 3 counters on it,
Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.
Sounds straight forward enough….but the process gets a little more complicated.
Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters
Grouped lines are given a group code – P8 in this case.
Example…
C1 not joined
H7 and H8 joined.
C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons
H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons
H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons
P8
Output – 1600 Cartons
The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.
As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.
So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.
I now want to implement the following calculation to my DB,
(see Attached Spreadsheet)
the calculations a re stored in cells b12 & b13 in the spreadsheet.
so that the output figure is separated
in proportion to impressions made.
I then need the figure per line to be fed back to the “tblProduction” output field..
in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...
Can this be done automatically, if so please help me…
Andy
View 5 Replies
View Related
Nov 16, 2013
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....
View 2 Replies
View Related
Aug 8, 2013
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
View 3 Replies
View Related
Aug 6, 2015
I created two tables, let's refer to them as Cars (VW, BMW and Audi) and Colours (White, Black and Grey).
Is it possible to create another table based on these tables - i.e. in the new table the rows will be the Cars and the columns the Colours as such:
White
Black
Grey
VW
BMW
Audi
And should I enter another Car or Colour in one of the first mentioned tables, then I would like this "new" table to update automatically. For example, if I have a new Car (say, Merc), then I would like the "new" table to update to the following:
White
Black
Grey
VW
BMW
Audi
Merc
View 2 Replies
View Related
Mar 12, 2013
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
View 6 Replies
View Related
Dec 30, 2014
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code:
Sub Update_Dates()
Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String
[Code] ....
View 6 Replies
View Related
Jan 14, 2013
I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
View 1 Replies
View Related
Jun 7, 2013
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]
[Code] ....
View 5 Replies
View Related
Apr 15, 2014
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
View 3 Replies
View Related
Nov 24, 2014
I have database with an userform called AssignWP, combobox called WPDevBy, listbox called List352 (Multi select) and table called Justified.I am trying to update one field WPDevelopedBy of the table as combobox value based on list box multi selected records.
View 2 Replies
View Related
May 6, 2013
I would like to have a table update query populate cells in a field, based on an IIf statement (below), but states that the IIf statement arguments still need to be enclosed in parentheses. I don't understand what the problem is, I have two sets of opening / closing parentheses.
Code:
IIf IsNull (=Mid( [CONTRACTOR_TASK]![TASK_NAME] ,6,6))
View 3 Replies
View Related
