Update field1 if Null, if Not Null update field2

I am importing a data extract with grades into a student database and using that to update a "Grades" Table. I need to do this every term. This table is then presented in a nicely organized Form.

I have no problems doing this for required courses, which I made their own separate columns for (ex. Course1-Term, Course1-Grade).

The problem is that there are also three electives the students are required to take. These electives can be taken from other programs or even other schools, and as a result it would be impossible and messy to have columns for every possible course.

I thought it would make sense to organize that section a bit differently. The problem is in figuring out how to update the table with my data extracts.

I can get data extracts in Excel which are organized like this (each is a column):

Sponsored Links:

Related Topics

Update not overwrite
Can you please give me the code on which I can just update an existing entry in a table?
Suppose I have a table that has field names, field1, field2, field3. field1 and field2 has already a value and field3 = Null (im talking about the value in the database)
what I want to happen is to have a form to be filled up to update a value in field3 without affecting field1 and field2. I hope I made it clear.

Converting text to numbers for limited rows
I have a table in SQL Server, which has two columns one of which contains

* numbers as characters (eg. 214.67)
* the letter U

I need to populate the second column with

* conversion of character numbers to numbers
* zero if the other field contains the letter U
* 999,999 if the other field is NULL

I can select only those records which contain numbers as characters

select field1 from Table
(field1 is not null
and field1 'U')

however when I use this in the update I get an error.

SET Field2 = val(Field1)
(field1 is not null and field1 'U')

Selecting only blank fields in access 2007
Lets say I had a table like below, and I only want to select the name and any field which was blank / null
For example, if the name was John and field1, 2, and 6 were blank I would want my results to be
Name Field1 Field2 Field6
John null null null

Count NULL togerther w/ No Stat records
My table:
1 NULL No Stat
1 No Stat NULL
1 No Stat No Stat
1 otherVal otherVal
The result that I want:
I want to select records that contain either NULL or No Stat in either FIELD1 OR FIELD2
then Count #rec for any field that has NULL or No Stat
The problem is the count function does not count NULL, so I use IIf statement to replace NULL with No Stat. I can display those values on the screen correctly, but cannot count these NULL recs together with the No Stat recs. Please give me the SELECT statement that does this

Concatenate multiple fields that might have a null value
Hope someone can help. I'm trying to concatenate many fields into one field and some have a null value. And some need to have a description added in front of the value.
Field1 = Bldg Name (maybe null)
Field2 = Street
Field3 = Floor (maybe null)
Field4 = Room (maybe null)
Field5 = City

My problem is that in front of [Floor] should be "FL:" only if that field is not null. However, I keep getting the "FL:" in front of everything. How do I not include that when that specific field is null?

Delete duplicates with conditions
I have a table with FieldA, FieldB, FieldC.

I want to delete records based on FieldA, leaving me with one unique record. The corresponding data in FieldB and FieldC is sometimes NULL - sometimes not.

How do I DELETE duplicate records based on FIELDA where FieldB and FieldC contain the most amount of data (or contain the least NULL values) I.e.

Field1, Field2, Field3
A , data , null | DELETE
A , data , data | KEEP
A , data , null | DELETE
B , null , null | DELETE
B , null , data | KEEP
B , null , null | DELETE
C , null , null | DELETE
C , null , data | DELETE
C , data , data | KEEP

update to null
I would like to update a field in my update querry to clear its data.i tried NULL in the update to but it didnt work

ORDER BY ... If (SQL syntax question)
I have the following as the Row Source for a list box:

SELECT Field1, Field2 ORDER BY Field1, Field2;

Supposing the possible values for Field1 are 1 and 2, how do I get Field2 to sort in ascending order if the value in Field1 is 1 but in descending order if the value in Field1 is 2? I'm guessing it's something like this, but I don't know enough about SQL to work out the correct syntax:

SELECT Field1, Field2 ORDER BY Field1, If(Field1=1, Field2 ASC, Field2 DESC

updating field whose value is the word NULL and Not a typical 'null' value
I was given a table where in a text field, the value is NULL. (Actually spelled out like that.)
I need to convert that field to a date/time one. Which is being hindered by these values of NULL.

The problem is that when I put the word NULL in the field criteria spot, Access believes that I am referring to a value of 'null' and will not do the update.
I have done "NULL", NULL, =NULL, ="NULL", is NULL. and no success.
Please remember that the actual value is not 'null'. It is a text field that has the word NULL in the field.

Fill in Null values
I have a problem with an Access table, I simplified the issue in the below attached zip file

There is a field that looks like this:




and the final results should look like this

Field1 Field2
a a
b b
e e

I tried using dlookup and dsum in a query but I did not succeed

Can access use the SQL SUM function?
This issue has been resolved elsewhere, but thanks go to plog for his help and insight.

I'm trying to run a query as a rowsource in a combo box as

SELECT Table.[Field1], Sum(Table.Field2) AS Total_Sum FROM Table WHERE (((Table.[Field3])=[Number]));

If I remove 'sum()' then the box shows the first results from Field2, but as soon as I add 'sum()' it returns Field1 instead, I assume this is because Field2 returns null.

VB Help update is supposed to be null.
I currently have the following vb code, which works if you are trying to update a record, but it still gives the message prompt when I am trying to add a new record and the field that it is trying to update is supposed to be null.

How can I have this code function when trying to update a record already filled out and not pop up when adding a new record?

Update field2
I have a table called DATA, field1 is populated from an import. what I want to do is populate field2 with data. the datain field1 starts with a 4500* and all 100* numbers below relate to the 4500* above it. I need to put the 4500* number at the side of the 100* it relates too. here is an example, ....

update a table
You have been very helpful with my previous issue, I am trying you again.

I want to update a main table if a secondary table has a particular value. I am assuming you have to write every field in a table when data is being updated; that you cannot just send one field a new value. If this is true, is there an easy way to pull the matching record from the main table and immediately update from the secondary table?

Such as sql1 = SELECT * FROM myTable WHERE aValue = keyValue
then turn around and sql2 = UPDATE myTable SET field1 = !fieldname, & field2 = !field2, etc,.

This code did not like the !fieldname or recordset!fieldname. So, do I have to read from the main table, put the values into variables then update main table using the variable names?

Even if it is telling me to limber up my fingers and start typing

Sum of field1 where field2 is true
Field1 contains a number
Field2 contains a checkbox (true/false)

I have a textbox in the report saying (Control Source) =Sum([Field1]) to, of course give me the sum of all of that field.

I am trying to get another textbox to sum the records in Field1 for all the ones in Field2 that are checked (True).

It seems I can do this in the Control Source with an expression but I have no clue how to get there. Something like:

=Sum([Field1]) Like ([Field2]) = True

Am I barking up the right tree?

I hope my information is sufficient

Update query
I am trying to create an update query that will read an ID from one table [Updates], and update it in another, [Original]

[Updates] has the following structure:

ID FieldToAmend Change To

[Original] has this structure.

ID Field1 Field2 Field3 .Field 70

I create a query to update one field at a time, but I obvioulsy would like to update them altogether.

Update where query not working
This should be simple but I can't figure it out.

I have a large table that contains NULL values and values like €0,00 in a field with the name price. I need to replace these values with €0,01

So I thought my rusty sql knowledge and google could help me solve this, but I am stuck.

To remove the null values I used the query:

UPDATE tblItem
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is Null));

and that worked well to remove the NULL values and changed them to €0,01

00 to €0,01

UPDATE tblItem
SET tblItem.prijs = "€0,01"
WHERE (((tblItem.prijs) Is "€0,00"));

But it does not and I am puzzled why. It must be something very obvious that I am overlooking, but what?

Update Query Not working
so I have a field named Vendor Claim # that I need to be updated via a prompt. Currently I am using an update query with the following IIf statement in the Update To: IIf([vendor claim #] Is Null,[What is the Vendor Claim # - This is NOT CLIENT CLAIM #],[vendor claim #] & "/" & [What is the Vendor Claim # - This is NOT CLIENT CLAIM #])

If the field is null I want it to update what I type in, but if it already has a value I want to add to that with a / in between the two values.

I don't want it to update the vendor claim # if the value I put in is the same as what is already there so I put in the following criteria: <>[What is the Vendor Claim # - This is NOT CLIENT CLAIM #]

The problem I am running into using this query is that it won't update the field if the Vendor Claim # is null. I don't know what I am doing wrong so

Using a Query to Set a Field equal to a Sum
I'm using Access 2007, and I want to calculate the sum of a field in one table and store that sum in another field in another table. I don't expect the original field to change much so I don't want to use a query just to calculate the sum and display it, I want to actually store it and display it whenever I want. Something similar to what you would do in Excel, except with sums from different tables. And if the original field does change, I want the sum to also update and the field in the other table to update as well.

So, to simplify, here is the above broken down:

I have Table1 and Table2 and Field1 and Field2.

Table1 --> Field1
Table2 --> Field2

I want the Field2 = Sum of Field1. I've read everywhere it's better to just calculate the number rather than store it in a table, but I don't need the number to be recalculated since it will most likely never change

duplicate alias ??
I have what might be a simple problem if I knew access/sql better. I have 12 fields (field1 through field12)which may or may not be populated. I know how to do this with other languages with if/else structures but it looks like access/sql doesn't have an ELSE structure.

I have to create a new table, with a new field populated with the contents of the last populated field.

in other words. if the 12th field is not null, populate fieldnew with the contents of field12
if field12 is null, then if field11 is not null,populate fieldnew with the contents of field11 |
if field11 is null, then if field10 is not null,populate fieldnew with the contents of fieldl10
if field10 is null, then if field9 is not null,populate fieldnew with the contents of fieldl9
if field9 is null, then if field8 is not null,populate fieldnew with the contents of fieldl8