Queries :: Update Query That Ignores Missing Fields?
Jul 28, 2015
I'm trying to create a single, Update Query in Access 2007 that does all of these 3 things:
Update (skipping zero-length values)
Append
Automatically handle missing Fields without showing any "Enter Parameter Value" prompts.
I've managed to accomplish the 1st & 2nd items on my own, but I'm stumped on the 3rd. In essence, what I'm looking for is a way of telling Access to automatically use a zero-length/null value for any missing Field(s) the Query encounters, so long as it does not involve manually modifying the Table beforehand. Automation is key. And that's where you guys come in.
Attached, is a diagram of the desired results applied to a set of sample Tables. And here's the SQL code for the Update Query adjusted to work with those sample Tables:
Code:
UPDATE Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID SET Table_1.ID = NZ([Table_2].[ID],[Table_1].[ID]), Table_1.Company = NZ([Table_2].[Company],[Table_1].[Company]), Table_1.Address = NZ([Table_2].[Address],[Table_1].[Address]), Table_1.Phone = NZ([Table_2].[Phone],[Table_1].[Phone]);
I have a fairly simple query to weed out all the records in our database that are missing vital pieces of infomation :
Code: SELECT Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code FROM Contacts WHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));
Is there anyway to count how many fields are missing for each record ?
I am trying to update several fields all at once in one single query. Also, I can only use the Design View (No SQL)
The weirdest thing keeps happening though: The more fields I enter to get updated, the less rows get updated.
For example,
When I have only one field that gets updated and I click "run" the window pops up saying I'm about to update 9 rows. It asks me if I'm sure I want to update those records so I say "no".....
Then, I add in another field and click "run" and the window pops up daying I'm about to update 8 rows. When it asks me if I'm sure I want to update those records, again I say "no"......
Then, I add in another field and this time it says 7 rows..... and the trend goes on and on until it says I'm going to update 0 rows.
I have about 15 fields I need to update all at once. I don't get why its not letting me update them simultaneously.
I'm mid build on a commitments tracking (pseudo Purchase Order system) project, but seem to have run into an issue with a loop I'm building to allow users to edit commitments. The idea is that an edit form is launched, values amended and the submit button is clicked. A VB subroutine then validates the entries to ensure that the mandatory entries are included. It then writes a copy of the original values to the Archive table, before attempting to update the existing commitment with the new values..
This is working perfectly, apart from the fact that 3 fields that are "updated" are being updated to a blank value..
Code: Private Sub CommitSubmit_Click() Dim SQLStr, LastID, DOwner, DHeading As String Dim ErrState, Dtype, DProperty, DTCA, DITD, DSD, DED, DSP, DRetention, DRA, DRPD, DSupplier, DDOW, DStatus, DUser, DShD As String Dim Authcheck, Complete, ErrMsg As Boolean Dim QDF1, QDF2 As QueryDef
I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions: <>"CINEMATIC" And <>"SFX" Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*" [Forms]![FRMscriptPrintReview]![selectLangCombo]
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
I have an access database with several linked tables (linked to MySQL database) and several local tables. The theory is that if there's ever a connection issue, the device connected to the computer will continue logging data to the local tables. Once a connection is re-established, the linked tables should be updated with all the missing records which appear on the local tables.
I found several possibilities which I outlined below, but I've been asked to investigate whether there's a built in function in access that does this for you and use the other options as a last resort. Does access have any program feature that updates one table with missing data from another table, or will I have to write VBA code to do that? Options I've discovered:
1) Write unmatched query and insert missing data into table.
2) Create a linked table on MySQL that will link to the local tables on access, then compare the records there.The boss isn't happy with those options because he wants to keep the amount of code we add to a minimum. Ultimately, we hope that a program feature that does this is built in to access. If not, I have no problem adding code to do this instead.
I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:
Syntax Error (Missing Operator) In (Field Name)
I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.
I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.
I have a master table that holds all of my data. The table details what qualifications someone is holding.I would like a query that would enable me to produce a list of people who DO NOT hold a qualification.
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
The following code supposed to let me print all records OR only those where dAreaFK = myCBO currently I get an error message "Object missing".if i remove this: Or Me!cboStatsArea Is Null..from the last line the it works but only if i make selection in combo.
Code: Private Sub cmdPrintOpen_Click() 'Print open defects using R_Open_details Dim i As Integer i = DCount("*", "Q_Open_details", "dAreaFK=cboStatsArea OR cboStatsArea IS Null") 'MsgBox "The count of rows is " & i If i = 0 Then MsgBox "No Records available for print", _ vbOKOnly, "Error" Exit Sub End If DoCmd.OpenReport "R_Open_details", acPreview, , _ "dAreaFK=" & Me!cboStatsArea Or Me!cboStatsArea Is Null End Sub
SELECT Costs.Costs_Per_Capita, Costs.Period IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1), (Select AVG(Costs_Per_Capita) From Costs Where Period = 2) AS result FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
I'm having problems with an Update query. I have data coming in on one file that I am loading to a temp table that I want to update on another table in my database. The problem is that when I run my update query, it is wiping out everything that DOESN'T match. I have 5 fields that MUST be equal before the update can occur. I'm trying to update 3 fields in my "master" table.
Here is my SQL:
UPDATE NPPDcopy LEFT JOIN tempNPPD ON (NPPDcopy.Hour_Ending = tempNPPD.Hour_Ending) AND (NPPDcopy.TieLineName = tempNPPD.TieLineName) AND (NPPDcopy.AdjBalancingAuthority = tempNPPD.AdjBalancingAuthority) AND (NPPDcopy.BalancingAuthority = tempNPPD.BalancingAuthority) AND (NPPDcopy.OperatingDay = tempNPPD.OperatingDay) SET NPPDcopy.Import = [tempNPPD].[Import], NPPDcopy.Export = [tempNPPD].[Export], NPPDcopy.Net = [tempNPPD].[Net];
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?
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?
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.
I have two tables table1 his a master list of companies that I have already delt with table2 is a list of companies that I have delt with as well as new companies and I allow for multiple instances of company data to be in table2
table2 my have several records refering to "ABCD Inc" but table1 will only have it once
If new companies show up in table2 how can I get a mass copy of all the companies that are in table2 but not in table1 into table1?
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.
I have a one-to-many query with two tables. I'm using the query as a record source for a subform.
The subform displays the correct data I'm after however I can't update any fields on the it due to the underlying query having a one to many relationship. I understand that and thats fine.
Problem is I need users to be able to update one field on this subform as required. Is there a way to allow this while keeping the underlying query the same?
am trying to change the tab order but have noticed that some of the fields are missing from the tab order form. I am attaching a screenshot to show which tabs are missing.
Access2007- Building a query to handle future input...
I run an update query that does what it is supposed to do and gives the proper values.
THEN I add a one more field to the source with the builder using + that has no data in it yet, the query fails and gives blanks, even though one of the fields in the source has data in it.
I would think that the "no data" field would be considered 0.00 value and be added together to the field that has values giving at least the value of the field with values as a result of the query.
Is there a setting somewhere that I need to change so that the no value and the value would be combined? I am using the builder to combine the values in the query.
