Access 2007 Query Help

[vree]

tblDues

I want to add Year = 2008 and StateDues ='120' for all contact records who have a record in table Dues for 2007

SELECT tblDues.ContactID, tblDues.DuesYear, tblDues.StateDues
FROM tblContacts LEFT JOIN tblDues ON tblContacts.ContactID = tblDues.ContactID
WHERE (((tblDues.DuesYear)=2007));

I have tried a subquery but it won't run in Access:

Update tblDues
SET tblDues.Year = '2008', tblDues.Amount = '120.00'
where tblDues.ContactID in
(select tblContacts.ContactID from tblContacts, tblDues
WHERE tblContacts.ContactID = tblDues.ContactID
AND tblDues.DuesYear = '2007');

This returns "This operation must use an updateable query"

 

[ajetrumpet]

I have tried a subquery but it won't run in Access:

Update tblDues
SET tblDues.Year = '2008', tblDues.Amount = '120.00'
where tblDues.ContactID in
(select tblContacts.ContactID from tblContacts, tblDues
WHERE tblContacts.ContactID = tblDues.ContactID
AND tblDues.DuesYear = '2007');

The section in red is not logical. It looks like the request is just being pulled out of the air. Could you explain in words what you are trying to get out of the subquery here?

 

[vree]

I have table Dues
For each record in tblDues where duesyear =2007 i want to add a record where duesyear = 2008 and state dues amount = 120.00

 

[ajetrumpet]

Sounds like you just want a carryover of the $120.00 to the next year. Is that what you want? You could do this just by updating, but if you really want a separate record (with any number of fields) for EACH record that has a due amount in 2007, you will have to write a procedure. Do you know how to do that?

 

[vree]

Actually do want to insert a new record (ie contactID - 753 has a record in dues table for every yr they have paid dues.) for each contactID that has a 2007 entry.

No I dont think I know how to do this with a procedure. Tips?

 

[Rabbie]

tblDues

I want to add Year = 2008 and StateDues ='120' for all contact records who have a record in table Dues for 2007

SELECT tblDues.ContactID, tblDues.DuesYear, tblDues.StateDues
FROM tblContacts LEFT JOIN tblDues ON tblContacts.ContactID = tblDues.ContactID
WHERE (((tblDues.DuesYear)=2007));

I have tried a subquery but it won't run in Access:

Update tblDues
SET tblDues.Year = '2008', tblDues.Amount = '120.00'
where tblDues.ContactID in
(select tblContacts.ContactID from tblContacts, tblDues
WHERE tblContacts.ContactID = tblDues.ContactID
AND tblDues.DuesYear = '2007');

This returns "This operation must use an updateable query"

See this link. Subqueries stop the query from being able to update.

 

[ajetrumpet]

You should have a stored procedure for this, as it is probably the same every year. But, at any rate, to get a new record into the dues table for each contact that you have, and to add the $120.00 into each one of the those new records, type the following into a module and run it (if you do run this, make a copy of your tables first, just in case it is not what you want to have happen)...

Function TypeAFunctionNameHere()

dim x as long
  dim intcounter as long
    dim db as database
      dim rsdues as recordset
        dim rscontacts as recordset

set db = currentdb
set rscontacts = db.openrecordset("tblcontacts")
set rsdues = db.openrecordset("tbldues")

x = rscontacts.recordcount
intcounter = 0

  rscontacts.movefirst
  rsdues.movefirst

  with rsdues

    do until intcounter = x [color=red](or you can put "[B]do until rscontacts.EOF[/B]" here)[/color]

      .addnew
        !statedues = 120
        !contactID = rscontacts.contactID
      .update
      .movenext

      rscontacts.movenext

      intcounter = intcounter + 1
  
    Loop

  end with

end function

 

[ByteMyzer]

ajetrumpet, you do not need a procedure to do this. A simple Append Query will suffice.

Rabbie, subqueries do not necessarily stop a query from being able to update. If written properly, the use of subqueries in Update Queries is a powerful tool.

vree, try this Append Query:

INSERT INTO tblDues (ContactID, DuesYear, StateDues)
SELECT DISTINCT T1.ContactID, 2008, 120
FROM tblDues T1
WHERE T1.DuesYear=2007

 

[ajetrumpet]

Very good ByteMyzer. You understand though, that your post makes me look bad, don't you? I might have to stop helping people now...

I would certainly take what you wrote, instead of mine!

 

[Rabbie]

Rabbie[/b], subqueries do not necessarily stop a query from being able to update. If written properly, the use of subqueries in Update Queries is a powerful tool.

I am sorry. I must have misunderstood the info in the link that I posted. Allen Browne is usually extremely good on these things.

 

[ByteMyzer]

Very good ByteMyzer. You understand though, that your post makes me look bad, don't you? I might have to stop helping people now...

Now, now, we all have our different approaches, and together we make one powerful help community

I am sorry. I must have misunderstood the info in the link that I posted. Allen Browne is usually extremely good on these things.

A subquery in the SELECT Clause of the SQL statement will stop a query from being able to update. That is what Allen is saying. This does not mean that a subquery can not be used in an update query under any circumstance.

 

[Rabbie]

Now, now, we all have our different approaches, and together we make one powerful help community

A subquery in the SELECT Clause of the SQL statement will stop a query from being able to update. That is what Allen is saying. This does not mean that a subquery can not be used in an update query under any circumstance.

Thanks for clarifying that. I am afraid I misread your SQL.