Trying To Update Field Based On Other Table
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 Replies
ADVERTISEMENT
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
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
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
Mar 3, 2008
Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....
I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once.
Any help would be appreciated.
Thanks in advance,
Freud52
View 7 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
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
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
Jul 13, 2013
I have a table that has been converted into a form for an input. Now suppose I have two columns. Say I enter value in first column. Now the second column is a lookup to a data from another table.
What I want is to see only those data in 2nd Column which have the value filled in Column 1. This is should be in run time mode. I fill in the value and then in the 2nd column I directly get to see only common values.
View 2 Replies
View Related
Jan 9, 2015
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
View 3 Replies
View Related
Aug 24, 2014
I have a form used to track attendance of employees:
I want to update a value in Field B to a default value if a specific criteria is selected in Field A. How? I cannot seem to find a answer...
Specifically: If the combo box value "Not Present" is selected from Field A, I want to value in Field B to automatically update to "Not Available".
View 11 Replies
View Related
May 11, 2015
I have the following fields in an A2010 form
PaymentMethod
Total
InstalmentAmounts
OurFee
InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box
Here is the current code
Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)
In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field
So
Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.
View 4 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
Jan 5, 2006
drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.
if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"
Right now I'm getting a circular reference error :/
please advise. Thank you.
View 5 Replies
View Related
Oct 9, 2006
I have two tables:
Table1 - show details of customer payment (full payment / with credit terms up to a maximum of six (6) months)
ColID - (PK/Autonumber)
Col1
Col2
Col3
Col4
Col5
Col6
Table2 - shows insurance agent's prorata commission (based on cleared payment).
ComID - (PK/Autonumber)
ColID
Com1
Com2
Com3
Com4
Com5
Com6
Scenario: If a customer was given 6-month-credit term to pay for his insurance premium, then the insurance agent's commission will also be given in six monthly terms.
Example Computation:
Amount of premium is 12,000 (payable in 6 months = 2,000/monthly)
Agents Commission is 10% of Premium (1,200 = 200/monthly)
What i need is a code to automatically update Table2 if Table1 is updated. If the dbase user updates Col2 based on the amount the customer pays, the field Com2 must also be updated.
I hope this is feasible.
Thanks!
Sheila
View 4 Replies
View Related
Jul 6, 2006
i have set up a database to help track the vehicles my company owns. However on the form i have created when i type in the license plate number want the work area that is responsible for the vehicle to pop up so i can inspect it.the form also has the different things i would look at but i don't want to continue to change the work area that has the possession of the vehicle or have to look it up every time i have an inspection come due. Any help would be appreciated
View 1 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
Jul 12, 2007
I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?
View 1 Replies
View Related
Mar 31, 2008
I have a combobox that selects the customer and shows related information on that customer such as phone number, cc#, etc..
now the trick is i need to allow the selected member to be added to the order information.
For instance, the user selects the customer "Bob" and bobs information is displayed to check for accuracy. After the info is approved the user will continue to process his order. In order to do this I need the customer ID to be the same as the selected customer in the combobox.
Then the user will proceed to fill out the order information, location, date, time, etc.
How can I go about doing this?
Thanks in advance
View 2 Replies
View Related
Jan 25, 2015
I have a calculated field in the form footer which adds up the number of boxes that have been ticked for the received field
=Sum(IIf([Recieved]=Yes,1,0))
If the ticks equal to 3 then I want to update the status field in another table to "Active".I am trying this VBA code but it won't work.
Code:
If Text9 = "3" And custNumber = tblCustomers.custNumber Then
tblCustomers.Status = "Active"
View 3 Replies
View Related
Oct 30, 2012
After testing it, the database got corrupted. I had backed it up just prior to using this.
It was something like = Sum(Abs[AmountPaid], [Paid] = "X")
I have a continuous subform with an "AmountPaid" column. The total is displayed in the subform footer. I need it to display the total for only the fields with an X in them denoting that they were paid. This total should match the statement we receive.
After clicking the button to put the X in the Paid field, then I used the formula to update the AmountPaid field.
Joe..........10..... X
Al..............5.....X
Flo.......... 25
.....Total = 15
View 3 Replies
View Related
Jul 26, 2006
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...
What is the code to have multiple fields updaterd based on what is input into a field?
---
Example:
A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---
I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.
Thanks a million!!!
View 1 Replies
View Related