Queries :: Update Field Based On Existing Record Fields
Jun 3, 2013
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
View Replies
ADVERTISEMENT
Jun 30, 2013
how to Add/Update record including existence of record based on single unique field.Now I trying to learn how to check existence of record on multiple field before adding (at least two) and how to update the record with multiple field record
Below is my testing table fields
Year Month Working Days
2013 Jan 20
2013 Feb 17
2013 Mar 22
Now two situations adding a new record and updating the existing record Which means the unique fields are (Year + Month)
View 8 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
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 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
Jun 2, 2014
I have a table similar to the following:
PatientID | LabID | LabDate | Result
001 | 55 | 01jan14 | 9.5
001 | 55 | 01feb14 | 10.0
001 | 55 | 01mar14 | 8.7
001 | 66 | 30jan14 | 11.2
001 | 66 | 30feb14 | 15.4
001 | 66 | 30mar14 | 13.0
002 | 55 | 01jan14 | 12.1
002 | 55 | 01feb14 | 9.9
002 | 55 | 01mar14 | 14.5
002 | 66 | 30jan14 | 16.5
002 | 66 | 30feb14 | 13.0
002 | 66 | 30mar14 | 10.0
Using a single-step Access query, I need to retrieve, for each PatientID, the most recent LabDate and Result *of a given LabID*. Thus, from the example dataset above, the desired output for LabID 55 is:
PatientID | LabID | LabDate | Result
001 | 55 | 01mar14 | 8.7
002 | 55 | 01mar14 | 14.5
I have searched this forum and others, but have not found an answer that I can directly tanslate to my situation. I have successfully written queries (with included subqueries) that retrieve the most recent of all the Labs, but have failed at obtaining a result dataset that contains only the records within a specified LabID.
For example, the query below fails because whenever the most recent of *all* the LabDates is not the same as the most recent of *the LabDates with a LabID=55*, the correct record is not included in the results. In the example dataset above, 0 records are returned.
SELECT a.PatientID, a.LabID, a.LabDate, a.Result
FROM Labs AS a
INNER JOIN (SELECT PatientID, MAX(LabDate) AS MaxLabDate FROM Labs GROUP BY PatientID) AS b
ON (a.PatientID = b.PatientID) AND (a.LabDate = b.MaxLabDate)
WHERE (((a.LabID)=55));
View 3 Replies
View Related
Apr 19, 2014
My table occasionally gets a second record added for the same person. What is the easiest way to update the new record with the non-zero fields from the older record, then delete the older record? There is a unique id field as well as a timestamp, so knowing which is older isn't a problem. Is there an easy way to do this?
View 2 Replies
View Related
Jan 18, 2015
I have the query below that return a table like:
PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp
What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]
[code]....
View 1 Replies
View Related
Oct 8, 2014
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
View 3 Replies
View Related
Nov 28, 2014
I have one field called FULL_ADDRESS. From that field I am going to populate two other fields. One called ADD_1 and the other, ADD_2.
In the FULL_ADDRESS field I have the following text :
"BOX 2747 ROCKY MOUNTAIN HOUSE AB T4T 1L7"
I want ADD_1 updated to:
"BOX 2747 ROCKY MOUNTAIN HOUSE AB"
and ADD_2 updated to:
"T4T 1L7"
While I understand how to do an update if I was using a space as a reference, i.e.
Trim(Left([FULL_ADDRESS],Instrrev([FULL_ADDRESS]," ")-1))
Trim(Mid([FULL_ADDRESS],Instrrev([A]," ")+1))
I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.
View 6 Replies
View Related
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
May 13, 2015
So, I have two forms that I am trying to make work the same way.
With F1Entry I can use the combo box in the header to select different request numbers.
With F2Finance I cannot do this. It works if I set the Form Record Source to T2FIN, but when I try to Add Existing Field, something about selector combo box breaks and I cannot select different request numbers.
Database attached
View 12 Replies
View Related
Sep 4, 2014
I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].
Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:
[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5
Customer#1 StockType#2 4
Customer#2 StockType#1 5
Customer#3 StockType#1 5
View 6 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
Oct 3, 2012
I have an existing table that has calculations. When I added a new field with an default value of zero, it did not populate the existing records. Now my tables are not calculating. How can I update the existing records with the new default of zero.
View 2 Replies
View Related
Apr 14, 2015
I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
View 1 Replies
View Related
Nov 26, 2007
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
View 2 Replies
View Related
Mar 8, 2015
Exporting all the OLE files in a 2003 database.
Any way to automatically connect these file to an attachment field in the existing record.
View 7 Replies
View Related
Apr 23, 2014
-Microsoft Access 2010
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
Table1[main]
...account1
...account2
...account3
...dol
[code]...
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
View 14 Replies
View Related
Nov 4, 2013
I am trying to add a new email field to my existing database of 900 employees. I have imported an excel worksheet of email addresses into a new table that has lastname, firstname, employee#, email. The primary key on the new table is email. The primary key on the employee table is employee#, and there is a new field titled email.
The email field in both tables are text fields. In running an append query, it either adds 900 to 27000 additional fields of email only or it says cannot complete because of key code violations. On the join properties I have clicked on #3 all records from source to only equal fields of destination.
View 12 Replies
View Related
Nov 26, 2013
I have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."
The expression I've written in the "Field:" box in Query design view is
Expr 2: DateAdd('yyyy',1,[Last_Date])
When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.
View 1 Replies
View Related
Aug 13, 2015
I'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:
AAnnnnnnA
...where A is any letter A-Z and n is a digit 0-9
Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?
View 7 Replies
View Related
Apr 8, 2014
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
View 5 Replies
View Related
Oct 28, 2014
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
View 1 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
