Queries :: Update Query Not Changing All Records?

Apr 24, 2013

i have an update query that looks for a product number, updates the cost, and re-calculates the price in two columns based on the salesman's margins. the problem i am having is that it seems to be hit and miss on the first run. if you run it again, it runs the calculations on all the fields. (it has to check / recalculate 16,000 rows.) should this query have vba to make it loop thru the table.

here is the basic layout of the query:

table 1 is newproductq
table 2 is Products
Field: PriceIn
Table: newproductq
Update To: [Products].[Cost]
UNITS
newproductq
[PriceIn]/((100-[Margin])/100)
PIECES
newproductq
[PriceIn]/((100-[BrokenMargin]/100)

View Replies


ADVERTISEMENT

Queries :: Update Query On Select Records

Apr 4, 2013

I have a Sales Table with below fields, i might have not set it up in the best way possible.

Consumer, Consumer_ID, SaleDate,Prod_Sl#,Prod_Type,Sale Amount

1. I need to update the sales price for each item sold based on category of Product Type, as we are tracking the product with Serial Number.

For an instance if 2 items of Category1 with Prod_Sl# as Sl1 and Sl2 and
2 items of Category 2 with Prod_Sl# as Sl3 and Sl4 are sold.

I need to update the sales price amount for these.

2. I want to accomplish this using a query.

View 1 Replies View Related

Queries :: Update Query Saying 0 Records Will Be Updated

Jun 3, 2014

I have a simple Select Query based on one table.

In SQL View, the query is:

Code:
SELECT SYSADM_CUSTOMER_ORDER.ID, SYSADM_CUSTOMER_ORDER.STATUS
FROM SYSADM_CUSTOMER_ORDER
WHERE (((SYSADM_CUSTOMER_ORDER.ID) Like 'Q%') AND ((SYSADM_CUSTOMER_ORDER.STATUS)="H"));

So basically getting all records in the CUSTOMER_ORDER table that have ID beginning with Q and the STATUS is H (on hold).

I want to simply update these to change the STATUS to C (closed).

I converted the SELECT Query to an UPDATE Query and added a "C" in the Update To Field.

The SQL View is now:

Code:
UPDATE SYSADM_CUSTOMER_ORDER SET SYSADM_CUSTOMER_ORDER.STATUS = "C"
WHERE (((SYSADM_CUSTOMER_ORDER.ID) Like 'Q%') AND ((SYSADM_CUSTOMER_ORDER.STATUS)="H"));

But for some reason Access is telling me that it will update 0 records. There are over 2500 records to update.

View 8 Replies View Related

Queries :: Update Query - Add Records To A Join Table?

Apr 11, 2013

I have 2 tables that are joined by a many to many table:

tblProductInfo
- ProductID

tblProductLinerMM

- PLProductID (FK to [tblProductInfo].[ProductID])
- PLLinerID (FK to [tblLiner].[LinerID])

tblLiner

- LinerID

I have a range of products that each use 2 liners. An inner liner and an outer liner. I need to add 2 records per product to the tblProductLinerMM table.

for example

tblProductInfo has the following records:

- 2138557
- 2378954
- 4387657

tblLiner has 2 liners in particular that relate to these products:

- L5475
- L5468

I need to create the following records in tblProductLinerMM preferably with the use of a query :

- 2138557 | L5475
- 2138557 | L5468
- 2378954 | L5475
- 2378954 | L5468
- 4387657 | L5475
- 4387657 | L5468

View 1 Replies View Related

Changing Records Through Queries

Aug 19, 2005

Whenever I have a form that is based on a query, I am not able to edit the data in the form. Is there any way to change that? I have all allowances set to yes and Data Entry also set to yes. The form contains data for a single record.

View 3 Replies View Related

Changing A Field Value For All Records In A Query

Mar 9, 2006

I have a query that pulls records from a main table based on a check box and I need a macro or code that will set the field value as if it were unchecked.

BTW I am self-taught and new at this so don't make fun of me if the question seems stupid.

Thanks!

View 8 Replies View Related

Queries :: Update Query (table To Table) Not Updating All Records

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

Queries :: Changing Value Of SQL In Query

Apr 9, 2015

So I have created a select query and I am using it as an all purpose search query. I plan on changing the value of SQL in the query based upon what the user selects as the method of searching. So there is a drop down menu, that allows the user to select the method, this will be expanded in the future but for now till I get this working I am not going to bother with that. That being said, I figure once I get a couple of these the work the others should follow suit. Anyway, here is what I have so far.

Code:
Dim StrSQL As String
Dim qdf As DAO.QueryDef
Select Case Me.Combo3

[code]...

View 6 Replies View Related

Queries :: Query Changing Criteria On Its Own

Dec 16, 2014

I have a query, and in order for it to run properly, I've set it to look in an unbound combobox on a form to display the necessary data I want, as long as I never close down the form, this is exactly how I want it.However, if I close this form down, and reopen it, I'm asked by messagebox to get the lookup from a different form's combobox, despite saving the correct form's design over and over again, and saving the actual query, more than once, while in design view.when Access changes the criteria, there isn't even a combobox value on the form it changes to.

View 3 Replies View Related

Can I Get An Update Query To Not Add Records To Tables Only Update?

Jun 28, 2005

Hi Guys,

I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.

It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.

Any suggestion guys & gals?

Many thanks
Tim

View 9 Replies View Related

Queries :: Changing Text To Hyperlink In Query

May 7, 2014

I was trying to see if there is a way to convert text to a hyperlink in a query.

Background : I'm pulling information from a lotus notes database using an OBDC connection and storing it into an access database and using that to upload to a sharepoint site. While we are converting over to a sharepoint site, I was trying to make it easy to get back to the original site for each document to check the work.

One of my columns in the query looks like this:

Code:
Notes Link: "notes://pncpgha66/852570CE0056FEF3/0BFCC83A5C11D7C3852576CD0072ED3E/" & [NoteUNID]

I don't see a way to convert that whole part to a hyperlink though. Like I could with a date and text. Cdate().

View 1 Replies View Related

Queries :: Update 5 Records At Once

May 6, 2013

I'm looking for a way to fill out 5 records in one row in a table at once. I want to update a supplier and price per unit for supplier via a form. I have price per unit 1-5 and the price will be the same for all 5 records. Is there a way to do this through append or update query and if so which is best in my situation?

View 12 Replies View Related

Queries :: Changing Field Format In Append Query

Dec 3, 2014

I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.

View 8 Replies View Related

Queries :: Dates Not Changing - Incomplete Query Clause

Jan 17, 2014

I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay

I am using the Query Design Grid. This is the SQL code behind the query:

UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);

When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?

I cannot get the dates to change by using a specific date either

UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;

View 1 Replies View Related

Queries :: Changing Query In VBA Not Working With Calculated Dates?

Sep 9, 2013

I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.

Public Sub SetDate1()
'Apply date filter and rebuild query in real time
On Error GoTo Err_SetDate1
If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1

[code]....

View 3 Replies View Related

Queries :: Changing Query Outcome Based On Field

Oct 18, 2014

I have a query that is based on Property & tenants. It runs a report that gives me all the property listed with the tenants & other info for the property's. Sometimes the tenant moves out & the property is Vacant, so i have a Vacant flag in the property table.

But when i run the query it prints out the last tenant. Can i pick up the vacant flag & change the tenant name to Vacant on the report? As i don't want to delete the tenant from the tenant table.

View 3 Replies View Related

Queries :: Changing Date / Time Format Using Query

Jun 26, 2013

I am having difficulties changing a date/time field like '22/04/2013 09:14' to make it look like '2013-04-22 09:14' using a query.

View 3 Replies View Related

Modules & VBA :: Update Every Records Using Queries

Dec 27, 2013

I am new to MS Access and using MS Access 2013.I just added 50 new fields into a table. I need to update these new fields on every record.The table layout is as followed:

Table1
-Company Id
-Question1
-Question2
-Question50

[code]....

View 6 Replies View Related

Queries :: UPDATE Multiple Records In Same Column

Jan 16, 2015

I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.

Code:
UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A';
UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';

View 4 Replies View Related

Queries :: Stakeholder Database - Update Multiple Records At Once

Apr 15, 2015

I am building a stakeholder database in Microsoft access 2010 and I want to be able to say that a stakeholder attended an event. I have managed to do this but I can only say that one stakeholder attended an event at one time. This is quite a problem as there can be up to 800 or even more stakeholders attending an event so to go through and click each one would be very time consuming.

My current set up is I have a stakeholder table an events table and an event attendance table. It all works fine apart from only being to edit one record at a time. I have tried update queries with no success, I can attach the database but would need to remove the data for data protection reasons.

View 3 Replies View Related

Queries :: Update And Insert Records With SQL Statements Using Access 2010

Jul 10, 2014

I am trying to update and insert records with SQL statements. Below my code:

Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"

[Code] ....

My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.

View 14 Replies View Related

Queries :: Update Table If A Field Is Blank Without Clicking Through All 5K Records

May 7, 2014

I am putting the finishing touches on a DB and have come up with a a problem which I cannot solve. I have code on my data entry form that concatenated the days and times of up to five days. That is : [Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" and so forth for five days. It has a complicated (for me) nested IIF statement and it works fine on the form. But I have now transferred the data from an old DOS DB and I do not want to go through almost 5K records just to update three fields.When I tried to do it in an update query, it said that the string was too long for the update. My update is:

IIf([Day5] Is Not Null,[Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" & " " & [Day2] & " " & "@" & " " & Format([Time2],"h:nn AM/PM") & " " & ";" & " " & [Day3] & " " & "@" & " " & Format([Time3],"h:nn AM/PM") & " " & ";" & " " & [Day4] & " " & "@" & " " & Format([Time4],"h:nn AM/PM") & " " & ";" & " " & [Day5] & " " & "@" & " " & Format([Time5],"h:nn AM/PM"),IIf([Day5] Is Null And [Day4] Is Not Null,[Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") &

[code]...

how to update the table if a field is blank without clicking through all 5K records? I have tried simple concatenation, but I usually get a lot of extra @ in the field.

View 2 Replies View Related

Queries :: Update Multiple Table Records With Button Click Event

Oct 29, 2014

I have a table with many records, using a form with an update button click event, I would like to update ALL records where the Item in the table = the Item in the form.

There are 6 checkboxes and 6 text boxes that will need to be updated, but right now I am just trying to test if i can even change one text box to keep it simple. I have tried to start by using this:

Code:
Dim mySQL As String
Dim ItemNo As String
Dim SO As String

mySQL = "UPDATE BT200 SET Part = '" & SO & "' WHERE Item = " & ItemNo
DoCmd.RunSQL mySQL

So every time i run this command button, it gives me a prompt "You are about to update 0 row(s)". Nothing gets updated at all, I am clearly missing something vital here.

View 7 Replies View Related

Values Changing On Import Or Update

Oct 21, 2005

Can anyone help -
I am importing an Ascii text file with decimal numbers ie (144392.26). the receiving field is defined as double with decimals set to auto. I have tried many other formats such as fixed 2 decimals, or Currency 2 decimals. In all cases the data has changed from its original value to 144392.265625 which of course rounds to 144392.27 on all reports. If I use a link to the same Ascii file the data retains its original value when it is appended to an empty table. But ... When I try to use that correct value in the appended table to update another table it again converts the amount to multiple decimals and may increase by more than .01.

Is there away around this problem.

Jim M

View 2 Replies View Related

Update Is Changing Field Type

Oct 1, 2007

i have a temporary table which is created by importing an excel file

some extra fields are then added to the table using sql vb code for example

strSQL = "ALTER TABLE [TBL_TmpSubmission] ADD COLUMN [EnergyUnit] TEXT;"
CurrentProject.Connection.Execute strSQL

these fields are later populated conditionally using an update exectuted again from vb code

If Me.cboProgram.Column(0) < 3 Then
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'GWh'"
Else
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'CarbonTonne'"
End If
CurrentProject.Connection.Execute strSQL

the problem is that this field now has a memo data type, this is a problem because i need to write a query which uses this field in a table join and access won't let you join on the memo type

anyone know why this might happen? for the time being i'm going to try and fudge this by using a further ddl statement to change the data type back to text but i'd rather know whats going on here

View 8 Replies View Related

Update Query Not Updating Records In Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved