How to update a table with information from another table?

SQL Server 2005 or MS Access 2003 (SQL Queries)

How would I update a table with information from another table, specifically a one to many relationship. Example:
TABLE [ACCT]
ACCTNBR  NAME    CREDITLIMIT-QTR1      CREDITLIMIT-QTR2
1                Joe         0                                  0
2                Fred       0                                  0
3                Diane      0                                  0

TABLE [CREDITLIMIT]
ACCTNBR    EFFDATE   LIMIT
1                  1/30/07       20000
3                  1/30/07       25000
2                   2/15/07      30000
3                   2/10/07      40000
3                   3/31/07      20000
3                   6/30/07      15000
2                   5/30/07      10000

I would like to update TABLE [ACCT] with the correct amounts from TABLE [CREDITLIMIT].  I'm not sure, but I think I would use something like a select Max statement?  Any ideas?
irishm20Asked:
Who is Participating?
 
Angelp1ayConnect With a Mentor Commented:
Does this query successfully get quarter and credit limit?

    SELECT Format(creditlimit.effdate,"MM")/4 AS Quarter, acctnbr, Max(creditlimit.limit)
    FROM creditlimit
    Group By Format(creditlimit.effdate,"MM")/4, acctnbr
0
 
Angelp1ayCommented:
This should get you quarter and credit limit:

    SELECT Format(creditlimit.effdate,"MM")/4 AS Quarter, acctnbr, Max(creditlimit.limit)
    FROM creditlimit
    Group By Format(creditlimit.effdate,"MM")/4, acctnbr

This will insert quarter 1:

    UPDATE acct
    SET creditlimit-qtr1 =
        (SELECT  Max(creditlimit.limit)
        FROM creditlimit
        WHERE Format(creditlimit.effdate,"MM")/4 = 1
        AND creditlimit.acctnbr = acct.acctnbr)

Quarter 2...:

    UPDATE acct
    SET creditlimit-qtr1 =
        (SELECT  Max(creditlimit.limit)
        FROM creditlimit
        WHERE Format(creditlimit.effdate,"MM")/4 = 2
        AND creditlimit.acctnbr = acct.acctnbr)
0
 
Angelp1ayCommented:
Sorry, the last one should have been:

Quarter 2...:

    UPDATE acct
    SET creditlimit-qtr2 =
        (SELECT  Max(creditlimit.limit)
        FROM creditlimit
        WHERE Format(creditlimit.effdate,"MM")/4 = 2
        AND creditlimit.acctnbr = acct.acctnbr)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
irishm20Author Commented:
I'm getting a "Operation must use updateable query" error.  Any ideas?
0
 
SQL_SERVER_DBACommented:
what datatypes are these?
creditlimit
qtr2
0
 
irishm20Author Commented:
In this case, [creditlimit-qtr2] is one field and it's a float.
0
 
irishm20Author Commented:
Thanks Anelp1ay... we had an incorrect security setting on our database so your original solution worked.
0
 
Angelp1ayCommented:
Glad to be of help :o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.