How do I get an update query to only copy part of a fields value?For example: The original field has a date in month, day, and year. I only want to update the new field with only the year.
View RepliesI have been spending all my today to fill a combobox dynamically, but have not been able yet.
I have a combobox and a pass-through query in access, which is working fine and fill the details into the combobox via data source. Now what I am planning to do is to update the combobox source as soon as value in a text box changes.
Here is the code I am using, but it is not working:
Dim rs As Recordset
Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("get_data")
qDef.SQL = "SELECT Initial + ' (' + Name + ')' uws FROM EM.dbo.UW" _
& " WHERE lob = '" & addSingleQuotation(Me.CMB_LOB.Value) & "'"
Me.cmbUM.RowSource = qDef.SQL
Me.cmbUM.Requery
I also used Recordset, but did not work:
Set rs = CurrentDb.OpenRecordset("get_data")
Me.cmbUM.RowSource = rs!uws
I am provided a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process.
What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).
After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.
TypeDescrPreEncEncExp
VOUJsmith-Instructor, 12/16/13$0.00$0.00$45.00
POJSmith-Instructor, 12/16/13, Course1, Parking($45.00)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Parking$0.00$45.00$0.00
VOUJsmith-Instructor, 12/16/13$0.00$0.00$221.13
POJSmith-Instructor, 12/16/13, Course1, Lodging($221.13)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Lodging$0.00$221.13$0.00
In access Im working with two tables, this is my setup
tableA.documentnr
tableA.revison
tableB.documentnr
tableB.revision
Both tables are filled with data, Table B contains the same kind of data as table A, But tableA has documentnumbers with different revisions (for example revision a,b,c, for each revision a seperate row). Table B might have an identical document, but just one revision (like revision a).
Now I like to append the data of tableA to tableB, except if a revision is similiar to a revision in table A. (There is more metadata involved, but I will do it step by step)
Im not working with primarykey data, becayse in the end result table B will also have multiple (identical)document numbers with different revisions on different rows.
I tried to use the update query but it doenst append the documentnumbers where the revision is not present in table B I attached a image of the tables.
Is it possible for an update query to simply replace all the data in a table's field with the results from a query?
For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.
I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.
When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table
I've attached a photo ......
I have a table that is updated from an update query which gets its data from a table, which gets its data from a form. I have set the query to only update the current record, this is done by a macro when the user exits the input box. The problem arises when a user goes to visit past record, I would like it to not overwrite the old data with the new data. If the query was only allowed to ADD data and not overwrite then this would fix the problem!
My SQL code is as follows:
UPDATE [Run Info], [Sieve Weights] SET [Run Info].[315 Tare] = [Sieve Weights]![315 Tare], [Run Info].[250 Tare] = [Sieve Weights]![250 Tare], [Run Info].[200 Tare] = [Sieve Weights]![200 Tare], [Run Info].[160 Tare] = [Sieve Weights]![160 Tare], [Run Info].[100 Tare] = [Sieve Weights]![100 Tare], [Run Info].[75 Tare] = [Sieve Weights]![75 Tare], [Run Info].[50 Tare] = [Sieve Weights]![50 Tare], [Run Info].[BD Tube Tare] = [Sieve Weights]![BD Tube]
WHERE ((([Forms]![1L Input form]![ID No])=[Run Info]![ID No]));
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
View 1 Replies View RelatedI have a form where I enter the students lastname, firstname
I need a field that just pulls the first 2 letters of the lastname as well
Is there an easy way to do this
I have a form where I enter the students lastname, firstname
I need a field that just pulls the first 2 letters of the lastname as well
Is there an easy way to do this
I have a form where I enter the students lastname, firstname
I need a field that just pulls the first 2 letters of the lastname as well
Is there an easy way to do this
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
I'm trying to build a query that groups data by a code that is six digits. however, I only want to consider the first 4 digits when grouping by. (ex. table has code 100023 and I only want to group by 1000). Is there a way to do this within a query instead of modifying the table itself?
View 1 Replies View RelatedI understand that a query can accept wildcards in the search criteria but this is different.
I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".
I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.
In other words, the record entries themselves need to be treated like wildcard entries.
I have a table with Part No. and their correlating descriptions. I have a second table with a Product Number and its assosciated Part No. I want to view all of my Part No. with descriptions and IF there is a match to the Part No on the second table, I want to see the associated Product Number. Below is the layout and SQL on my test tables.
Test_Items1 (My Table)
Part No = 1317667223606 Desc1 = ABC123
Part No = 1317667223614 Desc1 = ABC456
Test_Products1 (Customer Data)
Product Number = 14_01 Referenced Item = 1317667223606 1317667223735 1317667224517 1317667225406
SQL (Which isn't working for me)
SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number]
FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";
The Desired Results from my above scenario would be this
Part No = 1317667223606 Desc1 = ABC123 Product Number = 14_01
Part No = 1317667223614 Desc1 = ABC456 Product Number = Null/Blank
The Part No from Test_Items1 may not always be the first string from the Test_Products1.Referenced Item. It may appear anywhere withing that group of Part Nos. The customer has them separated by spaces.
I have prepared a table in a database which includes a yes no box to one of the fields.
The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.
I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table.
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies View RelatedWhen i go to input my query, firstly it doesnt display results half the time, secondly it is normally very problematic in terms of being case-sensitive, exact word matching etc.
What do i use (criteria) to program the query to let me use partial and non case-sensitive search methods?
PLEASE HELP ME!!! PLEASE!!!!
Running 2007
I never ran into this problem before.
I have a qry, with showing totals (group by). One of the fields is called comments, which is a memo field. I am only seeing partial comments.
When I redesign the query and take out the "totals", I see the entire comments.
Is there a fix to this?
Hi all:
Well, as part of my new job I get to revise a current database for a client and I am hitting a brick wall in my mind at the moment and could use some ideas thrown my way on how to deal with it.
First of all I have two tables -
Table AR_Due
AR_Due_ID - Autonumber (PK)
Entity_ID - Long Integer (FK)
AR_Due_Amt - Currency
AR_Due_Date - Date/Time
AR_Due_Waive (boolean and if checked the amt due is forgiven and not due)
Table AR_Pay
AR_Pay_ID - Autonumber (PK)
AR_Due_ID - Long Integer (FK)
AR_Pay_Date - Date/Time
AR_Pay_Amt - Currency
Now, I have to pull all AR_Due where the payments are late (past the AR_Due_Date) and have no in the Waive column and, if partial payments were made those payments can't be past the due date or else they need to be included as late (that's my main issue - trying to figure out the logic on that one).
So, any ideas on how that might be best accomplished? I need a report that shows all late payments (not counting lates more than two years ago) including any where there were partial payments made but the total partial payments were not made before the due date.
I'll take any ideas, although I might not be able to use some of them due to having to follow certain standards here (don't know all of them yet but I've kind of figured a few out based on the existing db). And, I didn't put this all together so there are some things I can't change (at least at this point), but I'm happy to get any ideas that might spark the "aha" moment :).
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. [...].
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
So now, I am almost done with forms (at least I want to believe that). But I ran into an issue I would love to have your comment and different approach on:
The Database I create is projected on another, significantly bigger database! The one I create is a kinda light version for other field offices with only the information they need to have, and additional tables for them to gather their own data and track their own activities. This being said, the light database needs to be updated every month once by the big (mother version). Both DBs cannot be connected!!!
So we will send to the field offices altogether 5 tables in one mdb-file every month. These 5 tables exist with the same structure ( name, field data-types, etc.) in the light version.
I now want to make an automatic update option (by pressing a key and select the "update-file"), i.e. based on the the respective Primary Key of every table the entry in the smaller DB should be updated!
Update for me means the following three things (Assume big DB is A, small DB is B):
1) If Primary Key (PK) exists in both tables of A and B, update the rest of the fields in B belonging to that key based on the specific record of A
2) If PK does not exist in A but in B, delete record from the database B (case: record was deleted in A after last update)
3) If PK does not exist in B but in A, add the record to database B (case: record was added to database A after last update)
I would like to show you what I already found in the internet on solving task 1) but I cannot post links due to my status.
Both links basically describe how to update one table by another in importing or linking the update-source table in the current DB, create a query with both tables, link the PKs of both and then using the Update To Criteria [UpdateSourceTable].[Field] for each field that I want to update.
But I wanted to ask before I implemented that if I should make a complete different approach since I also have 2) and 3) as tasks....
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies View RelatedI run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I am facing problem regarding updating of certain field of table. For example i would like to take data from a column of Table1 and would like insert that data into particular column in Table2.
Suppose Table One has data:
Table 1
ID Name amount
1 a 0
34 b 0
3 ab 0
Table2
ID Name amount
34 a 400
1 b 900
3 ab 4500
There are about 2000 records in each table.I would like to update column "Amount" of Table1 by taking data from Table2 "Amount" Field. how can i update the amount column by keeping the ID field?
I want to say update set field1 = field2
But field1's data type is Number and Field2 is Text. (Field1 is fed by a lookup table). Am I doomed to go through the 700 or so by hand, gurus, please?
I would like to run a query and then be able to edit that information. I want the data I change to be put into the table the info was gathered from. Right now when I edit the query data it only saves it there, not on the other table. I tried to do the copy to table but the original table was going to be erased and then written over. What should I do? thanks
View 3 Replies View Related