Update Query to Update Info from One Table to Another

[Mytara]

I have an "update table" that I need to update to my "main information",
however, I only want the update to happen if the numbers in the "update
table" are larger than the numbers in my "main table", or if my "main table"
is blank I need the number from the "update table" to be put into my "main
table". I created an Update Query that works, however, it updates all the
numbers no matter what. I am kind of new to Access so the easier the better.

UPDATE tblGrades INNER JOIN tblSOLALGI ON tblGrades.STI=tblSOLALGI.STI SET
tblGrades.ALGI = IIf(tblGrades.ALGI Is Null,tblSOLALGI.ALGI), tblGrades.ALGI
= IIf(tblgrades.ALGI>tblSOLALGI.ALGI,THEN,tblgrades.ALGI);


Update Table (tblSOLALGI)

STI = student number
LAST = last name
FIRST = first name
MI = middle initial
GRADE = grade
SCHOOL = school initials
SCHOOLYEAR = school year
ALGI = algebra i score

Main Table (tblGRADES)
STI = student number
ALGI = algebra i score
there is more, however, I only need to update the ALGI.

Thanks.

 

[Duane Hookom]

Try something like:
UPDATE tblGrades INNER JOIN tblSOLALGI ON tblGrades.STI=tblSOLALGI.STI
SET tblGrades.ALGI = IIf(Nz(tblgrades.ALGI,0) > tblSOLALGI.ALGI,
tblgrades.ALGI, tblSOLALGI.ALGI);

 

[Duane Hookom]

I should have also mentioned that storing subject names (Algi) in field names
isn't normalized. I would prefer a table with a field named [Subject] or
[Course] containing values like "Algebra", "Phy Ed", "Reading",...

 

[Mytara]

A THOUSAND Thanks! This worked perfectly.

I should have also mentioned that storing subject names (Algi) in field names
isn't normalized. I would prefer a table with a field named [Subject] or
[Course] containing values like "Algebra", "Phy Ed", "Reading",...

--
Duane Hookom
Microsoft Access MVP

I have an "update table" that I need to update to my "main information",
however, I only want the update to happen if the numbers in the "update
table" are larger than the numbers in my "main table", or if my "main table"
is blank I need the number from the "update table" to be put into my "main
table". I created an Update Query that works, however, it updates all the
numbers no matter what. I am kind of new to Access so the easier the better.

UPDATE tblGrades INNER JOIN tblSOLALGI ON tblGrades.STI=tblSOLALGI.STI SET
tblGrades.ALGI = IIf(tblGrades.ALGI Is Null,tblSOLALGI.ALGI), tblGrades.ALGI
= IIf(tblgrades.ALGI>tblSOLALGI.ALGI,THEN,tblgrades.ALGI);


Update Table (tblSOLALGI)

STI = student number
LAST = last name
FIRST = first name
MI = middle initial
GRADE = grade
SCHOOL = school initials
SCHOOLYEAR = school year
ALGI = algebra i score

Main Table (tblGRADES)
STI = student number
ALGI = algebra i score
there is more, however, I only need to update the ALGI.

Thanks.

 

[Michel Walsh]

Add the where clause, something like:

UPDATE tblGrades INNER JOIN tblSOLALGI
ON tblGrades.STI=tblSOLALGI.STI
SET
tblGrades.ALGI = tblSOLALGI.ALGI

WHERE tblgrades.ALGI < tblSOLALGI.ALGI
OR tblgrades.ALGI IS NULL




Hoping it may help,
Vanderghast, Access MVP