Problem In Updating Query After Spliting Table, Help Please
Sep 21, 2006
Hi!
The structure is like:
C query is based on 2 tables, A and B.
Then D form is created based on query C.
What I did:
Now I split table A to 2 tables, A1 and A2, which are independant from each other, and I updated query C to retrive data from A1 and A2.
Problem:
Then the form D which is based on C doesn't work properly.
Question:
Do I need to recreate a Query C because I split table A?
or do I need to recreate a From D because Query C is updated?
Thanks very much
View Replies
ADVERTISEMENT
Sep 21, 2006
Hi!
The structure is like:
C query is based on 2 tables, A and B.
Then D form is created based on query C.
What I did:
Now I split table A to 2 tables, A1 and A2 which are indepandant from each other, and I updated query C to retrive data from A1 and A2.
Problem:
Then the form D which is based on C doesn't work properly.
Question:
Do I need to recreate a Query C because I split table A?
or do I need to recreate a From D because Query C is updated?
Thanks very much
View 2 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Feb 5, 2008
Can someone show me how I can split the Zip+4 in this query, if it can be done at all?
SELECT
RTrim([CLIENT TABLE].CITY)+' , '+RTrim([CLIENT TABLE].ST)+' '+RTrim([CLIENT TABLE].ZIP) AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER, [CLIENT TABLE].PIN,
[CLIENT TABLE].A1, [CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]
FROM [CLIENT TABLE];
Thanks
View 9 Replies
View Related
May 29, 2005
Hi, Would it be possible to get the first word from a sentance.
For example
Peter James would Return Peter.
John Michael would Return John etc.
The first word could range from 1 to 50 characters.
Regards
Pete
View 2 Replies
View Related
Mar 3, 2008
Is it possible to update a query without updating the underlying table?
View 2 Replies
View Related
Sep 22, 2011
Let's say I have a table with employee's info In that table there's a field about their salary. In this table I have different pay scales. I have another table with Just pay scales..for instance
employetbl
Auto Record EmployeeName Workaddress PayGrade Salary
1 Joe "B"
2 John "C"
3 Steve "A"
salarytbl
Auto Record Grade Salary Comments
1 "A" $25.00 Technician
2 "B" $22.00 Administrative
3 "C" $23.00 Operations Spec.
If I were to run a a query from the employee table and pull all the fields to my QBE under design view. and selected my criteria and UpdatedQuery to change the update to...(what do i click to get to change the new salary.. ???.
A) Select the proper criteria and and change the update query to the new salary and do it manually?....
B) Can I somehow follow option "A" but at the end select the proper "grade" under my update query and let that somehow change it to the new salary?...(with out me having to type the salary..but only the salary grade?)
C. Or can I just type the auto record under to the updated query . Under my update query to ??.
In other words I am trying to select a salary from me typing a record number or a salary grade field..
View 4 Replies
View Related
Sep 29, 2005
Hi
I have the field fullname in my database which is in the format
Mr John Smith
What I have been trying to do is to extract the Mr then the John then the Smith into 3 separate columns. Can anbody help please?
Thank you
Marcus
View 3 Replies
View Related
Sep 10, 2004
I have a field in a table that looks like this:
hahafdhzuiwre8878878s
agahh87878jkjkkjk
etc...
there is no set length to the bits between the slashes.
I want to write a query that moves the data into a new table in three separate fields using the slashes as field separators.
Can anyone help ??
View 1 Replies
View Related
Jan 14, 2005
i have a field called Items, description
Items Description
1021 125BL/WH
1022 5RD
1023 1458BL/BU/YL
1024 2500GR
1025 5GR
i want to be able to split the numeric from the color code. and add make a new field with the color code, and then have the quantity as a record.
Any Help is apreciated
Mark
View 14 Replies
View Related
Feb 5, 2006
I have a query called providerIds
It appends to the a table called providerIDS
Everything works fine however when ever new data is added to a form
The data shows in the append query but not in the table where it appends to.
How do update the table where the append query is attached to.
View 1 Replies
View Related
Jan 6, 2014
I have a table and a query both of which have the same [Resource ID], [Resource Name] fields.
I am trying to update the [CBL_1_Date], and the [CBL_1_kW] fields in the table with the [CBL_x_Date] and [CBL_x_kW] fields of the query. The problem is that when I try to run the query I keep getting "Operation Must Use and Updateable Query" error.
here is the SQL Code
UPDATE tCBLAggregation
INNER JOIN qBestof4CBLs_avg
ON (tCBLAggregation.[Event Date] = qBestof4CBLs_avg.[Event Start Date]) AND (tCBLAggregation.[Resource ID] = qBestof4CBLs_avg.[Resource ID])
SET tCBLAggregation.CBL_1_Date = [qBestof4CBLs_avg].[CBL_x_Date], tCBLAggregation.CBL_1_kW = [qBestof4CBLs_avg].[AvgOfCBL_x_kW];
View 1 Replies
View Related
Feb 9, 2013
i want to update a field of the table from a field of query .
this is the syntax :
UPDATE tbl_PresentStockAll INNER JOIN qry_BroachStock ON tbl_PresentStockAll.ProductId = qry_BroachStock.ProductId SET tbl_PresentStockAll.Broach = [qry_BroachStock].[BalanceQuantity];
but i am getting error of "operation must use an updateable query".
View 8 Replies
View Related
Aug 9, 2014
I'm working on a project management database for my fathers small business. Data Entry goes as follows: Users enter a Work Order, and all the parts that must be created for that work order. The parts have different processes (tasks) done to them to be created. The database runs a simple system like this as we want it to work. I am now attempting to allow handling of more advanced work orders. Specifically, some parts require the completion of multiple other parts before they can be created.
Here is how the database should handle these events:
1) User clicks button named "requires other parts" when entering a new part.
2) On click a subform is opened that allows the user to enter which other parts need to be completed first.
3) Once all of the parts are completed the next part can begin.
This is my current solution:
1) A query calculates if a part is complete
2) Some form of logic looks up to see if all the required parts are completed. If the answer is yes, a field named ready changes to "Yes" and if the answer is no, a field named ready changes to "no"
View 9 Replies
View Related
Sep 5, 2012
I have a field in table A (used as my main form) that I would like to update with a unique yet understandable name. I have created a query that takes the first few characters of two other fields and the event date and concatenates them to a unique name.
Challenge is the three fields that create the unique name are in table B.
So I tried the following in query design just to see if I could update a field in the same table
Code:
Expr1: IIf(([EVENT_TBL].[EVENT]="Jacksonville" And [EVENT_TBL].[EVENT_DATE]="4/17/2011" And [EVENT_TBL].[EVENT_TYPE]="PRE-DEPLOYMENT"),([EVENT_TBL].[EVENT_TYPDTE_ID]="PREJACK170411"))
The results were #Error# in the cells.
View 14 Replies
View Related
Jan 10, 2015
I need to update a checkbox in a core table from a temp table. How can this be done?
View 2 Replies
View Related
Jan 8, 2014
I have a number hat has been calculated in a query which I want to be kept in a field in a table.
View 4 Replies
View Related
Dec 16, 2014
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
View 1 Replies
View Related
Nov 3, 2005
I created an update query that says it has successfully updated 600 records, but when you go into the table, it has not been updated. I am working with an .mde. Does that make a difference?
I first created a select query to find everyone in TX. Then I changed it to an update query to change all TX to CA. Since it did not update the table, I tried running the update query again and this time it says there are not records to update (since it uppposedly updated my 600 records).
Your help is appreciated.
View 1 Replies
View Related
Mar 4, 2014
I am trying to edit an existing record in my database.It goes to the correct record to update but will not update if "RecordUpdate" is specified as auto numbered.If the "RecordUpdate" is just specified as a number in the query it is fine.
CurrentDb.Execute "UPDATE QryIssueData " & _
" SET RecordUpdate =" & Me.txtRecordNum & _
", IssueDate ='" & Me.txtIssueDate & "'" & _
", Equipment ='" & Me.cboEquipment & "'" & _
", IssueDocNumber ='" & Me.txtIssue & "'" & _
[code]....
View 3 Replies
View Related
Dec 23, 2014
I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])
This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.
View 5 Replies
View Related
Jan 6, 2012
I have created a 'junction' table between 2 many to many tables:
Jobs table (details all jobs)Jobparts table ('junction' table details Job ID, Part ID, quantity and unit price)Stock table (details all stock).The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.
I discovered you can automatically populate the unit price field using autolookup based on the entry of the Part ID field on the form. However having amended some pre-existing jobpart details randomly on the form when I checked back to the table they had not updated. Similarly when creating new entries while the form 'unit price automatically updated on entry of the 'Part ID' these details did not feed through into the table.
I assume it has to do with how the table was originally set up the field properties will need to be changed.
View 7 Replies
View Related
Oct 11, 2007
I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....
My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.
Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?
Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)
I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.
View 2 Replies
View Related
Aug 14, 2013
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
View 2 Replies
View Related
Apr 19, 2013
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
View 2 Replies
View Related
Mar 29, 2013
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
View 2 Replies
View Related
